Backup Methods

3 4

There are several methods of backing up a database: full, differential, transaction log, filegroup, and data file. Each has its own modes of operation and features. A full backup consists of backing up all of the data in the database, the filegroup, or the data file. A differential backup involves backing up only the data that has changed since the last backup. A transaction log backup is used to back up and truncate the transaction log. (As we've seen, backing up the transaction log is a crucial DBA task because transaction log data is used in conjunction with database backups.) Filegroup and data file backups are used to back up a particular filegroup or data file in the database.

All SQL Server backups are performed for a specific database. To completely back up your system, you should back up all databases in the system and their transaction logs. Don't forget to back up the master database as well. And remember, without good backups, you might not be able to restore your data in the event of a failure.

Full Backups

As mentioned, a full backup involves backing up an entire database. All of the filegroups and data files that are part of this database are backed up. If you have multiple databases, you should back up all of them. A full backup is probably the most common technique for backing up small- to medium-size databases. Depending on how large the databases are, this process can be quite time consuming, so if time is an issue, you might consider performing differential backups or filegroup backups, as described next. Once you start a backup, you cannot pause it—the backup will continue until the entire database is backed up. Performing a full database backup is described in the section "Performing a Backup" later in this chapter.

Differential Backups

Differential backups enable you to back up only the information that has changed since the last backup. Because they back up only part of the data, differential backups are faster and take less space than full backups. However, differential backups are more difficult and time consuming to restore than full backups. Restoring a differential backup requires the restoration of the last full backup and all differential backups that have occurred since the last full backup.

Transaction Log Backups

Transaction log backups enable you to back up the transaction log. These backups are important for database recovery, as explained earlier in this chapter.

Filegroup Backups

A filegroup backup involves backing up all of the data files associated with a single file in a database. This process is similar to a full backup in that it backs up all of the data in the data files regardless of when the data was last backed up. You can use a filegroup backup to back up a filegroup that is associated with a particular department or workgroup, depending on how your system is configured. If your system is divided into individual departments accessing their own filegroups, you can back up each department's data according to a different schedule.

Data File Backups

Data file backups enable you to back up a single file in a filegroup. This backup type works in conjunction with SQL Server 2000's capability to restore a single data file separately. A data file backup can be useful if you don't have enough time each night to back up an entire filegroup, because it allows you to rotate data files for backup. In the event of a disk failure in which a data file is lost or corrupted, you can restore only that data file. The greater the amount of time that has passed since that data file was backed up, however, the longer the recovery process will take.