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

-- Restoring FULL backup moving files
-- from SECONDARY FG to M: drive
RESTORE DATABASE [RecoveryDemo]
FROM DISK = N'V:\RecoveryDemo-F1.bak'
WITH FILE = 1,
MOVE N'RecoveryDemo_Secondary'
TO N'M:\RecoveryDemo_Secondary.ndf',
NORECOVERY, STATS = 5;

-- Restoring DIFF backup
RESTORE DATABASE [RecoveryDemo]
FROM DISK = N'V:\RecoveryDemo-D2.bak'
WITH FILE = 1, NORECOVERY, STATS = 5;

-- Restoring L5 Log backup
RESTORE LOG [RecoveryDemo]
FROM DISK = N'V:\RecoveryDemo-L5.trn'
WITH FILE = 1, NORECOVERY, STATS = 5;

-- Restoring L6 Log backup
RESTORE LOG [RecoveryDemo]
FROM DISK = N'V:\RecoveryDemo-L6.trn'
WITH FILE = 1, NORECOVERY, STATS = 5;

-- Restoring tail-log backup
RESTORE LOG [RecoveryDemo]
FROM DISK = N'V:\RecoveryDemo-tail-log.trn'
WITH FILE = 1, NORECOVERY, STATS = 5;

-- Recovering database
RESTORE DATABASE [RecoveryDemo] WITH RECOVERY;

■ in our example, sQL server must redo all data modifications that occurred in between the time of the differential
backup D2 and the time of failure.

Note

You can take multiple restore paths while recovering the database. In addition to the method shown above, you
can also use differential backup D1, applying log backups L3-L7 and the tail-log backup. As another option, you can
use only log backups after you have restored a full backup without using any differential backups at all. However,
the time required for the restore process greatly depends on the amount of transaction log records that need to be
replayed. Differential backups allow you to reduce the amount of time involved and speed up the restore process.
You should design a backup strategy and find the right combination of full, differential, and log backups that
allow you to restore the database within the time defined by the RTO requirements. The key point here is to define the
schedule of full and differential backups because the frequency of log backups depends on RPO and possible data loss.

■ remember to enable instant File initialization, which prevents zeroing-out data files during the database creation
stage of restore.

Tip