Transact -SQL Restore Examples
Listings 16.11 through 16.14 show some samples of restoring the database using T-SQL scripts.
Listing 16.11 Sample Script for a Full Database Recovery
--Restore northwind from the first backup on device nwbackup -- Recover the database USE MASTER RESTORE DATABASE Northwind FROM nwbackup WITH FILE = 1, RECOVERY
Listing 16.12 Sample Script for a Full and Log Recovery
-- Restore northwind from the first backup on device nwbackup -- Restore two logs from device nwlogback -- Recover the database USE MASTER RESTORE DATABASE northwind FROM nwbackup WITH FILE = 1, NORECOVERY GO RESTORE LOG northwind FROM nwlogback WITH FILE = 1, NORECOVERY GO RESTORE LOG northwind FROM nwlogback WITH FILE = 2, RECOVERY
Listing 16.13 Sample Script for a Point in Time Recovery
-- Restore northwind from the first backup on device nwbackup -- Restore two logs from device nwlogback -- Stop at Jan 15 2001 09:00 AM -- Recover the database USE MASTER RESTORE DATABASE northwind FROM nwbackup WITH FILE = 1, NORECOVERY GO RESTORE LOG northwind FROM nwlogback WITH FILE = 1, NORECOVERY GO RESTORE LOG northwind FROM nwlogback WITH FILE = 2, RECOVERY, STOPAT = 'January 15, 2001 09:00 AM'
Listing 16.14 Sample Script for a File Restore
-- Restore one file from the northwind database -- Restore the log from device nwlogback to make the file consistant -- Recover the database USE MASTER RESTORE DATABASE northwind FILE = nwdbfile3 FROM nwbackup WITH NORECOVERY GO RESTORE LOG northwind FROM nwlogback WITH FILE = 1, RECOVERY
Performing Restore Operations with Enterprise Manager
When performing restore operations, the last thing you want to do is make a mistake. The consequences of an error at this point could range from a delay in restoring service to your users, to rendering a database unrecoverable. Enterprise Manager helps you avoid costly errors by presenting restore options in an easy-to-use graphic interface.
As with backing up the database, the Restore Database dialog box can be accessed from the Task Pad, the Tools menu, or by right-clicking the database, selecting All Tasks from the pop-up menu, and then selecting Restore Database. The Restore Database dialog box is shown in Figure 16.7.
Figure 16.7. The Restore Database dialog box.
The Restore as Database window allows you to select an existing database or enter a new database name. Entering a new name automatically creates a new database and restores a copy of the database contained in the backup to it. The default selection is a full database restore, but you can choose a filegroup or file restore, or specify a device to restore from. The parameters section lets you choose which database backups to display and, if there are multiple backups of that database, allows you to choose the First Backup to Restore from a drop-down list. By default the most recent backup is selected. If there are log backups for the database the Point in Time Restore check box is available. From here, you can specify which point in time the database will be restored to. The selection window is tied to the First Backup to Restore drop-down box. As mentioned before, the most recent backup is selected, as this is generally the one you will want to restore. SQL Server takes this one step further. Under the Restore heading, it automatically selects the best restore scenario based on the backups available. You, of course, can override this, but generally won't want to. For example, let's say you have done a full backup, and then three log backups, followed by a differential backup, and then two more log backups. The best full database restore plan for this is to restore the full backup, the differential, and then the two log backups taken after the differential. This is exactly what Enterprise Manager will have preselected for you. Click OK and the restore will be performed as selected. Isn't modern technology wonderful?
The Options tab, of the Restore Database dialog box, pictured in Figure 16.8, further extends Enterprise Manager's restore options. The top three check boxes are self-explanatory. The Restore Database Files as window lets you restore files to a new location by typing the path and filename in Restore As column. This is equivalent to the T-SQL MOVE option. The three options under Recovery Completion State map to RECOVERY , NORECOVERY , and STANDBY , respectively.
Figure 16.8. The Options tab of the Restore Database dialog box.
Performing your restore operations with Enterprise Manager will help speed your recovery and reduce the chance of error, without diminishing your ability to override any part of the operation. I highly recommend that you familiarize yourself with the interface, and test its capabilities in practice drills. This will allow you to perform restore operations on your production databases quickly and with confidence.