In need of IQ db backup script (to be run via unix cron)

[Former Member]

Hi all,

Before I commence attempting to generate an IQ backup script that I will schedule from cron I though id reach out and ask if anyone has any that they can share - so that I don't have to re-invent the wheel so to speak?

Thanks in advance 🙂

[raymond_lackey]

Hi Andrew, You might consider something similar to this, let the dB manage backups and cron the cleanups.sorry about the length. 🙂

Create a Store Procedure to Execute the Backups.

CREATE PROCEDURE DBA.msp_backup (
@myenv varchar(10),
@bckType varchar(10)) – F:full,IsF:Incremental since full,I:incremental
AS
BEGIN
declare @mypath varchar(30)
declare @myfile varchar(60)

  select ‘/backups/’  into @mypath
select @mypath + @myenv + ‘/’


            + case @bckType
                     when ‘F’  then ‘full’
                       else ‘inc’
               end case


           + ‘/’ + db_name() + ‘_’


          + @bckType + ‘_’


          + cast(dayname(getdate()) as varchar(3)) + ‘_’


          + cast(year(getdate()) as varchar(4)) + ‘_’


          + cast(month(getdate()) as varchar(2)) + ‘_’


         + cast(day(getdate()) as varchar(2)) + ‘_’


         + cast(hour(getdate()) as varchar(2)) + ‘h’ 


         + cast(minute(getdate()) as varchar(2)) + ‘m’
into @myfile 

IF @bckType = ‘F’
begin
backup database full to @myfile 
end

IF @bckType = ‘IsF’
begin
backup database incremental since full to @myfile 
end

IF @bckType = ‘I’
begin
backup database incremental to @myfile 

end

IF @bckType not in (‘F’,‘IsF’,‘I’) 
   raiserror 99999 ‘Invalid backup type: %1!’, @bckType

END

 This store procedure receive two (2) input parameters: @myenv, this parameter allow the use of the same procedure to backup different environments (i.e.: prod, test, dev); and @bckType, this allow to use the same procedure to execute different types of backups (i.e.: Full, Incremental, Incremental since full).

The name of the backup is automatically generated using the name of the database, the type of backup and the system date; for example, MyDB_F_Sun_2012_10_7_20h30 will be the full backup of MyDB made on Sunday Oct 7, 2012 at 10:30 pm.

2. Schedule the Backup Events.

Now, we schedule the backups with the following four (4) events:

CREATE EVENT WeeklyFullBackup
  SCHEDULE Sched_WeeklyFullBackup
  START TIME ’10:30 PM’ ON (‘Sunday’)
  ENABLE
  HANDLER
  BEGIN 
    CALL DBA.msp_backup (‘prod’, ‘F’)
  END

CREATE EVENT DailyIncSinceFullBackup
SCHEDULE Sched_DailyIncSinceFullBackup
BETWEEN ’12:00 PM’  AND ’10:00 PM’  EVERY 9 HOURS

      ON ( ‘Monday’, ‘Tuesday’, ‘Wednesday’, ‘Thursday’, ‘Friday’, ‘Saturday’)
ENABLE
HANDLER

BEGIN 

 CALL DBA.msp_backup (‘prod’, ‘IsF’)

 END

CREATE EVENT DailyIncBackupMorning
SCHEDULE Sched_DailyIncBackupMorning
BETWEEN ’6:00 AM’  AND ’10:10 AM’  EVERY 2 HOURS
ENABLE
HANDLER
BEGIN 
   CALL DBA.msp_backup (‘prod’, ‘I’)
END

CREATE EVENT DailyIncBackupAfternoon
  SCHEDULE Sched_DailyIncBackupAfternoon
BETWEEN ’2:00 PM’ AND ’10:10 AM’  EVERY 2 HOURS
ENABLE
HANDLER
BEGIN 
    CALL DBA.msp_backup (‘prod’, ‘I’)
   END

3. Clean the Old Backup Files.

Here is the script that I use to purge the backups files older that 24 hrs.


#!/bin/ksh

. $HOME/.profile
# Script name: CleanOldBckFiles.sh
# Description: Delete all files older that 24 hrs
# Parameters: $1:env(‘prod’,'dev’,'test’);$2:backup type (‘full’,'inc’)
# Author: Uvernes Somarriba
# Date: Oct 24, 2011


# Log files to be deleted


find / backups/$1/$2 -type f -mtime +1 -exec ls -l >> / backups/$1/deleted_files.log {} \;


# delete files


find / backups/$1/$2 -type f -mtime +1 -exec rm -f {} \;


Add the following line to your crontab:


00 22 * * 1,2,3,4,5,6 /IQ/scripts/CleanOldBckFiles.sh prod inc > /dev/null 2>&1


I’d located the script on the /IQ/Scripts folder. This script writes the name of the file to delete to the deleted_files.log file and the remove the file.


Restoration

You need to restore in the following order:

Restore the Weekly full backup.
Restore the last Incremental since full backup.
Restore, in the increasing order by time, all the incremental backups since the last “Incremental since full backup” backup.

[Former Member]

thank you