Step-By-Step: Learn how to restore databases in Microsoft SQL Server

by Steven "Warren MCSE, MCDBA" | Jan 21, 2002 8:00:00 AM

Takeaway: Learn how to restore your data to a backup or standby server or after a hardware failure.

Once you develop a good backup plan for Microsoft SQL Server 2000, you will need to learn how to restore your data when necessary. You might want to restore your data because of a hardware failure or you may want to 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, then you can restore to only the last full backup. If you are using either the Full or Bulk Recovery Model, you must restore the last full backup and then the last differential backup and all transaction logs that apply. I am going to walk you through the restore process.

Full database restore
Regardless of the recovery model you are using, the first step in restoring your database is to restore it to the last full backup. To restore the database in Enterprise Manager, right-click on the database and choose All Tasks | Restore Database to open the dialog box shown in Figure A.

Figure A

The Restore Database dialog box allows you to view all the most recent backups in chronological order. You can also specify the database you would like to restore or choose a new database. On the Options tab, shown in Figure B, you can choose to eject a tape after the backup completes or be prompted before restoring each backup. You can also choose the Force Restore Over Existing Database option, which is similar to the Move command in T-SQL.

Figure B

You can leave a restored database in one of the following states:

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 among these options, just click OK to restore the database.

Restores in T-SQL
You can also restore a database using T-SQL, which offers options that Enterprise Manager doesn't. The syntax for using T-SQL commands is as follows:
RESTORE DATABASE { database_name | @database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
    [ RESTRICTED_USER ]
    [ [ , ] 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 detailed definition of each option, see the description in SQL Server 2000 Books Online. In Figure C, I used T-SQL to perform a full restore of my Pubs database from a backup device.

Figure C

Differential database restore
If you're using either the Full or Bulk Recovery Model, you must perform a full restore and then restore the most recent differential backup and all transaction logs that apply. To perform a differential restore in Enterprise Manager, right-click on the database, choose All Tasks | Restore Database, and then select the full backup and the differential backup and choose OK (Figure D).

Figure D

To perform a differential restore using T-SQL, use the Restore command with the syntax shown in Figure E.

Figure E

Transaction-log restore
Prior to performing a transaction-log restore, you must restore your full backup and last differential backup. Then you can restore all transaction logs in the appropriate order. If you're using Enterprise Manager, right-click on the database, choose All Tasks | Restore Database, and select all the backups and the Point In Time Restore option (if applicable), as shown in Figure F.

Figure F

In T-SQL, use the Restore Log command to restore a transaction log with the syntax as shown in Figure G.

Figure G

End sum
Backing up and restoring data is one of the most fundamental and important aspects of being a database administrator. At this point, you should feel confident in your ability to restore a SQL Server 2000 database according to your disaster recovery plan. If you don't have a disaster recovery plan, I recommend you begin working on one immediately. After all, your company’s data is critical. If something goes awry and data is lost, the next loss could be your job.

Have a comment or a question?
We look forward to getting your input and hearing your experiences regarding this topic. Post a comment or a question about this article.

9 comment(s)