Restoring your Data 101

November 29, 2005

Now that your data is backed up, you will need to learn how to restore your data when it is applicable. You might want to restore your data because of a hardware failure or restore data to a backup or standby server. The reasons are endless but the most important thing is to be prepared.

Remember that if you are recovering a database using the Simple Recovery Model, you can only restore to the last full backup. If you are using the Full or Bulk Recovery Model, you must restore the last full backup, then the last full differential backup, and all transaction logs that apply.

Full Database Restore

Regardless of the model that you are using, the first step in restoring your database is to restore the database to the last full backup. To restore the database in Enterprise Manager, right-click on the database and choose All Tasks | Restore Database (Figure A). The Restore Database allows you to view all of the most recent backups in chronological order. You also have the option of specifying the database you would like to restore or choosing a new database.

Click for larger image

Figure A.

On the Options tab (Figure B.), you have the ability to eject a tape after the backup completes or get prompted before restoring each backup. In addition, you can choose the Restore Database file, which is similar to the Move command in T-SQL

Click for larger image

Figure B.

You can also leave a restored database in the following state:

  • Leave Database Operational. No Additional Transaction Logs Can Be Restored.
  • Leave Database Nonoperational But Able to Restore Additional Transaction Logs.
  • Leave Database Read Only and Able to Restore Additional Transaction Logs.
  • Undo File

After choosing all of your Options, you can click Ok to restore your database.

Restores in T-SQL

You can also restore a database using T-SQL; it has options that are not available when restoring data in Enterprise Manager. The syntax is as follows:

RESTORE DATABASE { database_name | @database_name_var } 
[ FROM < backup_device > [ ,...n ] ] 
    [ [ , ] FILE = { file_number | @file_number } ] 
    [ [ , ] PASSWORD = { password | @password_variable } ] 
    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] 
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ] 
    [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] 
            [ ,...n ] 
    [ [ , ] KEEP_REPLICATION ] 
    [ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ] 
    [ [ , ] { NOREWIND | REWIND } ] 
    [ [ , ] { NOUNLOAD | UNLOAD } ] 
    [ [ , ] REPLACE ] 
    [ [ , ] RESTART ] 
    [ [ , ] STATS [ = percentage ] ]

For a definition of each option, please see the description in SQL Books Online. In the following example, I will restore the Pubs database from a backup device (Figure C).

Figure C.

Differential Database Restore

Prior to performing a differential restore, remember to first restore the last full backup of your database. To perform a differential restore in Enterprise Manager, select the full backup and differential backup and choose Ok.

Figure D.

To perform differential restore using Transact SQL, use the restore command with the appropriate syntax (Figure E).

Figure E.

Transaction Log Database Restore

Prior to performing a transaction log restore, you must first restore your full backup, last differential backup and all transaction logs in order, prior to your failure. If you are using Enterprise Manager, select all the backups and a point-in-time restore option if applicable (Figure F).

Figure F.

In T-SQL, you want to use the Restore Log command to restore a transaction log. To perform a transaction log restore using Transact SQL use the following syntax (Figure G).

Figure G.


After reading this article, you should be able to restore any database depending on your disaster recovery plan. Restoring data is one of the most fundamental and important aspects of being a Database Administrator.

» See All Articles by Columnist Steven S. Warren