Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search

Automatic Recovery After a System Failure or Shutdown


[Table of Contents] Designating Responsibility for Backups

SYBASE SQL Server System Administration Guide

[-] Chapter 7: Developing a Backup and Recovery Plan
[-] Using the Dump and Load Commands

Using the Dump and Load Commands

In case of media failure, such as a disk crash, you can restore your databases if-and only if-you have been making regular backups of the databases and their transaction logs. Full recovery depends on the regular use of the dump database and dump transaction commands to back up databases and the load database and load transaction commands to restore them. These commands are described briefly below and more fully in Chapter 8, "Backing Up and Restoring User Databases" and Chapter 9, "Backing Up and Restoring the System Databases" .

Warning! Never use operating system copy commands to copy a database device. Loading the copy into SQL Server causes massive database corruption.

Checking Database Consistency: dbcc

The dump commands can complete successfully even if your database is corrupt. Before you back up a database, use the dbcc commands to check its consistency. See Chapter 6, "Checking Database Consistency" , for more information.

Making Routine Database Dumps: dump database

The dump database command makes a copy of the entire database, including both the data and the transaction log. dump database does not truncate the log.

dump database allows dynamic dumps: users can continue to make changes to the database while the dump takes place. This feature makes it convenient to back up databases on a regular basis.

The dump database command executes in three phases. A progress message informs you when each phase completes. When the dump is finished, it reflects all changes that were made during its execution, except for those initiated during Phase 3.

Making Routine Transaction Log Dumps: dump transaction

Use the dump transaction command (or its abbreviation dump tran ) to make routine backups of your transaction log. dump transaction is similar to the incremental backups provided by many operating systems. It copies the transaction log, providing a record of any database changes made since the last database or transaction log dump. Once dump transaction has copied the log, it truncates the inactive portion.

dump transaction takes less time and storage space than a full database backup, and is usually run more often. Users can continue to make changes to the database while the dump is taking place. You can run dump transaction only if the database stores its log on a separate segment.

After a media failure, use the with no_truncate option of dump transaction to backup your transaction log. This provides a record of the transaction log up to the time of the failure.

Copying the Log After Device Failure: dump tran with no_truncate

When your data device fails and the database is inaccessible, use the special with no_truncate option of the dump transaction command to get a current copy of the log. This option does not truncate the log. You can use it only if the transaction log is on a separate segment and the master database is accessible.

Restoring the Entire Database: load database

Use the load database command to load the backup created with dump database . You can load the dump into a pre-existing database, or create a new database with the for load option. When you create a new database, allocate at least as much space as was allocated to the original database.

Warning! You cannot load a dump that was made on a different platform or with an earlier version of SQL Server.
If the database you are loading includes tables that contain the primary keys for tables in other databases, you must load the dump into the same database name.

Put the database into single-user mode so that users cannot make changes from the time you begin loading it until you have applied the last transaction log dump. Before loading a database, lock it by using sp_dboption to set the no chkpt on recovery, dbo use only, and read only options to TRUE.

Applying Changes to the Database: load transaction

Once you have loaded the database, use the load transaction command (or its abbreviation, load tran ) to load each transaction log dump in the order in which it was made. This process reconstructs the database by re-executing the changes recorded in the transaction log.

Note: If users have made any changes between the load database and load transaction commands, the load transaction fails. Lock the database, then reload it and re-apply the transaction log dumps.

When the entire sequence of transaction log dumps has been loaded, the database reflects all transactions that had committed at the time of the last transaction log dump. Use sp_dboption to reset the no chkpt on recovery, dbo use only, and read only options to FALSE so that users can resume making changes to the database.

Using the Special dump transaction Options

Under certain circumstances, the simple model described above does not apply. Table 7-2 describes when to use the special with no_log and with truncate_only options instead of the standard dump transaction command.

Table 7-2: : When to Use dump transaction with truncate_only or with no_log
When Use
The log is on the same segment as the data dump transaction with truncate_only to truncate the log
dump database to copy the entire database, including the log
You are not concerned with the recovery of recent transactions (for example, in an early development environment) dump transaction with truncate_only to truncate the log and
dump database to copy the entire database
Your usual method of dumping the transaction log (either the standard dump transaction command or dump transaction with truncate_only ) fails because of insufficient log space dump transaction with no_log to truncate the log without recording the event and
dump database immediately after to copy the entire database, including the log

Using the Special Load Options to Identify Dump Files

Use the special with headeronly option to provide header information for a specified file or for the first file on a tape. Use the with listonly option to return information about all files on a tape.

Note: These options do not actually load databases or transaction logs on the tape.

Backup and Recovery Illustrated

Figure 7-1 illustrates the process of restoring a database that is created at 4:30pm on Monday and dumped immediately after. Full database dumps are made every night at 5pm. Transaction log dumps are made at 10am, noon, 2pm, and 4pm every day:

Figure 7-1: Restoring a Database from Backups

If the disk that stores the data fails on Tuesday at 6:00 pm, use the following steps to restore the database:

  1. Use dump transaction with no_truncate to get a current transaction log dump .

  2. Use load database to load the most recent database dump, Tape 6.

  3. Use load transaction to apply the most recent transaction log dump, Tape 7.

Figure 7-2 illustrates how to restore the database when the data device fails at 4:59 pm on Tuesday-just before the operator is scheduled to make the nightly database dump:

Figure 7-2: Restoring a Database, A Second Scenario

Use the following steps to restore the database:

  1. Use dump transaction with no_truncate to get a current transaction log dump on Tape 6 (the tape you would have used for the routine database dump) .

  2. Use load database to load the most recent database dump, Tape 1.

  3. Use load transaction to load Tapes 2, 3, 4, and 5 and the most recent transaction log dump, Tape 6.


Automatic Recovery After a System Failure or Shutdown


[Table of Contents] Designating Responsibility for Backups