Skip to content

Sybase IQ Restore

January 9, 2013

Introduction

In this post I present a procedure to restore a backup made using the Backup Strategy that I described in the post “Sybase IQ Backup Strategy: Full Database“.

The restore in this example is performed on Tuesday afternoon after 4:00 pm, this allow presenting the steps required to restore the database up to last incremental backup.

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)

Restore Strategy

  1. Restore the last full backup (from Sunday Night).
  2. Restore the last “incremental since full backups” (from Tuesday noon).
  3. Restore all “incremental backups” since the last “incremental since full backup” (from Tuesday at 2:00 pm and Tuesday at 4:00 pm).

Note: Do not start the database before restoring the last incremental backup.

Assumptions

I assume that:

  1. You know the name and size of the database files (dbfiles) of the database when the backup was taken.
  2. The required raw devices to restore the database have been already created.

Restore Procedure

  1. Stop the database you will restore

  2. If the database is not already stopped, you can use the stop_iq command to stop the database.

  3. Delete the <database>.db and <database>.log files

  4. It is a good idea to save a copy of those files before deleting them.

  5. Start the utility database

  6. start_iq @uatDwIqSrvr_restore.cfg -gu utility_db

    where uatDwIqSrvr_restore.cfg contains:

    # uatDwIqSrvr_restore.cfg by Uvernes
    # ———————————————————————-
    # Must be in the format: One parameter per line
    # ———————————————————————-
    -n uatDwIqSrvr
    -x tcpip{port=4400}
    -cl 500m
    -ch 1000m
    -gc 20
    -gd DBA
    -gl all
    -gm 1
    -gp 4096
    -ti 4400
    -iqmc 4096
    -iqtc 5120

    Execute all the following instructions as DBA on the utility database.

  7. Restore the Sunday Full Backup

  8. RESTORE DATABASE ‘uatDwIqDb’
    RENAME IQ_SYSTEM_MAIN TO ‘/dev/vx/rdsk/datadg/puatsystem1’
    RENAME IQ_SYSTEM_MSG TO ‘uatDwIqDb.iqmsg’
    RENAME DF1 TO ‘/dev/vx/rdsk/datadg/puatdata1’
    RENAME DF2 TO ‘/dev/vx/rdsk/datadg/puatdata2’
    RENAME DF3 TO ‘/dev/vx/rdsk/datadg/puatdata3’
    RENAME DF4 TO ‘/dev/vx/rdsk/datadg/puatdata4’
    RENAME DF5 TO ‘/dev/vx/rdsk/datadg/puatdata5’
    RENAME DF6 TO ‘/dev/vx/rdsk/datadg/puatdata6’
    RENAME IQ_SYSTEM_TEMP1_16390 TO ‘/dev/vx/rdsk/datadg/puattemp1’
    RENAME IQ_SYSTEM_TEMP2_16387 TO ‘/dev/vx/rdsk/datadg/puattemp2’
    RENAME IQ_SYSTEM_TEMP3_16393 TO ‘/dev/vx/rdsk/datadg/puattemp3’
    RENAME IQ_SYSTEM_TEMP4_16394 TO ‘/dev/vx/rdsk/datadg/puattemp4’
    RENAME IQ_SYSTEM_TEMP5_16396 TO ‘/dev/vx/rdsk/datadg/puattemp5’
    RENAME IQ_SYSTEM_TEMP6_16397 TO ‘/dev/vx/rdsk/datadg/puattemp6’
    FROM ‘/backups/prod/full/prodDwIqDb_F_Sun_2013_2_24_20h30’

    Note: I’m restoring the database to another server. I need to use RENAME to restore the database files to the new raw devices.

  9. Restore the Tuesday Noon (Last) Incremental Since Full Backup

  10. RESTORE DATABASE ‘uatDwIqDb’
    RENAME IQ_SYSTEM_MAIN TO ‘/dev/vx/rdsk/datadg/puatsystem1’
    RENAME IQ_SYSTEM_MSG TO ‘uatDwIqDb.iqmsg’
    RENAME DF1 TO ‘/dev/vx/rdsk/datadg/puatdata1’
    RENAME DF2 TO ‘/dev/vx/rdsk/datadg/puatdata2’
    RENAME DF3 TO ‘/dev/vx/rdsk/datadg/puatdata3’
    RENAME DF4 TO ‘/dev/vx/rdsk/datadg/puatdata4’
    RENAME DF5 TO ‘/dev/vx/rdsk/datadg/puatdata5’
    RENAME DF6 TO ‘/dev/vx/rdsk/datadg/puatdata6’
    RENAME IQ_SYSTEM_TEMP1_16390 TO ‘/dev/vx/rdsk/datadg/puattemp1’
    RENAME IQ_SYSTEM_TEMP2_16387 TO ‘/dev/vx/rdsk/datadg/puattemp2’
    RENAME IQ_SYSTEM_TEMP3_16393 TO ‘/dev/vx/rdsk/datadg/puattemp3’
    RENAME IQ_SYSTEM_TEMP4_16394 TO ‘/dev/vx/rdsk/datadg/puattemp4’
    RENAME IQ_SYSTEM_TEMP5_16396 TO ‘/dev/vx/rdsk/datadg/puattemp5’
    RENAME IQ_SYSTEM_TEMP6_16397 TO ‘/dev/vx/rdsk/datadg/puattemp6’
    FROM ‘/backups/prod/inc/prodDwIqDb_IsF_Tue_2013_1_8_12h0m’

    Note that you still need to specify the rename clause.

  11. Restore All the Incremental Since the Last Incremental Since Full Backup

RESTORE DATABASE ‘uatDwIqDb’
RENAME IQ_SYSTEM_MAIN TO ‘/dev/vx/rdsk/datadg/puatsystem1’
RENAME IQ_SYSTEM_MSG TO ‘uatDwIqDb.iqmsg’
RENAME DF1 TO ‘/dev/vx/rdsk/datadg/puatdata1’
RENAME DF2 TO ‘/dev/vx/rdsk/datadg/puatdata2’
RENAME DF3 TO ‘/dev/vx/rdsk/datadg/puatdata3’
RENAME DF4 TO ‘/dev/vx/rdsk/datadg/puatdata4’
RENAME DF5 TO ‘/dev/vx/rdsk/datadg/puatdata5’
RENAME DF6 TO ‘/dev/vx/rdsk/datadg/puatdata6’
RENAME IQ_SYSTEM_TEMP1_16390 TO ‘/dev/vx/rdsk/datadg/puattemp1’
RENAME IQ_SYSTEM_TEMP2_16387 TO ‘/dev/vx/rdsk/datadg/puattemp2’
RENAME IQ_SYSTEM_TEMP3_16393 TO ‘/dev/vx/rdsk/datadg/puattemp3’
RENAME IQ_SYSTEM_TEMP4_16394 TO ‘/dev/vx/rdsk/datadg/puattemp4’
RENAME IQ_SYSTEM_TEMP5_16396 TO ‘/dev/vx/rdsk/datadg/puattemp5’
RENAME IQ_SYSTEM_TEMP6_16397 TO ‘/dev/vx/rdsk/datadg/puattemp6’
FROM ‘/backups/prod/inc/prodDwIqDb_I_Tue_2013_1_8_14h0m’

RESTORE DATABASE ‘uatDwIqDb’
RENAME IQ_SYSTEM_MAIN TO ‘/dev/vx/rdsk/datadg/puatsystem1’
RENAME IQ_SYSTEM_MSG TO ‘uatDwIqDb.iqmsg’
RENAME DF1 TO ‘/dev/vx/rdsk/datadg/puatdata1’
RENAME DF2 TO ‘/dev/vx/rdsk/datadg/puatdata2’
RENAME DF3 TO ‘/dev/vx/rdsk/datadg/puatdata3’
RENAME DF4 TO ‘/dev/vx/rdsk/datadg/puatdata4’
RENAME DF5 TO ‘/dev/vx/rdsk/datadg/puatdata5’
RENAME DF6 TO ‘/dev/vx/rdsk/datadg/puatdata6’
RENAME IQ_SYSTEM_TEMP1_16390 TO ‘/dev/vx/rdsk/datadg/puattemp1’
RENAME IQ_SYSTEM_TEMP2_16387 TO ‘/dev/vx/rdsk/datadg/puattemp2’
RENAME IQ_SYSTEM_TEMP3_16393 TO ‘/dev/vx/rdsk/datadg/puattemp3’
RENAME IQ_SYSTEM_TEMP4_16394 TO ‘/dev/vx/rdsk/datadg/puattemp4’
RENAME IQ_SYSTEM_TEMP5_16396 TO ‘/dev/vx/rdsk/datadg/puattemp5’
RENAME IQ_SYSTEM_TEMP6_16397 TO ‘/dev/vx/rdsk/datadg/puattemp6’
FROM ‘/backups/prod/inc/prodDwIqDb_I_Tue_2013_1_8_16h0m’

Post-Restore Tasks

  1. Stop the Utility Database

  2. Use the Sybase stop_iq utility to stop the utility database and associated engine.

  3. Verify the licence

  4. Verify that the appropriate license type (LT) is registered in the <database_name>.lmp file.

  5. Start the Restored Database

Use the Sybase start_iq utility to start the restored database.

Done!

Advertisements

From → SAP IQ

One Comment
  1. >>>You know the name and size of the database files (dbfiles) of the database when the backup was taken.

    you can do this in 15 version via db_backupheader

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: