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