Chapter 10. Backup and recovery

The importance of backups can't be overstated. During normal activity, it's easy to view backing up databases as an administrative chore that complicates the day and offers little benefit. However, when required in an emergency, the presence of valid backups could make all the difference to an organization's ongoing survival. As DBAs, we have a vital role to play in that process.

Successful backup strategies are those that are designed from a restore perspective—that is, they begin with service level agreements covering data loss and restoration times, and work backwards to derive the backup design. Second only to not performing backups, the biggest backup-related mistake a DBA can make is failing to verify backups. There are countless stories of backup tapes being recalled for recovery before finding out that the backups have been failing for the past few months (or years!). While the backup may appear to have succeeded, how can you be sure until you actually restore it?

In this chapter, we begin with an overview of the various types of backups that can be performed with SQL Server before we look at database recovery models. We then move on to cover online piecemeal restores, expanding on the previous chapter's coverage of filegroups. We then explore the benefits of database snapshots, and we conclude with a new backup feature introduced in SQL Server 2008: backup compression.