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. When restoring an entire server, make sure to perform either of the following:

• If the dataserver is running, execute the following command to turn off these options:

sp_configure ’enable xact coordination’, 0

sp_configure "enable job scheduler", 0

• Disable the following parameters by editing the Sybase configuration file which usually resides in Sybase ASE home directory:

Set the ‘enable xact coordination = 0’ under “DTM Administration” section

Set the ‘enable job scheduler = 0’ under “SQL Server Administration” section

Prior to restoring an entire server, make sure that the Sybase server is offline. Use the following steps to restore an entire Sybase server:

From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> point to All Tasks and then click Restore Sybase Server. Select Sybase Create Devices. 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.

Prior to restoring the master database, make sure that the Sybase server is offline. Use the following steps to restore a master database without selecting Sybase create devices:

From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> and then click All Tasks | Browse Backup Data. Click OK. Select the Master database in the right pane and click Recover All Selected.
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 (model, sybsystemdb and sybsystemprocs) must be backed up.

Make sure to disable the following parameter in Sybase configuration file which usually resides in Sybase ASE home directory:

sp_configure ’enable xact coordination’, 0

By default, system databases are restored in the same location from where they were backed up and their data files are overwritten. Follow the steps given below to restore the system databases:

From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> and then click All Tasks | Browse Backup Data. Click OK. Select the system databases (model, sybsystemdb and sybsystemprocs) in the right pane and click Recover All Selected. Click OK.

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 performing the point-in-time restore of a database, ensure that transaction logs for the database are backed up. Follow the steps given below to restore a database(s) to a point in time:

From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> and then click All Tasks | Browse Backup Data. Click OK. In the right pane of the Browse window, select one or more databases and click Recover All Selected. Select the Restore Options check box. Click Point-in-Time. From the Restore Time list, select the date and time. Click OK to start the restore. The database(s) will be restored to the selected date and the next scheduled backup for that database will automatically convert 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 logs. The database backup job along with the most recent 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  etc 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.

Follow the steps given below to restore transaction logs along with the database(s) to a point in time:

From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> and then click All Tasks | Browse Backup Data. Click OK. In the right pane of the Browse window, select one or more databases and click Recover All Selected. Select the Restore Options check box. Click Point-in-Time. From the Restore Time list, select the date and time. Click OK to start the restore. The  transaction logs along with the database(s) will be restored to a selected point in time.

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

If you want to recover the database after the restore, follow the steps given below:

From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> and then click All Tasks | Browse Backup Data. Click OK. In the right pane of the Browse window, select the database to be recovered and click Recover All Selected. Click Recover. Click OK.

Restoring the Databases without Recovery

Follow the steps given below to restore the database without recovery:

From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> and then click All Tasks | Browse Backup Data. Click OK. In the right pane of the Browse window, select the databases you want to recover and click Recover All Selected. Click No Recover. Click OK.

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. Step-by-Step Restore is performed on individual database. This is a controlled restore and applicable for restoring a single database.

Use the following steps to run a Step-by-Step restore:

From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> and then click All Tasks | Browse Backup Data. Click OK. In the right pane of the Browse window, select the database to be restored and click Recover All Selected. In the Destination Server drop-down box, select the destination server name. Select the Restore Options check box. Select Sybase Step by Step. Click No Recover. Click OK to start the restore.

Renaming a Database

You can rename user databases. Ensure that the new database names are unique and conform to the Sybase Identifier rules. When you rename databases, make sure that the tables in the databases are not referencing to or referenced by table (s) in any other databases.

Use the following steps given to rename a database:

From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> and then click All Tasks | Browse Backup Data. Click OK. In the right pane of the Browse window, select the database to be renamed and click Recover All Selected. Select the Rename Database check box. Click Advanced. Click the Options tab. Click the Target Database column and type the new database name. Click OK.

Restoring Databases to a New Device

While restoring the Sybase databases to a new device, you can also change the size of the device. However, you need to drop the existing device to change the size and then perform the restore. Use the following steps to restore the selected databases to a new device:

From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> and then click All Tasks | Browse Backup Data. Click OK. In the right pane of the Browse window, select the databases you want to restore and click Recover All Selected. Select the Sybase Create Devices checkbox. Click Advanced. Click the Options tab. Type the new device name under the Device Name column and type the path of the new device under the Physical Path column. Type the new size under the Size Column. Click OK.

Restoring the database with the data and logs existing on the same device

If the Sybase data and logs are on the same device, use the following steps to set the data and logs and then perform a restore:

From the CommCell Browser, navigate to Client Computers. Right-click the <Client>, and then click Properties. Click the Registry Key Settings tab. Click Add. In the Name box, type sCreateDatabaseWithOverrideOption. In the Location box, select or type SybaseAgent from the list. In the Type box, select Value. In the Value box, type Y or y and then click OK.

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 in source server in the destination server.

Prior to restoring the database to a different server, note the following:

• The max page size of the source server should be the same as the destination server.
• Ensure that the Sybase destination server is configured in the CommCell console.

Follow the steps below to restore a database to a different destination server.

From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> and then click All Tasks | Browse Backup Data. Click OK. In the right pane of the Browse window, select the databases you want to restore and click Recover All Selected. 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. Click OK to start the restore.

Restoring the Sybase server to a Different Path

Make sure that you have a Sybase Database Administrator privileges when you restore a Sybase server to a different path. Also, make sure that the device should be able to hold the database being restored.

If an entire server needs to be restored to a different path, then perform the following before restoring the server:

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

Modify the RUN_File with the new master device path.

2. You need to restore the master database and the system databases like model and sybsystemdb, which reside on master device.
3. Restore the individual databases using Sybase create devices.

Use the following steps to restore individual databases to a different path:

From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> and then click All Tasks | Browse Backup Data. Click OK. In the right pane of the Browse window, select the databases you want to restore and click Recover All Selected. Select Sybase Create Devices. Click Advanced. Click Options tab. Double-click on the Device Name to change the device name. Double-click on the Physical Path to change the path of the database. Click OK to start the restore.

Command Line Restores

You can perform restores of one of more databases from the command line interface.

Command line restores enable you to perform restore operations on multiple clients at the same time. It also allows you to reuse the command line scripts for additional restores.

When performing command line restores, note that backups taken from the CommCell Console can be restored using Command Line and vice versa. However, backups taken from a previous version of the CommCell Console can be restored only from the Command Line.

In order to run the restores from command line, you need an input xml file which contains the parameters for configuring the restore options. This input xml file can be obtained using one of the following ways:

• Download the input xml file template and save it on the computer from where the restore will be performed.
• Generate the input xml file from the CommCell Console and save it on the computer from where the restore will be performed.

Log on to the Commserve

To run command line operations you must first login to the CommServe as follows:

• From Command prompt, navigate to <Software_Installation_Directory>/Base and run the following command:

  qlogin -cs <commserve name> -u <user name>

• For example, to log on to CommServe 'server1' with username 'user1':

  qlogin -cs server1 -u user1

Perform the Restore

1. Download the restore_template.xml file and save it on the computer from where the command will be executed.
2. Execute the saved xml script using qoperation execute command.

   qoperation execute -af restore_template.xml -clientName xxxxx -instanceName xxxxx -destinationInstance/clientName xxxxx -destinationInstance/instanceName xxxxx -databaseId/name xxxxx

3. Verify the status of the job using the following command:

   qlist job –j JOBID

4. Once the job completes, logout from the CommServe using the qlogout command.

   qlogout [-cs commserver] [-all] [-tf tokenfile] [-tk token] [-h]

Examples

Rename a Database Restore

1. Download the rename_restore_template.xml file and save it on the computer from where the command will be executed.
2. Execute the saved xml script using qoperation execute command.

   qoperation execute -af rename_restore_template.xml -sourceclientName xxxxx -sourceSybaseinstanceName xxxxx -destinationInstance/clientName xxxxx -destinationInstance/instanceName xxxxx -databaseId/name xxxxx - devices <source subclientId>:<source dbId>:<source device Id >:<source device name>:<renamedDeviceName>:<renamed device Path>:<size of the device in bytes>:<renamed Database name>  -renameDatabases true –databaseChain -sybaseCreateDevices false [-fromTimeValue <timeValue> -toTimeValue <timeValue>]

   Where

   -devices is a colon( : ) separated field and is comprised of 8 fields
   1. Source subclient Id: This is an internal field for subclientId and need to be passed as 0 in qcommand
   2. Source dbId: database Id of the Sybase dataserver (can be got from Sybase sysdatabases table)
   3. Source device Id: device id of the source device, can be passed as 0 in qcommand
   4. Source device Name: source Sybase device name can be got from Sybase sysdevices table
   5. Renamed device Name: Device name on destination (new name for the Sybase device)
   6. Renamed device Path: Destination server Device Path 
   7. Size of the device in bytes: If device is already created, this can be passed as 0
   8. Renamed Database Name: New name for the database on destination server. Restore will be performed on this database. Please note if DB is already created size should at least match the source
   Here if the given database has more than one device then –device needs to be repeated for each of the database devices.
   For cross-machine and rename database restores, it is recommended to create alFor cross-machine and rename database restores, it is recommended to create all the devices on the destination server before performing the restore. 

3. Verify the status of the job using the following command:

   qlist job –j JOBID

4. Once the job completes, logout from the CommServe using the qlogout command.

   qlogout [-cs commserver] [-all] [-tf tokenfile] [-tk token] [-h]

Finding a database ID from Sybase Dataserver

Login as Sybase SA user and execute the following SQL script:

isql -Usa –P<password>-S<Sybase server name> use master go select name, dbid from sysdatabases where name = ‘<dbname>’ go exit go

Finding an Endtime of the job for Point-in-time restore

Use the following command to find an end time of the job for Point-in-time restore:

./qoperation execscript -sn getjoblist.sql -si <clientName>

You will receive the list in the following format:

The command output returns the UTC time and you need to convert this into CommServe (CS) time zone before using it in restore command.

Examples

The following are the examples executed from your base directory for clientName - sybserver3, instance - S155syb3 database - db8 restored to the same server with the database renamed as test. Here we presume the devices are already created and only restore needs to be performed.

In the above examples, the following names are presumed:

rename_restore_template.xml is the XML template file

Generate the Command Line Script from the CommCell Console

In addition to the parameters provided in the template xml file, if you want to include additional options for the restore, you can do so by selecting the required options from the CommCell Console and generate the command line xml script for the restore operation.

Follow the steps given below to generate a script which you can use to perform a restore from the command line interface:

From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> and then click All Tasks | Browse Backup Data. Click OK. In the right pane of the Browse window, select the data that you want to restore and click Recover All Selected. Select the required restore options which you want to execute using the script. Click Save as Script.

Enter the location where you want to save the script or click Browse and navigate to the location. The script will be saved as a .xml file and a .bat file is created. If a file with the same name already exists in the specified location, the .xml file will be created with a timestamp. However, the .bat file will overwrite the existing file. Enter the username and password for the user account which you want to use to perform the restore. By default, the user account which you have used to login to CommCell console is used for performing the restore. However, if the user account does not have access to application and database, click Use a different account. Click OK.

Scheduling A restore

Follow the steps given below to schedule a restore job:

1.	From the CommCell Browser, navigate to Client Computers | <Client> | Sybase. Right-click the <Instance> and then click All Tasks | Browse Backup Data. Click OK.
2.	Select a database to restore. Click Recover All Selected.
3.	Select the required restore options. Click OK.
4.	Click Job Initiation tab and click Schedule. Click Configure.
5.	Select the appropriate scheduling options.  For example: Click Weekly. Check the days you want the run the restore job. Change the Start Time to 9:00 PM Click OK to close the Schedule Details dialog Click OK to close the Restore Options dialog The restore job will execute as per the schedule.

See Scheduling for a comprehensive information on scheduling jobs.

Managing Restore Jobs

Once you initiate the restore operation, a restore job is generated in the Job Controller. Jobs can be managed in a number of ways. See Job Management for a comprehensive information on managing jobs.

The following sections provide information on the different job management options available:

Restarting Jobs

Jobs that fail to complete successfully are automatically restarted based on the job restartability configuration set in the Control Panel. Keep in mind that changes made to this configuration will affect all jobs in the entire CommCell.

To Configure the job restartability for a specific job, you can modify the retry settings for the job. This will override the setting in the Control Panel. It is also possible to override the default CommServe configuration for individual jobs by configuring retry settings when initiating the job. This configuration, however, will apply only to the specific job.

Configure Job Restartability at the CommServe Level

From the CommCell Browser, click Control Panel icon. Select Job Management. Click Job Restarts tab and select a Job Type. Select Restartable to make the job restartable. Change the value for Max Restarts to change the maximum number of times the Job Manager will try to restart a job. Change the value for Restart Interval (Mins) to change the time interval between attempts for the Job Manager to restart the job. Click OK.

Controlling Jobs

The following controls are available for running jobs in the Job Controller window:

Suspending a Job

1. From the Job Controller of the CommCell Console, right-click the job and select Suspend.
2. The job status may change to Suspend Pending for a few moments while the operation completes. The job status then changes to Suspended.

Resuming a Job

1. From the Job Controller of the CommCell Console, right-click the job and select Resume.
2. As the Job Manager attempts to restart the job, the job status changes to Waiting, Pending, or Running.

Killing a Job

1. From the Job Controller of the CommCell Console, right-click the job and select Kill.
2. Click Yes when the confirmation prompt appears if you are sure you want to kill the job. The job status may change to Kill Pending for a few moments while the operation completes. Once completed, the job status will change to Killed and it will be removed from the Job Controller window after five minutes.

Additional Restore Options

Several additional options are available to further refine your restore operations. The following table describes these options, as well as the steps to implement them.

Be sure to read the overview material referenced for each feature prior to using them.