Designing a Backup Strategy - Pro SQL Server Internals - page 591

Listing 30-4. Restoring the database

use [master]
go

-- Initial FULL backup
RESTORE DATABASE [OrderEntryDbDev]
FROM DISK = N'C:\Backups\OrderEntry.bak'
WITH FILE = 1,
MOVE N'OrderEntryDB' TO N'c:\backups\OrderEntryDB.mdf',
MOVE N'OrderEntryDB_log' TO N'c:\backups\OrderEntryDB_log.ldf',
NORECOVERY, NOUNLOAD, STATS = 5;

-- Differential backup
RESTORE DATABASE [OrderEntryDbDev]
FROM DISK = N'C:\Backups\OrderEntry.bak'
WITH FILE = 2,
NORECOVERY, NOUNLOAD, STATS = 5;

-- Transaction Log backup
RESTORE LOG [OrderEntryDbDev]
FROM DISK = N'C:\Backups\OrderEntry.trn'
WITH NOUNLOAD, NORECOVERY, STATS = 10;

RESTORE DATABASE [OrderEntryDbDev] WITH RECOVERY;

When the backup file stores multiple backups, you should specify a file number by using the WITH FILE option.
As I noted earlier, be careful with this approach and make sure that your backup routine does not accidentally
override existing backups in the file.
Each RESTORE operation should have a database recovery option specified. When a backup is restored with the
RECOVERY option, SQL Server recovers the database by performing both the redo and undo recovery stages, and it
makes the database available to the users. No further backups can be restored. Alternatively, the NORECOVERY option
performs only the redo stage of database recovery, and it leaves the database in the RESTORING state. It allows you to
restore further backups from the log chain.

■ the ui interface in Management studio uses the RECOVERY option by default. always pay attention to this
setting when using the Database restore ui in Management studio.

Important

Accidental use of the RECOVERY option would require you to repeat the restore process from the beginning, which
could be very time consuming in the case of large databases. It is safer to restore all backups with the T-SQL RESTORE
command using the NORECOVERY option all of the time. Finally, you can recover the database and bring it online with
the RESTORE DATABASE WITH RECOVERY command, as was shown in Listing 30-4.
We will discuss how to restore the database after a disaster later in this chapter. Now let's cover a couple useful
options that you can use during a restore.