Skip to Content
avatar image
Former Member

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

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 :)

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 17 at 01:48 PM

    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.
    
    
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 21 at 01:04 PM

    thank you

    Add comment
    10|10000 characters needed characters exceeded