Restoring your Data 101

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.

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

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 ] ]
[ 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 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.

Conclusion

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

Steven Warren
Steven Warren
Steven S. Warren is a popular author residing in Winter Haven, Florida with his wife Danna and 2 children: Catie-Charlotte and Dain. As a columnist on such well-known IT web sites as Techrepublic.com, CNET, and ZDNET, Steven has published numerous articles. Additionally, Steven holds the following certifications: MCDBA, MCSE, MCSA, CCA, CIW-SA, CIW-MA, Network+, and I-Net+. As a Senior Technical Consultant for The Ultimate Software Group, Steven has become an expert at administering Microsoft networks including Microsoft SQL Server. He is also a computer hardware and troubleshooting expert, and is constantly seeking out new technologies and certifications. Additionally, Microsoft recently awarded him the Most Valuable Professional (MVP) award for his outstanding achievements. Steven resides in Winter Haven, Fl.

Latest Articles