Restoring Data from a Backup

When disasters occur and you need to get the data back to a known state, you need to perform a restore operation. At other times, you might want to restore data, such as when preparing for log shipping or copying data from one server to another. You can also use the RESTORE command to validate backups or read the catalog information from an existing media set.

Restore operations, just like backups, can be performed from the SQL Server Management Studio interface. For the 70-431 exam, as well as for the greatest flexibility, you must also know the command options for the RESTORE statement and how to apply them. Figure 6.3 shows the options available with the restore process in Server Management Studio.

Figure 6.3. The Restore Database dialog's options.

On the General page, you essentially select what you intend to restore. If you leave the default settings, the interface automatically selects the options that provide you with the most recent data possible. The backups selected at the bottom of the interface indicate what is going to be restored. You can alternatively select a specific point in time (equivalent to using the RESTORE statement with the STOP_AT option); in this case, the appropriate selections are made at the bottom of the interface, as shown in Figure 6.4.

Figure 6.4. A point-in-time restore.

If you make a selection yourself from the bottom of the dialog, as shown in Figure 6.4, you end up restoring the database to the end of what is in the last backup selected.

If you have created an extra backup or you want to restore data backed up from another server, you can use the From Device option and browse to the location where the backup is stored.

If you want to create a copy of the database for test purposes, you can restore the data into a different database than the one originally backed up by selecting the appropriate receiving database from the To Database drop-down list.

The Options page of the Restore dialog allows you to fine-tune a restore operation as desired. Most of the options in Server Management Studio are fairly straightforward and self-explanatory.

You can choose to overwrite data in any existing database. You might need to select this option when restoring data that was originally in another database or on another server. You have to change the file locations if the original paths are different from the destination file paths.

You can preserve the replication settings when restoring is available only by selecting the option Leave the Database Ready for Use by Rolling Back the Uncommitted Transactions, which is equivalent to restoring a backup with the RECOVERY option. If you select this option, SQL Server prevents replication settings from being removed when a database backup or log backup is restored on a warm standby server and the database is recovered.

When you select Prompt Before Restoring Each Backup, you must confirm each backup set before you restore it. This option is useful when you must swap tapes for different media sets when the server has only one tape device.

Restrict Access to the Restored Database makes the restored database available only to the members of db_owner, dbcreator, or sysadmin. This helps prevent users from accessing the data and gives administrators the ability to perform further operations.

On the bottom of the restore dialog, you can select the recovery options desired. The default, RESTORE WITH RECOVERY, places the database in a standard usable state. If you want to apply further backups, as in the case of a warm secondary database, you can choose to leave the database non-operational by selecting RESTORE WITH NORECOVERY. When creating a read-only secondary server, you can select RESTORE WITH STANDBY and provide the path to the undo file.

Exam Alert

Be prepared for exam questions pertaining to point-in-time recovery and the setup of secondary databases. Both topics are common on Microsoft exams.

As you can see from the restore dialog, Server Management Studio provides similar options to those available when you use the T-SQL RESTORE statement.

Using the T-SQL RESTORE Statement

You can use the T-SQL RESTORE statement to recover a database and restore backups. This command enables you to perform a variety of recovery procedures and restore an entire database, a portion of a database, a specific file or files, a specific filegroup or filegroups, a specific page or pages, or a log. You can also use options of this command to verify a backup or read information from existing backup media.

Many options are available for use with the T-SQL RESTORE statement. In addition, there are several options with BACKUP, but many of them are the same and perform in a similar manner. This chapter concentrates on the ones you are most likely to use that have not been discussed yet in this chapter. For more information on other options and more complete syntax examples, consult SQL Server Books Online.

The following are the most commonly used features of the RESTORE statement that we have yet to discuss:

• FILELISTONLY RESTORE FILELISTONLY returns a result set that contains a list of the database and log files contained within the backup.

• HEADERONLY RESTORE HEADERONLY returns a result set that contains all the backup header information for all backup sets on a particular device.

• LABELONLY RESTORE LABELONLY returns a result set that contains information about the backup media identified by the given device.

• VERIFYONLY You use RESTORE VERIFYONLY to verify a backup. This option tells SQL Server to ensure that the backup set is complete and the entire backup is readable, but it does not attempt to verify the structure of the data contained in the backup volumes. In Microsoft SQL Server 2005, RESTORE VERIFYONLY has been enhanced to do additional checking on the data to increase the probability of detecting errors. The goal is to be as close to an actual restore operation as practical.

This shortened list of options is used to gain information from backup devices without actually performing a restore. These options are often used for obtaining information about the contents of the media created from other databases and/or servers.

Exam Alert

For the 70-431 exam, you need to be able to differentiate between the functionality of the FILELISTONLY, HEADERONLY, and LABELONLY options.

You can set up backup and restore operations as scheduled jobs, as discussed later in this chapter.