10.3. Backup options

SQL Server 2008 includes a rich array of options that can be employed as parts of a customized backup and recovery strategy. In this section, we'll consider three such options: checksums, backup mirroring, and transaction log marks. But before we cover these options, let's have a look at an important part of any backup strategy: the backup location and retention policy.

10.3.1. Backup location and retention policy

A key component of a well-designed backup strategy is the location of the backups: disk or tape (or both). Let's consider each of these in turn before looking at a commonly used backup retention policy.

Tape

Historically, organizations have chosen tape media as a backup destination in order to reduce the cost of online storage while retaining backups for long periods of time. However, a tape-only approach to backups presents a number of challenges:

• Tape systems typically have a higher failure rate when compared to disk.

• Typically, tapes are rotated offsite after a predefined period, sometimes as soon as the morning after the backup. Should the backup be required for restore, there may be a time delay involved in retrieving the tape for restore.

• Depending on the tape system, it may be difficult/cumbersome to restore a tape backup to a different server for restoration verification, or to use it as a source for DBCC checks or other purposes.

In addressing these concerns, disk backups are frequently used, although they too have some challenges to overcome.

Disk

Due to some of the limitations with the tape-only approach, backup verification, whereby backups are restored on a regular basis to ensure their validity, are often skipped. As a result, problems are often discovered for the first time when a real restore is required.

In contrast to tape, disk-based backups offer the following advantages:

• When required for a restore, they are immediately available.

• Disk media is typically more reliable than tape, particularly when RAID protected.

• Disk-based backups can be easily copied to other servers when required, making the verification process much simpler compared with a typical tape-based system.

Despite its advantages, a disk-based backup approach has some drawbacks. The main one is the extra disk space (and associated cost) required for the backup files. Further, the cost advantage of tape is fully realized when considering the need to store a history of backups—for example, daily backups for the last 30 days, monthly backups for the past 12 months, and yearly backups for the last 7 years. Storing all of these backups on disk is usually more expensive compared to a tape-based system, not to mention the risk of losing all of the disk backups in an environmental disaster.

With the introduction of third-party backup compression tools and the inclusion of backup compression as a standard feature of SQL Server 2008 (Enterprise edition), the cost of disk storage for backups is significantly reduced, but the overall cost is still typically higher than a tape-based system.

In addressing the negative aspects of both tape and disk, a common approach is to combine both methods in what's known as a disk then tape approach.

Disk then tape

As shown in figure 10.6, the ideal backup solution is to combine both disk and tape backups in the following manner:

The advantages of such a system are numerous:

• Backups are stored in two locations (disk and tape), thus providing an additional safety net against media failure.

  Figure 10.6. The disk then tape backup methodology provides fast restore, dual backup protection, and long-term archive at a moderate cost.
   

  

• The most common restore scenario, typically that of the previous night's backup, is available on disk for fast restore without requiring tapes to be requested from offsite.

• A full history of backups is available for restore from tape.

• The cost of the overall system is reduced, while still providing all of the advantages of disk-based backups for the most common restore scenarios.

A variation on this approach is using SAN-based backup solutions. In chapter 3 we covered the benefits that SANs provide in disaster recovery scenarios. Most of the enterprise-class SANs available today provide several methods of snapping, or cloning, LUNs in order to provide near instant backup/restore solutions. Once snapped, the cloned LUN can be archived to tape, thereby providing long-term storage like the disk then tape approach. If using these backup types, take care to ensure the backup method used is SQL Server compatible and enables transaction log roll forwards for point-in-time recovery.

Regardless of the backup destination, an important consideration is how long to retain the backups before deleting or overwriting them.

Backup retention

Assuming the disk then tape backup method is used, the retention period for each location needs to be considered separately. For the disk backups, the retention period is dependent on the backup model. For example, if a weekly full, nightly differential system is in place, then the weekly backup would need to be retained on disk for the whole week for use with the previous night's differential backup. If disk space allows, then additional backups can be retained on disk as appropriate.

In considering the tape rotation policy (how long to keep a tape before overwriting it), the classic rotation policy typically used is the grandfather-father-son (GFS) system, whereby 22 tapes are used per year.

The GFS tape rotation policy, as shown in table 10.1, uses 6 sons, 3 fathers, and 13 grandfathers (52 weeks per year divided by 4-week periods) for a total of 22 tapes per year. Optionally, one of the grandfather tapes can be retained as a yearly backup tape for a period of years.

Regardless of the disk location and retention period, ensuring backups are valid is an important consideration. Short of actually restoring each backup, one of the options available for detecting media failure is to use backup checksums.

10.3.2. Backup checksums

One of the features introduced in SQL Server 2005 was the ability for backups to verify the validity of pages as part of the backup process, and for the backup itself to include a checksum.

When using the optional^[] WITH CHECKSUM option of the BACKUP command as shown here, the backup process verifies the checksum of each page as the backup is performed, assuming the PAGE_VERIFY database option, covered in more detail in chapter 12, is set to CHECKSUM (which is the default).

^[] Enabled by default on compressed backups.

-- Verify Page Validity during backup with CHECKSUM
BACKUP DATABASE [AdventureWorks2008]
TO DISK = N'G:SQL BackupAdventureWorks.bak'
WITH CHECKSUM

The PAGE_VERIFY option calculates and stores a checksum value for each database page written to disk. When read from disk, the checksum is verified against the page read and used to alert the presence of suspect pages.

The WITH CHECKSUM option of the BACKUP command calculates and verifies the checksum value of each page as it's read by the backup process. If a checksum error occurs, the backup fails, unless the CONTINUE_AFTER_ERROR option is used. In that case, the backup is flagged as containing errors and the suspect page(s) are marked in the suspect_pages table in the msdb database.

In addition to verifying the checksum of each page, the WITH CHECKSUM option calculates a checksum for the entire backup process. When a database is restored from a backup created with the checksum option, the restore process verifies the checksums as part of the restore process, unless the NO_CHECKSUM option is used. If a checksum error is found as part of the restore, the restore will fail, unless the CONTINUE_AFTER_ ERROR option is used.

Although backup checksums provide additional confidence in the validity of the backups, they do introduce additional CPU overhead during the backup process. Before enabling this option, ensure the overhead is measured on a test system, particularly in cases where the additional overhead may extend the backup window beyond the desired time frame. That being said, the additional confidence this option provides is well worth the typically small CPU overhead.

Another technique commonly used for backup assurance is the mirroring option.

10.3.3. Backup mirroring

There is no such thing as too many backups. One of the optional backup clauses is MIRROR TO. Here's an example:

-- Mirror the backup to a separate backup server using a UNC path
BACKUP DATABASE [AdventureWorks2008]
TO DISK = N'G:SQL BackupAdventureWorks-20080701.bak'
MIRROR TO DISK = '\BACKUP-SERVERSQL-BackupsAdventureWorks-20080701.bak'
WITH FORMAT

The MIRROR TO clause allows a backup to be streamed to multiple destinations. The typical use of this option is for making a duplicate backup on a file server using a Universal Naming Convention (UNC) path to a file share (in the previous example, \BACKUP-SERVERSQL-Backups). This option provides multiple advantages:

• Additional backups for protection against media failure.

• Different retention periods for different locations; for example, the file server backups can be retained for a longer period on disk when compared to the backup file on the database server.

• The tape archive process can archive from the file share rather than the database server. Not only does this reduce the additional load the tape archive process places on the database server, it also avoids the need for tape drivers and associated software to be installed on the database server.

In concluding this section, let's take a look at the challenge of coordinating backups across multiple databases.

10.3.4. Transaction log marks

A common backup requirement is for coordinated backups across multiple databases. This is usually a requirement for the restore process rather than the backup—when a database is restored, all associated databases must be restored to exactly the same point.

Synchronized restores are enabled using transaction log marks. Before we take a look at using them in a restore scenario, let's see how they're used in recovering from an unintended action. Consider the following statement, which increases product prices by 2 percent:

-- Update all prices by 2%
BEGIN TRANSACTION updatePrices WITH MARK 'Updating Prices Now';
   UPDATE Products
   SET Price = Price * 1.02
COMMIT TRANSACTION updatePrices

Let's imagine we only intended to update some products, not all of them, as shown in the previous statement. Short of running additional commands to roll back the price increase (and other flow-on effects), we'd be looking at a database restore, but if we can't remember the time of the update, a transaction log recovery using the STOPAT option won't help.

One of the optional clauses we used in the update price transaction was WITH MARK, and we can use that in a restore command. After performing a restore of a full backup in NORECOVERY mode, we can then restore a transaction log backup made after the transaction to the point immediately before the mark, using the STOPBEFOREMARK option:

-- After restoring the full backup, roll forward the transaction log
-- Use the STOPBEFOREMARK option to stop before the marked transaction
RESTORE LOG [AdventureWorks2008]
FROM DISK = N'G:SQL BackupAdventureWorks-log.bak'
WITH RECOVERY, STOPBEFOREMARK = 'updatePrices'
GO

Now that's all well and good (and very handy), but how does that help us with coordinating backups and restores across multiple databases? Well, by encapsulating statements that update multiple databases within a single marked transaction, we can achieve the desired result (see listing 10.2).

Example 10.2. Marking multiple transaction logs for coordinated restores

-- Use a dummy transaction to mark multiple databases
-- If required, each database can be restored to the same point in time
BEGIN TRANSACTION backupMark WITH MARK
   UPDATE db1.dbo.dummytable set col1 = 1
   UPDATE db2.dbo.dummytable set col1 = 1
   -- other databases here ...
COMMIT TRANSACTION backupMark

By executing a simple update statement in multiple databases within one transaction, we're marking the transaction log of each database at the same time. Such an update statement could be executed immediately before transaction log backups are performed, thus enabling the backups to be restored to the same point in time using the STOPBEFOREMARK that we saw earlier. Bear in mind, however, that data entered in the databases after this transaction will be lost, and this is an important consideration in a coordinated restore scenario.

Using transaction marks to enable synchronized restores across multiple databases is one example of using backup/restore features beyond the basics. While a basic backup/restore approach may suffice for small databases, it's insufficient for very large databases (VLDBs). In the previous chapter, we covered the use of filegroups as a mechanism for enabling enhanced administration options. We also explored a best practice whereby user objects are placed on secondary filegroups so that the only objects in the primary filegroup are system objects. Let's take a look at that process in more detail, and see how it can be used to minimize the user impact of a restoration process.