Loading...

Advanced Restore - Sybase iDataAgent

Table of Contents

Restoring the Sybase Server

When the Sybase server is corrupted or lost, you can restore and recover it from the latest full backup.

Before You Begin

  1. Place the Sybase server offline.
  2. Edit the Sybase configuration file which usually resides in Sybase home directory to disable the following parameter:

    sp_configure ’enable xact coordination’, 0

    Set the ‘enable xact coordination = 0’ under “DTM Administration” section by editing the configuration file.

  3. If  the dataserver is running, execute the following command to turn it off and then restart the dataserver to update the changes.

    “sp_configure "enable job scheduler", 0 “

Procedure

  1. From the CommCell Browser, expand Client Computers > client > Sybase.
  2. Right-click the instance, point to All Tasks and then click Restore Sybase Server.

    The Sybase Restore dialog box is displayed.

  3. On the General tab, select the Sybase Create Devices check box.
  4. Click OK to start the restore.

Restoring the Master Database

When you restore an entire Sybase server or a master database, it is highly recommended to select Sybase create devices. However, you can also create a master database manually and then perform a restore without selecting Sybase create devices. See Manually Creating a Master Database for a detailed information.

Before You Begin

  • Place the Sybase server offline.

Procedure

  1. From the CommCell Browser, expand Client Computers > client > Sybase.
  2. Right-click the instance, point to All Tasks and then click Browse and Restore.
  3. On the Browse and Restore dialog box, click View Content.
  4. From the Browse window, select the master database in the right pane and click Recover All Selected.

    The Sybase Restore dialog box is displayed.

  5. Best Practice: On the General tab, select the Sybase Create Devices check box.
  6. Click OK to start the restore.

Restoring System Databases

System databases are essential for the operation of the Sybase Server instance. If you need to restore the Sybase Server in the event of a system failure, the system databases (master, model, sybsystemdb and sybsystemprocs) must be backed up. The tempdb does not get backed up as it is re-created by the Sybase Server every time the server is started.

By default, system databases are restored in the same location from where they were backed up and their data files are overwritten.

Before You Begin

  1. Place the databases offline.
  2. Edit the Sybase configuration file which usually resides in Sybase home directory to disable the following parameter:

    sp_configure ’enable xact coordination’, 0

    Set the ‘enable xact coordination = 0’ under the DTM Administration section by editing the configuration file.

  3. If you are restoring the sybmgmtdb as part of the system databases, then you need to turn off the job scheduler during restore. Edit the Sybase configuration file to set ‘enable job scheduler = 0’ under SQL Server Administration.
  4. If the dataserver is running, execute the following command to turn it off and then restart the dataserver to update the changes.

    “sp_configure "enable job scheduler", 0 “

Procedure

  1. From the CommCell Browser, expand Client Computers > client > Sybase.
  2. Right-click the instance, point to All Tasks and then click Browse and Restore.
  3. On the Browse and Restore dialog box, click View Content.
  4. From the Browse window, select the master, model, systemdb and systemprocs databases in the right pane and click Recover All Selected.
  5. Click OK to start the restore.

Restoring Sybase Databases to a Point-in-Time

If any undesired transaction occurs in the database, you can revert the database to a state just before the transaction. The point in time option enables you to restore the database to a specific point-in-time. This option is useful in the following scenarios:

  • If any undesired transaction occurs in the database, you can revert the database to a state just before the transaction.
  • If a database fails, you can restore to the state just before the point of failure.
  • You can restore multiple databases to a consistent time. This will be useful for the absolute synchronization of the databases.

Before You Begin

  • Ensure that transaction logs for the database are backed up.

Procedure

  1. From the CommCell Browser, expand Client Computers > client > Sybase.
  2. Right-click the instance, point to All Tasks and then click Browse and Restore.
  3. On the Browse and Restore dialog box, click View Content.
  4. From the Browse window, select the databases that you want to restore in the right pane and click Recover All Selected.

    The Restore Options dialog box is displayed.

  5. On the General tab:
    1. Select the Point-in-time check box.
    2. In the Restore Time box, enter the date and time.
    3. When the client is in a different time zone that the CommServe computer, select the client time zone from the Time Zone list.
  6. Click OK to start the restore.

Result

The databases are restored to the selected data and the next scheduled backup for that database automatically converts to a full backup.

Restoring Transaction Logs to a Point-in-Time

You can restore the transaction logs along with the database to a specific point in time. The point in time should be in between the start and end point of a transaction log. The database backup job along with the immediate transaction log after a point in time will be restored.

For example:

Let us assume that you have performed a Full backup (F1) and transaction log backup (L1) for the first time at 1 P.M. Subsequently you have performed L2 at 3 P.M. Later, you have performed L3, F2, L4,L5 at subsequent intervals. If you perform a point in time restore for the transaction log backups at 2 P.M. the Full backup (F1) along with the log backups of L1 and L2 (transaction log after point in time) will be restored.

Procedure

  1. From the CommCell Browser, expand Client Computers > client > Sybase.
  2. Right-click the instance, point to All Tasks and then click Browse and Restore.
  3. From the Browse window, select the databases that you want to restore in the right pane and click Recover All Selected.

    The Restore Options dialog box is displayed.

  4. On the General tab:
    1. Select the Point-in-time check box.
    2. In the Restore Time box, enter the date and time.
    3. When the client is in a different time zone that the CommServe computer, select the client time zone from the Time Zone list.
  5. Click OK to start the restore.

Restoring Cumulative Incremental Backups

Use the following sequence when you perform a cumulative incremental backup in your data protection strategy:

  1. Load the full backup.
  2. Load the latest cumulative backup that was run before the recovery point.
  3. Load the transaction logs to the point of recovery.

Example

You have the following backups.

  1. Full backup (F1)
  2. First cumulative backup (C1)
  3. First transaction log backup (L1)
  4. Second cumulative backup (C2)
  5. Second transaction log backup (L2)
  6. Third transaction log backup (L3)

If the recovery point is a time between the second cumulative backup (C2) and the second transaction log backup (L2), the restore sequence is:

  1. Restore the full backup (F1)
  2. Restore the second cumulative backup (C2)
  3. Restore the second log backup (L2)

Recovering Databases

By default, a database is left in the online state after a restore. you can restore the data and then recover the database at a later point-in-time after performing any post operations.

Restoring and Recovering the Databases

  1. From the CommCell Browser, expand Client Computers > client > Sybase.
  2. Right-click the instance, point to All Tasks and then click Browse and Restore.
  3. From the Browse window, select the databases that you want to restore in the right pane and click Recover All Selected.

    The Restore Options dialog box is displayed.

  4. On the General tab, select the Recover option.
  5. Click OK to start the restore.

Restoring the Databases without Recovery

  1. From the CommCell Browser, expand Client Computers > client > Sybase.
  2. Right-click the instance, point to All Tasks and then click Browse and Restore.
  3. From the Browse window, select the databases that you want to restore in the right pane and click Recover All Selected.

    The Restore Options dialog box is displayed.

  4. On the General tab, select the No Recover option.
  5. Click OK to start the restore.

Restoring a Database to the Current State (Step-by-Step Restore)

When you want to restore an event in the database but do not know the exact time of the event, you can first restore the database to the last full backup and then run a Step-by-Step restore to apply the logs by the sequence of log backups. Step-by-Step Restores are performed on individual databases. You will not be able to schedule a step-by-step restore.

You must repeat this procedure until you get to the correct restore time.

Note: This is a controlled restore and applicable for restoring a single database.

Procedure

  1. From the CommCell Browser, expand Client Computers > client > Sybase.
  2. Right-click the instance, point to All Tasks and then click Browse and Restore.
  3. On the Browse and Restore dialog box, click View Content.
  4. From the Browse window, select the databases that you want to restore in the right pane and click Recover All Selected.

    The Restore Options dialog box is displayed.

  5. On the General tab:
    1. From the Destination Server list, select the destination server name.
    2. Select the Restore Options check box.
    3. Select Sybase Step By Step option.
    4. Select the last Full backup time from the Restore Time drop down list.
    5. Click No Recover.
  6. Click OK to start the restore.

Renaming a Database

You can rename user databases. The new database names must be unique and follow the Sybase Identifier rules. When you rename databases, make sure that the tables in the databases are not referencing to or referenced by tables in any other databases.

Procedure

  1. From the CommCell Browser, expand Client Computers > client > Sybase.
  2. Right-click the instance, point to All Tasks and then click Browse and Restore.
  3. On the Browse and Restore dialog box, click View Content.
  4. From the Browse window, select the databases that you want to restore in the right pane and click Recover All Selected.

    The Restore Options dialog box is displayed.

  5. On the General tab, select the Rename databases check box and then click Advanced.

    The Advanced Restore Options dialog box is displayed.

  6. On the Options tab, in the row for the database that you want to rename, click the Target Database column and enter the new name.
  7. Click OK to close the Advanced Restore Options dialog box.
  8. Click OK to start the restore.

Restoring Databases to a New Device

While restoring the Sybase databases to a new device, you can also change the size of the device. You need to drop the existing device to change the size and then perform the restore.

Procedure

  1. From the CommCell Browser, expand Client Computers > client > Sybase.
  2. Right-click the instance, point to All Tasks and then click Browse and Restore.
  3. On the Browse and Restore dialog box, click View Content.
  4. From the Browse window, select the databases that you want to restore in the right pane and click Recover All Selected.

    The Restore Options dialog box is displayed.

  5. On the General tab, select  the Sybase Create Devices check box and then click Advanced.

    The Advanced Restore Options dialog box is displayed.

  6. On the Options tab, in the row for the database that you want to rename, click the Target Database column and enter the new name.
  7. Click OK to close the Advanced Restore Options dialog box.
  8. Click OK to start the restore.

Restoring Databases to a Different Sybase Server Instance

By default, when restoring to a different Sybase Server instance, a database is restored to the same location as source server in the destination server.

You can only restore from a lower version of the software to a higher version, for example, Sybase 15.5. to 15.7.

Before You Begin

  1. Verify that page size is the same at the source and destination.
  2. Configure the destination server in the CommCell Console.

Procedure

  1. From the CommCell Browser, expand Client Computers > client > Sybase.
  2. Right-click the instance, point to All Tasks and then click Browse and Restore.
  3. On the Browse and Restore dialog box, click View Content.
  4. From the Browse window, select the databases that you want to restore in the right pane and click Recover All Selected.

    The Restore Options dialog box is displayed.

  5. Select the Destination Server. If the devices are not existing on the destination server, then select Sybase Create Devices. See Restoring Databases to a New Device to create or change the devices on the destination server.
  6. Click OK to start the restore.

Restoring the Sybase Server to a Different Path

Before You Begin

  1. Verify that you have Sybase Database Administrator privileges when you restore a Sybase server to a different path.
  2. Verify that the device has enough physical space to store the database

If you want to restore  an entire server to a different path:

  1. Restore the master database and system databases.
  2. Drop the databases and existing Sybase devices. Ensure that the databases of a different Sybase server are not part of the device that is being dropped.
  3. Restore the individual databases separately choosing Sybase create device. Each individual database needs to be restored as a separate job.

Procedure

  1. From the CommCell Browser, expand Client Computers > client > Sybase.
  2. Right-click the instance, point to All Tasks and then click Browse and Restore.
  3. On the Browse and Restore dialog box, click View Content.
  4. From the Browse window, select the databases that you want to restore in the right pane and click Recover All Selected.

    The Restore Options dialog box is displayed.

  5. On the General tab, select the Sybase Create Devices check box and then click Advanced.

    The Advanced Restore Options dialog box is displayed.

  6. On the Options tab, in the row for the database that you want to rename, type the new path of the selected device under the Physical Path column and click OK.
  7. Click OK to start the restore.

Restoring to a Disk (App Free Restore)

You can restore the backup jobs of data and logs or multiple backup jobs at a time directly to a location on a disk using CommCell console without the intervention of Sybase application. This is useful for restoring multiplexed data on the same media. The data from different backup jobs are passed back to the client so that all of the interleaved backup jobs can be restored in parallel. You can restore the data associated with a specific backup job (s) such as full, incremental, logs, etc., in parallel streams. You can also submit multiple restore jobs from full and incremental backups.

Use the following steps to perform an app free restore operation:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | Sybase.

    Right-click the <Instance>, point to All Tasks and then click Restore to Disk.

  2. Click OK

  3. Select the backup job that you want to restore to a disk. Right click on the job and select Restore to Disk.

    You can select multiple backup jobs at a time and restore them to disk.

  4. Type the path or click Browse to specify the location of the disk to restore the application data.

    Impersonate User if you want to use a different account that contains the required privileges.

    n the User Name box, type the user name. The User should have necessary privileges to restore the application data.

    In the Password box, type the password for the user account.

    In the Confirm Password box, re-type the password.

  5. The backup job will be restored to the disk location specified in the Restore Options for All Selected Items dialog box.

Recovering the Database after a Restore

The dump files associated with a backup job will restored to a disk in the following format:

<BACKUP-JOB-ID>-<DATABASE-NAME>-<TOTAL-NO-OF-STRIPES>-<DB/XACT>-<TIME-STAMP>-<STRIPE-NUMBER>.

Example 1: Database dump files associated with database db1 and backup job 1989. Backup job 1989 was performed with 4 streams, hence you will see 4 files.

1989-db1-4.DB.130212185252553.00
1989-db1-4.DB.130212185252553.01
1989-db1-4.DB.130212185252553.02
1989-db1-4.DB.130212185252553.03

Example 2: Transaction dump files associated with database db1 and transaction log backup job 2140. Backup job 2140 was performed with 3 streams, hence you will see 3 files.

2140-db1-3.XACT.130221194903420.00
2140-db1-3.XACT.130221194903420.01
2140-db1-3.XACT.130221194903420.02

A restore script will be auto generated by SnapProtect for each database and for each backup job.

Restore script file format will be as follows:

<BACKUP-JOB-ID>-<DATABASE-NAME>.sql

Format for database restore script is as follows:

load database <DATABASE-NAME> from “SybGalaxy:: -disk <FIRST-STRIPE-DUMP-FILE-PATH>” stripe on “SybGalaxy:: -disk <SECOND-STRIPE-DUMP-FILE-PATH>” ………..

Format for database recovery script is as follows:

load transaction <DATABASE-NAME> from “SybGalaxy:: -disk <FIRST-STRIPE-DUMP-FILE-PATH>” stripe on “SybGalaxy:: -disk <SECOND-STRIPE-DUMP-FILE-PATH>” ………..

Example 1:

Script file name for database db1 job ID 1989(Database backup) is 1989--db1.sql

Script file content:

load database db1 from "SybGalaxy:: -disk /MyAppFreeRestore/2141/1989-db1-4.DB.130212185252553.00" stripe on "SybGalaxy:: -disk /MyAppFreeRestore/2141/1989-db1-4.DB.130212185252553.01" stripe on "SybGalaxy:: -disk /MyAppFreeRestore/2141/1989-db1-4.DB.130212185252553.02" stripe on "SybGalaxy:: -disk /MyAppFreeRestore/2141/1989-db1-4.DB.130212185252553.03"

Example 2:

Script file name for database db1 job ID 2140(Transaction log backup) is 2140—db1.sql

Script file content:

load transaction db1 from "SybGalaxy:: -disk /MyAppFreeRestore/2141/2140-db1-3.XACT.130221194903420.00" stripe on "SybGalaxy:: -disk /MyAppFreeRestore/2141/2140-db1-3.XACT.130221194903420.01"
stripe on "SybGalaxy:: -disk /MyAppFreeRestore/2141/2140-db1-3.XACT.130221194903420.02"
go

Connect to Sybase ASE server and execute the following restore script to restore/recover the database:

isql –U username –P password –S ServerName -i <script_name>.sql

Example :

Execute restore script [root@localhost 2141]# isql -Usa -Padmin123 -SybGalaxy -i <script_name>.sql
3> go
Backup Server session id is: 60. Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume change
request from the Backup Server.
Backup Server: 4.132.1.1: Attempting to open byte stream device: 'SybGalaxy::
-disk /MyAppFreeRestore/2141/1989-db1-4.DB.130212185252553.00::00'
Backup Server: 4.132.1.1: Attempting to open byte stream device: 'SybGalaxy::
-disk /MyAppFreeRestore/2141/1989-db1-4.DB.130212185252553.01::01'
Backup Server: 4.132.1.1: Attempting to open byte stream device: 'SybGalaxy::
-disk /MyAppFreeRestore/2141/1989-db1-4.DB.130212185252553.02::02'
Backup Server: 4.132.1.1: Attempting to open byte stream device: 'SybGalaxy::
-disk /MyAppFreeRestore/2141/1989-db1-4.DB.130212185252553.03::03'
Backup Server: 6.28.1.1: Dumpfile name 'db11307110984 ' section number 1
mounted on byte stream 'SybGalaxy:: -disk
/MyAppFreeRestore/2141/1989-db1-4.DB.130212185252553.03::03'
Backup Server: 6.28.1.1: Dumpfile name 'db11307110984 ' section number 1
mounted on byte stream 'SybGalaxy:: -disk
/MyAppFreeRestore/2141/1989-db1-4.DB.130212185252553.01::01'
Backup Server: 6.28.1.1: Dumpfile name 'db11307110984 ' section number 1
mounted on byte stream 'SybGalaxy:: -disk
/MyAppFreeRestore/2141/1989-db1-4.DB.130212185252553.00::00'
Backup Server: 6.28.1.1: Dumpfile name 'db11307110984 ' section number 1
mounted on byte stream 'SybGalaxy:: -disk
/MyAppFreeRestore/2141/1989-db1-4.DB.130212185252553.02::02'
Backup Server: 4.188.1.1: Database db1: 1040 kilobytes (16%) LOADED.
Backup Server: 4.188.1.1: Database db1: 1046 kilobytes (17%) LOADED.
Backup Server: 4.188.1.1: Database db1: 2072 kilobytes (33%) LOADED.
Backup Server: 4.188.1.1: Database db1: 4122 kilobytes (67%) LOADED.
Backup Server: 4.188.1.1: Database db1: 6170 kilobytes (100%) LOADED.
Backup Server: 4.188.1.1: Database db1: 6182 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database db1).
Started estimating recovery log boundaries for database 'db1'.
Database 'db1', checkpoint=(912, 18), first=(912, 18), last=(912, 18).
Completed estimating recovery log boundaries for database 'db1'.
Started ANALYSIS pass for database 'db1'.
Completed ANALYSIS pass for database 'db1'.
Started REDO pass for database 'db1'. The total number of log records to process
is 1.
Completed REDO pass for database 'db1'.
Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.
Recovery of database 'db1' will undo incomplete nested top actions.
Database db1 is now online.

Restoring SQL Anywhere Backups

Restoring Image Backups

Use dbeng utility to start and recover the database for image based database files and transaction log files respectively.

Restoring a SQL Anywhere Data Backup

Use the following steps to restore an image data backup:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | File System | <Backup Set>.
  2. Right-click the Subclient created for SQL Anywhere data backups in the right pane and then click Browse and Restore.

  3. Click View Content.
  4. Select the database files that you want to restore and click Recover All Selected.
  5. Stop the database and rename the database files.
  6. In the Restore Options dialog box, select the Destination client from the dropdown list.
  7. Type the destination path or click Browse to select the destination path to restore the selected database files.

  8. From the CommCell Browser, navigate to Client Computers | <Client> | File System | <Backup Set>.
  9. Right-click the Subclient created for SQL Anywhere log backups in the right pane and then click Browse and Restore.

  10. Click View Content.
  11. Select the log files that you want to restore and click Recover All Selected.

  12. Click Advanced.
  13. Click Pre/Post tab.
  14. Click Browse to locate the recoverDBScript path and add the script for recovering the database and transaction log files.

    The script should contain the dbeng as a post recovery command. This command should include the database file and transaction log files location.

  15. Click OK.

The recoverDBScript dbeng for UNIX and Windows based file systems will be as follows:

dbeng11 -n <Server-Name> <Database-File> -ad <Transaction-Log-Files-Location>.

where

-ad : specifies the location of the transaction log files. The database server determines the correct order for applying the transaction logs to the backup copy of the database based on the log offsets.

Example (UNIX):

dbeng11 "/Sybase Anywhere/mydb/mydb.db" -ad /databackup/LogBackup

Example (Windows):

dbeng11 "d:\Sybase Anywhere\mydb\mydb.db" -ad d:\LogBackup

Use dbspawn command with dbeng utility to start the database server in the background.

Example:

dbspawn dbeng11 "/Sybase Anywhere/mydb/mydb.db" -ad /temp1/LogBackup(UNIX)

dbspawn dbeng11 "d:\Sybase Anywhere\mydb\mydb.db" -ad d:\LogBackup(Windows)

Restoring Dump Backups

Use dbisql utility to reload the dump file in to the database. Perform the following before restoring the dump based backup:

  • Drop the database which needs to be restored.
  • Initialize the same database using dbinit utility.

    For example, if you drop the mydb.db database, then you have to create and initialize the same mydb.db with the command dbinit mydb.db. Then, you must start the initialized database.

Use the following steps to restore a dump based backup:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | File System | <Backup Set>.
  2. Right-click the Subclient created for SQL Anywhere dump backups in the right pane and then click Browse and Restore.

  3. Click View Content.
  4. Select the data dump file and reload (sql) file that you want to restore and click Recover All Selected.
  5. Click Advanced.
  6. Click Pre/Post tab.
  7. Click Browse to locate the restoreDBScript file and add the script for recovering the reload sql file.

    The script should contain the dbisql as a post recovery command. This command should include the reload sql file which contains the path to the dump file to be applied to restore the content of the database. 

  8. Click OK.

The recoverDBScript dbisql for UNIX and Windows based file systems will be as follows:

dbisqlc -c “Connect-String” <Reload-SQL-File>

Example (UNIX):

dbisqlc -c "eng=demo11;dbn=mydb;uid=dba;pwd=sql" /temp/myDBReload.sql

Example (Windows):

dbisqlc -c "eng=demo11;dbn=mydb;uid=dba;pwd=sql" d:\myDBReload.sql

Managing Restore Jobs

For information on how to manage the job, see Job Controller Overview.

Browsing Data

For information on the advanced browse options, see Browse and Restore Data: Advanced.