In my last post about backup and recovery, I discussed the various methods for backing up a SQL Server database. In this post, I'll discuss the other half of the equation: restoring the database. After all, backups are only as good as the last restoration! I will cover the following topics:

• How to perform a basic restore operation
• How to restore to a point in time

How to Perform a Basic Restore Operation

Before you begin a restore operation, ensure that you have at least one good full backup of the database. This establishes the backup chain. For a detailed overview of the restore and recovery process, see the "Restore and Recovery Overview" topic in SQL Server Books Online on MSDN.

To restore a database, follow these steps:

1. In SQL Server Management Studio, right-click the Databases node, and then click Restore Database.

   

2. In the Source for restore area of the Restore Database dialog box, select which database you want to restore. In this example, I am restoring the AdventureWorks database. After you select a database to restore, the name of the database is automatically populated in the To database box in the Destination for restore area. For this example, I want to restore the database to its original location with its original name.

   

3. In the Select the backup sets to restore area, click to select the check boxes for the backups that you want to restore.
   The Select the backup sets to restore area displays all the various backups that exist for this database. The backups that you see are part of the backup chain that I discussed in the previous post. This backup chain contains the last full backup and any differential and transaction log backups that were taken after it.

   

   
   Notes:
   • By default, SQL Server assumes that you want to restore the database to the point of the latest backup. When you select a database to restore, only the backups that are needed to perform the restore operation are selected.
   • Because a full backup starts and ends a backup chain, you'll only see one full backup in the list at a time.
4. Under Select a page, click Options.

   

5. In the Recovery state area, leave the following default option selected:
   Leave the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)
   Note: On the Options page, you can select several important restore options. I'll discuss the other options in more detail later on.
6. Click OK to start the restore operation. After the database is restored, a confirmation lets you know that the database was restored successfully.

    

How to Restore to a Point in Time

As I mentioned earlier, by default, SQL Server Management Studio selects the most recent backup as the point of restoration. But, there are times when you want to restore to a specific point in time. The only way to perform point-in-time restorations is for the database to be in the Full recovery mode.

To restore to a point in time, follow these steps:

1. First, you must perform a tail log backup:
   1. In SQL Server Management Studio, right-click the database, point to Tasks, and then click Back Up.
   2. Change the Backup type to Transaction Log.
   3. Under Select a page, click Options.
   4. In the Transaction log area, click Back up the tail of the log, and leave the database in the restoring state.

      

   5. Click OK to start the backup. After the backup is complete, click OK in the confirmation dialog box.
2. The database is now in a Restoring state. Repeat steps 1 and 2 from the previous section.

   

3. This time, in the Destination for restore area, Most recent possible is selected in the To a point in time box. To change this setting, click the ellipsis (…) button. The Point in time restore dialog box opens.

   

4. In the Restore to area, click A specific date and time, enter the date and time that you want to restore your database to, and then click OK.
5. In the Select the backup sets to restore area, all the backup files that will be used to fulfill your restore request will be selected. Click OK to start the restore operation. After the database is restored, click OK in the confirmation dialog box.

Summary

Knowing how to restore your database is just as, if not more, important than backing up your databases. An advantage of restoring databases on a regular basis is that it allows you to verify that your backups are valid and can be restored from. Another advantage is that it gives you an idea of how long it takes to restore a database during a disaster recovery scenario.

Jorge Segarra is a database administrator, author, presenter, and blogger who lives and works in Florida. You can find his technical blog at http://sqlchicken.com, where he also runs the community blogging project known as SQL University.