Restoring a Database or Applying a Transaction Log

If the device that a database resides on fails and the database is damaged, you can restore the database by reloading the most recent database backup and the succeeding transaction log backups. Or if you find that you have some corrupt data on the database, you can reload a database backup over the old database.

When a database is restored from a backup, that database must not be in use. Any data in the specified database is replaced by the loaded data.

If you are reloading a database because of media failure, you must drop the damaged database by using DBCC or the sp_dbremove system stored procedure, re-create the database, and then restore the database from a backup.

Loading a transaction log results in re-execution of the changes it contains and in rolling back any transactions that were uncommitted when the transaction log was backed up.

Backups of the transaction log must be loaded in the sequence in which they were made. SQL Server checks the time stamps on each dumped database and each dumped transaction log to see that the sequence is correct. When the entire sequence of transaction log dumps has been loaded, the database is restored to its state at the time of the last transaction log dump (minus active transactions). This recovers as much of a database as possible in case of system failure.

Note Restoring the master database is a special case. For information, see Restoring the master Database, later in this chapter.

You can also dump a database and load that dump into another database. However, the new database must be at least as large as the dumped database that is being loaded. (Segment mappings must also be the same. For more information, see More About Backing Up and Restoring, later in this chapter.)

Note If the msdb database is to be restored from a backup, the SQLExecutive service must be stopped before the restore begins and then restarted after the restore is complete. If this is not done, then SQL Executive will not function properly.

    To restore a database or apply a transaction log

1. From the Server Manager window, select a server.
2. From the Tools menu, choose Backup/Restore.

   The Database Backup/Restore dialog box appears.

3. Select the Restore tab.

   

4. To display information about the backups that exist on a dump device, select the device's "" box.

   A list of backups on that dump device appears. This information includes the number of the backup, the database name, the date the backup was created, and the size of the backup. An icon indicates whether the backup was of a transaction log or was a complete database dump.

5. For volume or header information about the backups on a disk or tape dump device, select the device from the Dump Devices list, and choose the Info button.

   The Header Info Dialog box appears. For information about this dialog box, see Viewing Volume and Header Information, later in this chapter.

6. To select the device that will be restored from, select a device from the Dump Devices list, and then choose Add. The added device moves to the Restore Locations list.

   Repeat this step for each device to restore from. If you have dumped the information to one or more disk devices, you must restore the information from all those devices. If a dump was made entirely to tape dump devices, you can restore from fewer tape dump devices than were originally dumped to, as long as all tapes from that dump are provided.

   If necessary, you can add a dump device (for example, to restore from a network path) by choosing New and completing the Create Dump Device dialog box that appears.

   You cannot restore from diskette dump devices when using SQL Enterprise Manager. For information about restoring from diskette dump devices, see the Microsoft SQL Server Transact-SQL Reference. For more information about dump devices, see Chapter 5, Managing Devices.

7. To specify a volume name for a device, type up to six characters (letters or numbers) in the Volume box of the Backup Destination list. A volume name is optional, but if you enter one, it must match the volume name on the device.

   To determine the volume name, select the device from the Dump Devices list and then choose the Info button.

8. From the Database list, select the database to restore.
9. Select either Load Database or Apply Transaction Log.
   • Load Database restores the entire database.
   • Apply Transaction Log restores the transaction log information, reexecutes the changes it contains, and rolls back any transactions that were uncommitted when the transaction log was backed up.
10. Select or clear the restore options.
    • Skip Tape Header specifies that the server will not read the ANSI header on the tape.
    • Unload Tape at End automatically rewinds and unloads the tape when a restore from a tape device completes.
    • File Number specifies the file number of the dump to restore for dumps containing multiple files.
11. Choose the Restore button.

You can also restore a database or apply a transaction log by using the LOAD statement. For information, see the Microsoft SQL Server Transact-SQL Reference.

Note If you have loaded a dump into a new database that was created using the FOR LOAD option, after completing the load(s) you must clear the database option DBO Use Only by using SQL Enterprise Manager or sp_dboption. Until you do this, users other than DBO may not access the database.

For more information about restoring databases, see More About Backing Up and Restoring, later in this chapter.