SYBASE SQL Server System Administration Guide
|Chapter 7: Developing a Backup and Recovery Plan|
|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.
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.
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.
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.
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.
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.
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.
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.
|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
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.
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:
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: