Performing a Database Restore | Microsoft SQL Server 2000 Administrators Companion

3 4

As mentioned, you can perform a restore operation by using either Enterprise Manager or T-SQL commands—both techniques produce the same results. Unlike for backup operations, for restore operations, SQL Server does not provide a wizard.

Using Enterprise Manager to Perform a Restore

To perform a restore operation by using Enterprise Manager, follow these steps:

In Enterprise Manager, right-click the name of the database you want to restore, point to All Tasks in the shortcut menu, and then choose Restore Database to display the Restore Database dialog box, shown in Figure 33-1.

Figure 33-1. The General tab of the Restore Database dialog box.
At the top of the General tab is the Restore As Database drop-down list, which enables you to specify what database the backup will be restored as. Figure 33-1 shows the Example database selected.
You are not required to restore a database directly—in fact, at times, you will definitely need to restore a database using a different database name. For instance, suppose a user accidentally deletes a table. If you were to restore the entire database, you would replace all data with the earlier data. Instead, you can restore the data to a database with a different name, extract the deleted table, and insert the table into the live database.
Next specify the type of restore operation: Database, Filegroups Or Files, or From Device. The Database option lets you specify the database to restore. The Filegroups Or Files option lets you specify filegroups or files to restore. The From Device option lets you specify the device to restore from, and the contents of the device will then determine the type of restore. Figure 33-1 shows the Database option selected.
The Parameters area enables you to set options such as whether the backups of other databases should be shown (for restoring from another database's backup), which backup should be restored first (if multiple backup sets are available), and whether a point-in-time restore should be performed. A point-in-time restore enables you to restore data to the state it was in at a particular instant. For instance, if you accidentally deleted a table at 12:01, you could use a point-in-time restore to restore the database to the state it was in at 12:00, just before the deletion. Because you have the list of all of the backups available, you can choose the one that you want to use. You are not required to restore the most recent backup.
In the Restore Database dialog box, you can also select a backup set and then view its properties by clicking Properties. A sample Backup Set Properties window is shown in Figure 33-2.

Figure 33-2. The Backup Set Properties window.
Click OK to return to the General tab of the Restore Database dialog box, and click Filegroups Or Files to display a slightly different view, shown in Figure 33-3. All of the file and filegroup backups set up for the Example database are displayed in Figure 33-3. To view the properties for the file and filegroup backups, select a backup and click Properties.

Figure 33-3. The General tab of the Restore Database dialog box, after Filegroups Or Files is clicked.
Now select From Device, as shown in Figure 33-4. As mentioned, you use this option when you want to select a particular backup device to restore from. If you specify this option, you must manually select the backup set and then specify whether SQL Server should perform a complete restore, a differential restore, a transaction log restore, or a file or filegroup restore. You can also have SQL Server read the backup set information and store it with other backup history information in the msdb database. Then the information about that backup is available in case you want to perform a database restore.

Figure 33-4. The General tab of the Restore Database dialog box, after From Device is clicked.
Click the Options tab of the Restore Database dialog box, shown in Figure 33-5. At the top of this tab, you will see three check boxes. The Eject Tapes After Restoring Each Backup check box can be used to guarantee that the tape won't remain in the tape drive and be overwritten. Selecting the Prompt Before Restoring Each Backup option gives you the opportunity to change your mind about performing the backup. And selecting the Force Restore Over Existing Database check box lets you overwrite the existing database with the database restore. On this tab, you can also restore the database under a new data filename, which can be useful if you want to save the original database.

Figure 33-5. The Options tab of the Restore Database dialog box.

The remaining options on this tab let you specify in what state the database should be left after the recovery is completed, as follows:
- Leave Database Operational. No Additional Transaction Logs Can Be Restored. This option allows no further recovery of differential restores or transaction log restores. This option essentially sets the RECOVERY flag on the restore. You cannot restore transaction log backups if this option is set.
- Leave Database Nonoperational But Able To Restore Additional Transaction Logs. This option sets the NORECOVERY flag on the restore. With this flag set, you can apply further differential backup restores and transaction log restores. While this restore is under way, the database is nonoperational, meaning that users cannot access the database until you finish the entire restore.
- Leave Database Read-Only And Able To Restore Additional Transaction Logs. This option will also set the NORECOVERY flag on the restore, and you can perform differential backup restores and transaction log restores. Unlike the previous option, this option allows users to have read-only access to the database while you are performing the restore operation.
When you have finished setting the options, click OK to begin the restore operation. You will be kept informed of the restore operation's progress via a message box that contains a status bar, as shown in Figure 33-6. When the operation is completed, you will see a status box informing you of the success or failure of the restore.

Figure 33-6. The Restore Progress message box.

NOTE
A SQL Server backup device can hold the output of many backup operations. The converse is also true: a single backup can be split among several devices. The contents of the group of backup devices that collectively form one backup are called a backup set. Thus, a backup device (tape, disk file, and so on) can hold parts of many backup sets for different databases or many backup sets for the same database.

Using T-SQL to Perform a Restore

The RESTORE T-SQL command is similar to the BACKUP command (covered in Chapter 32). Like the BACKUP command, RESTORE can be relatively difficult to use at first, but some DBAs prefer to put their administrative procedures into SQL scripts so that they can be run over and over again. And like the BACKUP command, the RESTORE command provides a few more options than using Enterprise Manager does.

In this section, we'll look at the syntax of the RESTORE command and the various options this command provides. The RESTORE command has two forms, shown here:

RESTORE DATABASE Restores either the entire database or a file or filegroup
RESTORE LOG Restores the transaction log

As you can see, which command you use depends on the type of restore operation you are performing. Because these commands have most of the same options, we'll look at all the options for both types of restores (database and log) in a single list later in this section.

The RESTORE Statement

The syntax for the RESTORE statement when a full database restore is performed is shown here:

 RESTORE DATABASE database_name [ FROM backup_device ] [ WITH options ]

This statement requires only the database name and the location of the backup.

The syntax for the statement when a file or filegroup restore is performed is shown here:

 RESTORE DATABASE database_name  [FILE = file_name ] [FILEGROUP = filegroup_name ] [ FROM backup_device ] [ WITH options ]

This statement requires only the database name, the filename or filegroup name, and the location of the backup.

The syntax for the statement when a transaction log restore is performed is shown here:

 RESTORE LOG database_name [ FROM backup_device ] [ WITH options ]

With any of these commands, database_name is the name of the database that the restore will be performed on. The backup_device parameter is either a logical backup device name or the name of a physical device. If you want to specify a physical device, you must qualify it with a device type—that is, the name of the device must be preceded by DISK =, TAPE =, or PIPE =. You can specify one or more devices. (You separate multiple device names by using commas.)

NOTE
If you don't specify the FROM clause, a restore will not take place, but a recovery will still be performed (unless you specify the NORECOVERY option). This technique can be used to set a database to recovery mode without restoring any additional data. For example, you can perform several differential restore operations and then run the RESTORE statement without the FROM clause to set the database to recovery mode, thus launching the recovery process.

Options

Table 33-1 lists the options available for use with the RESTORE command. As you will see, these options provide a great deal of flexibility in performing the restore operation. (One of the options listed is available for only transaction log restores; this limitation is noted.)

Table 33-1. The RESTORE command options

Option	Description
RESTRICTED_USER	Sets the security of the newly restored database so that members of only the db_owner, dbcreater, and sysadmin roles can access it.
FILE = file_number	Identifies the backup set to be used if more than one set exists on a medium. For example, setting this value to 2 will cause the second backup set on the medium to be used.
PASSWORD = password	Specifies the password for the save set.
MEDIANAME =media_name	Specifies the name of the medium.
MEDIAPASSWORD = password	Specifies the password that was assigned to the media set.
MOVE logical_file_name TO OS_file_name	Changes the location of the restored file for example,MOVE Northwind TO D:\data\Northwind.mdf' You can use this option if you are restoring to a new disk because the old disk is unusable.
NORECOVERY \|RECOVERY \| STANDBY = undo_file	NORECOVERY specifies that transactions will not be rolled back or rolled forward after the restore. Using this option is necessary if you will be restoring other backups (differential or transaction log). RECOVERY, the default option, specifies that the recovery operation will be performed and any uncommitted changes will be rolled back.STANDBY specifies that an undo file will be created in case the recovery needs to be undone.
KEEP_REPLICATION	Specifies that replication settings be preserved when the database that is being restored is a publisher.
NOUNLOAD \| UNLOAD	NOUNLOAD specifies that the medium not be unloaded after the restore is completed (for example, that the backup tape not be rewound and ejected). UNLOAD, the default, specifies that the medium be unloaded after the restore is completed.
REPLACE	Indicates that SQL Server will restore data files even if those files currently exist. The existing data files will be deleted and written over. If you do not specify REPLACE, SQL Server checks to see whether the database you specified already exists. If it does exist, the restore operation fails. This safety feature helps you avoid unintentionally restoring over a live database.
RESTART	Specifies that SQL Server should restart a restore operation that was interrupted.
STATS [ = percentage ]	Displays a message after the specified percentage of the restore operation is completed. This option is useful if you want to watch the progress of operations.
PARTIAL	Specifies that a partial restore be done.
STOPAT = date_time (log restore only)	Specifies that the database should be recovered to the state it was in on the date specified by date_time.
STOPATMARK = mark	Specifies that the restore operation proceed until the specified mark is reached.
STOPBEFOREMARK = mark	Specifies that the restore operation proceed until just before the specified mark is reached.

NOTE
Named transactions are a new feature in SQL Server 2000. These named transactions, which are created with the command BEGIN TRANSACTION ... WITH MARK mark_name option, allow you to use the STOPATMARK and STOPBEFOREMARK features of the RESTORE command.

REAL WORLD Using RESTORE

This sidebar shows a few examples of using the RESTORE T-SQL command.

This statement restores the data files for the Example database:
 RESTORE DATABASE Example FROM Backup_Dev_1, Backup_Dev_2 WITH NORECOVERY, STATS = 5 GO 
This statement restores the transaction log for the Example database:
 RESTORE LOG Example FROM Backup_Dev_3, Backup_Dev_4 WITH NORECOVERY, STATS = 5, UNLOAD GO 
The output will display the percentage of the operation that is completed, as well as the outcome of the restore. You'll be informed of how many pages were restored up, how long the restore took, and how fast the restore was performed (in megabytes per second).

You can now recover this database by using the following command:
 RESTORE LOG Example WITH RECOVERY GO 
You will again see statistics regarding the restore operation.