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

■ the intervals between log backups should not exceed the time specified by the recovery point Objective
requirement. you should also factor in log backup duration when designing a backup strategy.

Important

While it is relatively easy to define a backup strategy based on the RPO, it is much trickier with RTO, which specifies
maximum duration of the recovery process and, therefore, system downtime. That time depends on a few factors, such
as network throughput, which dictates how much time is required to transmit backup files over the network as well as on
the size and number of backup files. Moreover, it changes over time as the database and load grows.

■ Important you should regularly test the database recovery process, making sure that it still meets rtO requirements.

Figure 30-4 shows a recovery scenario for a database that has multiple differentials and log backups. As a first step
during recovery, you should make a tail-log backup, which backs up the portion of the transaction log that had not
been backed up since the last log backup. After that, you should restore the last full backup, most recent differential
backup, and all log backups taken afterwards, including the tail-log backup.

Figure 30-4. Recovery sequence

Let's assume that the example shown in Figure 30-4 represents a database with the primary filegroup residing
on disk M:, secondary filegroup on disk N:, and transaction log on disk L:. All backup files are stored on disk V:.
Listing 30-12 shows the script that recovers the database after a disaster when disk N: becomes corrupted and
unavailable. The data files from the secondary filegroup are moved to disk M.

Listing 30-12. Restoring the database after a disaster

-- Backing up Tail-Log.
-- Database will be left in RESTORING stage
BACKUP LOG [RecoveryDemo]
TO DISK = N'V:\RecoveryDemo-tail-log.trn'
WITH NO_TRUNCATE, NOFORMAT, INIT,
NAME = N'RecoveryDemo-Tail-log backup',
NORECOVERY, STATS = 5;