Skip to content

Sybase IQ Backup Strategy: Full Database

October 11, 2012

Introduction

In this post I present the implementation of an automated Simple Backup Strategy for Sybase IQ; the full database is backed up.

Environment

Sybase IQ 15.2 ESD # 2 hosted on Solaris 10 64b; Sun Sparc platform.

Backup Strategy

  1. Make a full database backup once a week (on Sunday Night).
  2. Make “incremental since full backups” twice a day (at noon and at 9:00 pm; except Sunday).
  3. Make “incremental” backups every 2 hrs during the day.
  4. No backup is scheduled during the maintenance window (10:00 pm – 6:00 am)
Backups Schedule
 

Sun

Mon

Tue

Wed

Thu

Fri

Sat

0:00

 

 

 

 

 

 

 

2:00

 

 

 

 

 

 

 

4:00

 

 

 

 

 

 

 

6:00

I

I

I

I

I

I

I

8:00

I

I

I

I

I

I

I

10:00

I

I

I

I

I

I

I

12:00

IsF

IsF

IsF

IsF

IsF

IsF

14:00

I

I

I

I

I

I

I

16:00

I

I

I

I

I

I

I

18:00

I

I

I

I

I

I

I

20:00

I

I

I

I

I

I

I

20:30

F

 

 

 

 

 

 

21:00

 

IsF

IsF

IsF

IsF

IsF

IsF

F Full backup
I Incremental
IF Incremental since full backup

Implementation

1. 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.

Additional Recommendations

  1. Make regular backups of the backups files (to external media, like tape).
  2. Verify regularly that the backup files are been successful created.
  3. Test restore at least once a month.

Restoration

You need to restore in the following order:

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

Do not start the database until the last backup has been restored; if you do so, you won’t be able to restore further.

Conclusion

With the utilization of events, you can easily automate a backup’s strategy for Sybase IQ databases.

Advertisements

From → SAP IQ

2 Comments
  1. Good example
    You can also use Data Protector for Sybase IQ using IBM Spectrum Protect ( TSM )
    Or Snap Protector for Sybase IQ using IBM Spectrum Protect ( TSM )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: