SQL Server Magazine

Restoring a SQL Server database after a disaster is one of the most important jobs of a systems administrator (sa). But recovery often receives less attention than its companion operation, backup. Most competent administrators know they need to regularly back up the company's mission-critical data. So, to make sure it gets finished—and because making backups is usually a straightforward operation—they assign the job of backing up to a novice member of the administration team. And there's nothing wrong with having novice SQL Server administrators perform backups as long as they use a consistent procedure.

Restore operations, however, are rarely left to novices. Still, because restoring a database isn't a daily operation, a SQL Server administrator might manage databases for years without having to perform an emergency restore. So on the day that you have to perform a restore after a disaster, the many subtleties of the restore process can take you by surprise. Unexpected glitches in the restore can mean that you're searching SQL Server Books Online (BOL) and the Microsoft Knowledge Base for solutions to problems while your entire company is waiting for you to make data available. In addition to being prepared for unexpected problems, you need to test your recovery plan. If you haven't already completely tested your recovery operations in a simulated disaster scenario, start planning such a simulation as soon as you finish reading this article.

In this article, I review various types of backup operations, including full, differential, and transaction log backups. Then, I discuss basic restore operations and describe what SQL Server does when it restores your data. In future SQL Server Magazine articles, I'll discuss details you need to know when you're moving a database to a new location that has different users. I'll also look at problems you might face when restoring an entire SQL Server system instead of just an individual user database.

Backup and Restore
Although backup is usually a straightforward operation, you need to understand what happens during different types of backups so you can plan your restore operations. When you perform a backup, you're copying data, the transaction log, or both to another, presumably safe, location. That location can be a local disk file (which you then copy to tape or other media) or tape. Although you can copy to a remote disk file, writing to a local file and using the OS file copy operations to move the file to another machine is usually more efficient. How fast and how completely you can restore backed-up data depends on the type of backup you've made and how well you've planned your restore operation. (For example, you need to plan ahead for how much space your restore will require, as I explain in the sidebar "Planning the Space for a Restore.") SQL Server 2000 supports three main types of backups: full, differential, and log. For tips about using these backup types, see Michael D. Reilly, "Top 11 Backup Tips," September 2001, InstantDoc ID 21701.

Full backup. A full database backup copies all the pages from a database to a backup device, which can be a local or network disk file, a local tape drive, or even a named pipe. SQL Server also copies the portion of the transaction log that was active while the backup was in process.

Differential backup. A differential backup copies only the extents that have changed since the last full backup. SQL Server 2000 can quickly tell which extents need to be backed up by examining a special page called the Differential Changed Map (DCM) in each file of the database. A file's DCM contains a bit for each extent in the file. Each time you make a full backup, all the bit values revert to 0. When any page in an extent is changed, the page's corresponding bit in the DCM page changes to 1. SQL Server copies the portion of the transaction log that was active during the backup. Typically, you make several differential backups between full backups, and each differential backup contains all the changes since the last full backup.

Log backup. A transaction log backup copies all the log records that SQL Server has written since the last log backup. Even if you've made full database backups, a log backup always contains all the records since the last log backup. Thus, you can restore from any full database backup as long as you have all the subsequent log backups. However, the exact behavior of the BACKUP LOG command depends on your database's recovery-model setting. If the database is using the full recovery model, the BACKUP LOG command copies the entire contents of the transaction log. In the bulk_logged recovery model, a transaction log backup copies the contents of the log and all the extents containing data pages that bulk operations have modified since the last log backup. If the database is using the simple recovery model, you can't perform a log backup because the log is truncated regularly, so no useful information is available. (For more information about recovery models, see Inside SQL Server, "Database Recovery Models," June 2000, InstantDoc ID 8551.) In a typical recovery scenario, an administrator would make a series of log backups between full database backups, with each log backup containing only the log records recorded since the last log backup.

SQL Server supports variations on these basic backup types, including file or filegroup backups, which are useful in environments that use very large databases (VLDBs). For information about file and filegroup backup and recovery, see the sidebar "Backing Up and Restoring Files and Filegroups." The more kinds of backups you make and the more frequently you make them, the more options you have for restoring a database quickly and completely. Restore operations, however, involve more work for SQL Server than backups do. When you perform a complete restore operation, SQL Server must make sure that the data in the database agrees with the transaction records in the transaction log. The process of verifying that the data and the log are in agreement is called database recovery.

   Prev. page   [1] 2 3 4     next page