Lesson 2: The RESTORE Statement

[Previous] [Next]

The RESTORE statement retrieves information about a backup set or backup device before you restore a database, file, or transaction log. This lesson describes how to use the RESTORE statement and its options.

After this lesson, you will be able to

Use the RESTORE statement to get information about a backup set or backup device before you restore a database, file, or transaction log
Describe the purpose of various important RESTORE statement options

Estimated lesson time: 30 minutes

Preparing to Restore a Database

You should verify backups before restoring them. Do this to confirm that you are restoring the intended data and objects and that the backup contains valid information. This section explains how to verify backups and describes other tasks you should perform to ensure a smooth restore process.

Verifying Backups

Before you perform a restore, you must ensure that the backup device is valid and contains the expected backup set(s). You can use SQL Server Enterprise Manager to view the property sheet for each backup device. For more detailed information about the backups, you can execute the Transact-SQL statements described in the sections that follow.

The RESTORE HEADERONLY Statement

Use the RESTORE HEADERONLY statement to obtain a list of header information for all backup sets on a backup device.

When you execute the RESTORE HEADERONLY statement, the information you receive includes

The backup set name and description

The backup method: 1 = complete database, 2 = transaction log, 4 = file, and 5 = differential database

The position of the backup set on the device; this is needed for the FILE option of the RESTORE statement

The type of backup medium: 5 or 105 = tape, and 2 or 102 = disk

The date and time that the backup was performed

The size of the backup

The RESTORE FILELISTONLY Statement

Use the RESTORE FILELISTONLY statement to obtain information about the original database or transaction log files that are contained in a backup set. Executing this statement can help you avoid restoring the wrong backup files.

When you execute the RESTORE FILELISTONLY statement, SQL Server returns the following information:

The logical name(s) of the data and transaction log file(s)

The physical name(s) of the data and transaction log file(s)

The type of file, such as a data (D) or a transaction log (L) file

The filegroup membership

The backup set size, in megabytes (MB)

The maximum allowed file size, in MB

The RESTORE LABELONLY Statement

Use the RESTORE LABELONLY statement to obtain information about the backup medium that holds a backup device. This is useful if you are using multiple backup devices in a media set.

The RESTORE VERIFYONLY Statement

Use the RESTORE VERIFYONLY statement to verify that a backup set is complete and that all backup devices are readable. SQL Server does not verify the structure of the data contained in the backup.

Other Tasks to Perform Before Restoring Backups

Before you restore backups, you should restrict access to the database, back up the transaction log, and switch to the master database, as described in the sections that follow.

Set the dbo use only Database Option

A member of the sysadmin or db_owner role should set the dbo use only database option to true before restoring the database. This restricts access to the database so that users cannot interfere with the restore. You can set the dbo use only database option in the following ways:

Using the database property sheet in SQL Server Enterprise Manager

Using the sp_dboption system stored procedure and setting the dbo use only database option to true, as follows:

EXEC sp_dboption database_name, 'dbo use only', true

Back Up the Transaction Log

Database consistency is ensured if you back up the transaction log before you perform any restore operations. The following are some considerations regarding the transaction log backup:

The transaction log backup is used to recover the database as the last step in the restore process.

If you do not back up the transaction log before you restore a backup, you lose data modifications that occurred between the last transaction log backup and the time when the database was taken offline.

In some cases it may not be possible to back up the transaction log before beginning the restore—for example, if the trunc. log on chkpt database option is set to true, or if you have used the BACKUP LOG statement with the TRUNCATE_ONLY or NO_LOG option since the last complete database backup.

Switch to the master Database

If you are using SQL Server Query Analyzer or executing a script from the command prompt to restore backups, you should execute the following command before beginning to restore the database:

USE master

This ensures that you are not using the database that you are trying to restore.

Using the RESTORE Statement

You can use the RESTORE statement or SQL Server Enterprise Manager to perform restore operations. The RESTORE statement has various options that allow you to specify how the backup should be restored. Each of these options has a counterpart in SQL Server Enterprise Manager. This section describes the RESTORE statement and the various restore options.

The syntax for the RESTORE database statement is as follows:

RESTORE DATABASE database_name __ [FROM <backup_device> [, ... n]] [WITH       [[,] FILE = file_number]      [[,] MOVE 'logical_file_name' TO operating_system_file_name']      [[,] REPLACE]      [[,] {NORECOVERY | RECOVERY | STANDBY = undo_file_name}]]

 where <backup_device> is 
      {{backup_device_name | @backup_device_name_var}      | 
      {DISK | TAPE | PIPE} =  {'temp_backup_device' |  @temp_backup_device_var}
      }

NOTE
The RESTORE statement replaces the LOAD statement found in previous versions of SQL Server. LOAD is still supported for backward compatibility only. You should use the RESTORE statement.

The following example restores the Northwind database from a named backup device.

USE master RESTORE DATABASE northwind FROM nwindbac

The RECOVERY Option

In SQL Server Enterprise Manager, the RECOVERY option corresponds to specifying Leave Database Operational. No Additional Transaction Logs Can Be Restored for the Recovery Completion State on the Options tab of the Restore Database dialog box.

Use this option to return the database to a consistent state when restoring

The last transaction log backup

A complete database backup without transaction log backups

A differential database backup without transaction log backups

This option causes SQL Server to roll back any uncommitted transactions and roll forward any committed transactions that were restored from the backup. The database is available for use after the recovery process is complete.

NOTE
Do not use the RECOVERY option if you have additional transaction logs or differential backups that must be restored, because this option indicates that you are restoring the final backup and brings the database back online.

The NORECOVERY Option

In SQL Server Enterprise Manager, the NORECOVERY option corresponds to specifying Leave Database Nonoperational, But Able To Restore Additional Transaction Logs for the Recovery Completion State on the Options tab of the Restore Database dialog box.

Use this option when you have multiple backups to restore. Keep the following in mind:

Specify the NORECOVERY option for all backups except the last backup to be restored.

When this option is specified, SQL Server neither rolls back any uncommitted transactions in the transaction log nor rolls forward any committed transactions.

The database is unavailable for use until the database is recovered.

The FILE Option

In SQL Server Enterprise Manager, the FILE option is specified by checking Restore for one or more backup sets in the restore list on the General tab of the Restore Database dialog box.

Use this option to select specific backups from a backup device that contains multiple backups. You must specify a file number that corresponds to the order in which the backup sets exist within the backup device. The position column of the output from the RESTORE HEADERONLY statement gives the file number of each backup.

The MOVE TO Option

In SQL Server Enterprise Manager, the MOVE TO option is specified by changing the Restore As name for one or more files in the Restore Database Files As list on the Options tab of the Restore Database dialog box.

Use this option to specify where to restore data or log files if you are restoring the files to a different location, such as a different disk drive or server or a standby SQL Server.

You are required to specify the existing logical name and the new location for the file(s) in the backup that you wish to move. Use the RESTORE FILELISTONLY statement to determine the existing logical filenames if these are not known.

TIP
You can copy the files from a database to a different location or SQL Server and use either the sp_attach_db or the sp_attach_single_file_db system stored procedure to bring the database online from its new location or on the new server.

The REPLACE Option

In SQL Server Enterprise Manager, the REPLACE option is specified by checking Force Restore Over Existing Database on the Options tab of the Restore Database dialog box.

Use the REPLACE option only if you want to replace an existing database with data from a backup of a different database.

By default, SQL Server performs a safety check that ensures that an existing database is not replaced if

You are specifying a new name for the database being restored and a database with that name already exists on the target server.

The set of files in the database is different from the files contained in the backup set. SQL Server ignores differences in file size.

When you specify the REPLACE option, no checks are performed, and SQL Server will overwrite the existing database, if one exists.

Preparing to Restore a Database

Verifying Backups

Other Tasks to Perform Before Restoring Backups

Using the RESTORE Statement

Lesson Summary