Section 17.7. Recovering Your Database

17.7. Recovering Your Database

The syntax of recovering your backups is identical to that of backing them up. The keyword backup is replaced with the keyword restore and the keyword to is replaced with from.

17.7.1. Recovering from a Disaster

Disaster recovery is different from a normal restore. You do, however, need a plan in the unlikely case that a catastrophic failure occurs. You must plan for location outages, system outages, component outages, and database corruption. You should have an idea of how to recreate your system and recover each of your databases from backups.

The fundamentals of disaster recovery are not covered here (see Chapter 24 for treatment of this topic). You will need access to your configuration audits and database backups on whatever system you eventually want to restore from. Be sure to think about the basics of your disaster planconsiderations like ensuring that the tape backup of your database dump files occurs after your database dumps complete.

The most common systems failure you will face is a disk drive failure. The most obvious way to protect your systems is therefore to always use disk systems protected by RAID. Most large organizations require RAID on all systems; disk failures are too common a condition to ignore, and there are significant administrative and downtime costs to these failures even if no data is lost.

The case in which your database server fails catastrophically is rare but can happen. To deal with this situation, you should be able to restore your system from scratch. This includes having all necessary information and software to do this recovery.

17.7.2. Restoring from Backups

Loading a Sybase database from a database dump file is not a quick process. You can estimate that it will take between 1 and 2 hours per 100 GB of database space, so you should plan accordingly. You will also need to budget time to apply transaction log backups if that is necessary. The database into which you restore your backups needs to be created in the exact same order and size as the original database. There is no easy stored procedure available in the default installation of Sybase that details this information for you, but all major Sybase tools (including Sybase Centralthe enterprise manager that Sybase ships) can rebuild these statements for you. If you are trying to restore a backup into a second server or into a server that has no existing database of the appropriate name, you need to recreate the database using commands like the following:

C:> create database mydb on mydefseg = 40, mydefseg = 40         log on mylogseg = 10 C:> alter database mydb on mydefseg = 40

I also distribute a widely used free set of Sybase and SQL Server extended stored system procedures from my web site http://www.edbarlow.com. The stored procedure sp_ _revdb dbname can be used to reverse-engineer the layout of your databases; you can download it from my site.

Once the database creation is complete, the full dump of the database needs to be applied using the load command, as shown in Example 17-2. The load command is identical in structure to the dump command.

Example 17-2. Sample load database command

1> load database mydb from '/sybase/backups/mydb.990312.bck' 2> go Backup Server session id is:  26.  Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server. Backup Server: 6.28.1.1: Dumpfile name 'mydb9909106EF4   ' section number  0001 mounted on disk file '/sybase/backups/mydb.990312.bck' Backup Server: 4.58.1.1: Database mydb: 17926 kilobytes LOADed. Backup Server: 4.58.1.1: Database mydb: 19462 kilobytes LOADed. Backup Server: 4.58.1.1: Database mydb: 19470 kilobytes LOADed. Backup Server: 3.42.1.1: LOAD is complete (database mydb). Use the ONLINE DATABASE command to bring this database online; SQL Server  will not bring it online automatically.

As noted in the output, you will need to run the online database dbname TSQL command to activate your database after a load. If there are no transaction logs to apply for this database, you can immediately run online database dbname to activate the database. If you wish to apply transaction logs, you should apply them first and then run the online database command.

To apply a transaction log to the database, use the load transaction command. To restore the transaction logs for the database mydb in the preceding dump example, enter the command shown in Example 17-3.

Example 17-3. Sample load transaction command

1> load transaction mydb from '/sybase/backups/mydb.tlogdmp' 2> go Backup Server session id is:  28.  Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server. Backup Server: 6.28.1.1: Dumpfile name 'mydb9909106F49   ' section number  0001 mounted on disk file '/sybase/backups/mydb.tlogdmp' Backup Server: 4.58.1.1: Database mydb: 24 kilobytes LOADed. Backup Server: 3.42.1.1: LOAD is complete (database mydb). Use the ONLINE DATABASE command to bring this database online; SQL Server  will not bring it online automatically.

Repeat the transaction log loads until there are no more transaction logs to apply. It is strongly recommended that you dump your transaction logfiles to a filename that contains a timestamp in the format yyyymmdd.hhmmss. If you do this, you can list the files, create a command batch, and then restore the transaction logs in order.

17.7.2.1. The online database command

When this process is done, the database has been restored completely and should be brought online using the online database mydb command.

17.7.2.2. Restoring to a specific time

Sybase can also use saved transaction logs to restore a database to a specific point in time. So, if someone added important data at 12:00 p.m. and then someone else deleted this data by accident at 2:00 p.m., you can restore from a backup and apply saved transaction logfiles to restore the database to exactly 1:59 p.m. Data is back, and the users are happy.

To recover deleted production data without having production downtime, you can load backups of the database into development, load the saved transaction logfiles, and then use a tool such as bcp to copy the data from development to production.

To specify the time to which the database should be restored, use the until_time parameter. This parameter takes a single value of a time and date in the default format for the dataserver. For example, to restore a database up to April 1, 2007 at 12:34:32:650 a.m., first apply the full database dump, then apply all transaction log dumps up to the one that contains the stop time. With this dump, add until_time, like this:

load transaction mydb  from '/sybase/backups/mydb.tlogdmp' with until_time = 'April 1, 2007 at 12:34:32:650AM'

17.7.2.3. Restoring from compressed backups

As noted in the backup section, you can back up your data to compressed files using the syntax compress:: compression_level ::filename. One final difference between the dump and load commands is that the load command does not need the compression level specified. The load database command can figure out the compression level from the backup file. The syntax for loading compressed files is therefore compress::::filename.