Backup Overview (SQL Server)

Every recovery model lets you back up a whole or partial SQL Server database or individual files or filegroups of the database. Table-level backups cannot be created.

Data Backups

The scope of a backup of data (a data backup) can be a whole database, a partial database, or a set of files or filegroups. For each of these, SQL Server supports full and differential backups:

• Full backup
  A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

• Differential backup
  A differential backup is based on the latest full backup of the data. This is known as the base of the differential, or the differential base. A differential base is a full backup of read/write data. A differential backup contains only the data that has changed since the differential base. Typically, differential backups that are taken fairly soon after the base backup are smaller and faster to create than the base of a full backup. Therefore, using differential backups can speed up the process of making frequent backups to decrease the risk of data loss. Usually, a differential base is used by several successive differential backups. At restore time, the full backup is restored first, followed by the most recent differential backup.
  Over time, as a database is updated, the amount of data that is included in differential backups increases. This makes the backup slower to create and to restore. Eventually, another full backup will have to be created to provide a new differential base for another series of differential backups.

  Note

  Typically, a differential backup covers the same data files as those files that are covered in a single differential base. Under the simple recovery model, a differential backup can have only one differential base. Trying to use multiple bases causes an error and the backup operation fails. Under the full recovery model, differential file backups can use multiple bases, but this can be difficult to manage. For more information, see Working with Multibase Differential Backups.

Each data backup includes part of the transaction log so that the backup can be recovered to the end of that backup.

After the first data backup, under the full recovery model or bulk-logged recovery model, regular transaction log backups (or log backups) are required. Each log backup covers the part of the transaction log that was active when the backup was created, and the log backup includes all log records that were not backed up in a previous log backup.

Database Backups

Database backups are easy to use and are recommended whenever database size allows. SQL Server supports the following types of database backups.

Partial Backups

Partial and differential partial backups are new in SQL Server 2005. These backups are designed to provide more flexibility for backing up databases that contain some read-only filegroups under the simple recovery model. However, these backups are supported by all recovery models.

SQL Server 2005 supports the following types of file backups.

File Backups

The files in a database can be backed up and restored individually. Using file backups can increase the speed of recovery by letting you restore only damaged files, without restoring the rest of the database. For example, if a database consists of several files that are located on different disks and one disk fails, only the file on the failed disk has to be restored. However, planning and restoring file backups can be complex; therefore, file backups should be used only where they clearly add value to your restore plan.

SQL Server supports the following types of file backups.

Backup type

Description

File backup

A full backup of all the data in one or more files, or filegroups.

Important:

Under the simple recovery model, file backups are basically restricted to read-only secondary filegroups. You can create a file backup of a read/write filegroup, but before you can restore the read/write file backup, you must set the filegroup to read-only and take a differential read-only file backup.

Differential file backups

A backup of one or more files that contain data extents that were changed since the most recent full backup of each file.

Note:

Under the simple recovery model, this assumes that the data has been changed to read-only since the full backup.

Transaction Log Backups (Full and Bulk-Logged Recovery Models Only)

Under the full recovery model or bulk-logged recovery model, regular transaction log backups (or log backups) are required. Each log backup covers the part of the transaction log that was active when the backup was created, and it includes all log records that were not backed up in a previous log backup. An uninterrupted sequence of log backups contains the complete log chain of the database, which is said to be unbroken. Under the full recovery model, and sometimes under the bulk-logged recovery model, an unbroken log chain lets you to restore the database to any point in time.

Before you can create the first log backup, you must create a full backup, such as a database backup. Thereafter, backing up the transaction log regularly is necessary, not only to minimize work-loss exposure but also to enable truncation of the transaction log. For more information, see Working with Transaction Log Backups.

Copy-Only Backups

Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. For this purpose, SQL Server 2005 introduces copy-only backups. These backups are independent of the regular sequence of SQL Server backups. For more information, see Copy-Only Backups.

Backup Devices

SQL Server backups are created on backup devices, such as disk files or tape media. You can append new backups to any existing backups on a device, or overwrite any existing backups. For more information, see Working with Backup Media in SQL Server.

Scheduling Backups

Performing a backup operation has minimal effect on transactions that are running; therefore, backup operations can be run during regular operations. During a backup operation, SQL Server copies the data directly from the database files to the backup devices. The data is not changed, and transactions that are running during the backup are never delayed. Therefore, you can perform a SQL Server backup with minimal effect on production workloads. For information about concurrency restrictions during backup, see "Restrictions on Backup Operations in SQL Server," later in this topic.

You can schedule backups to run automatically at set intervals. For information about how to schedule backup jobs for database backups and log backups, see Maintenance Plan Wizard.

Restrictions on Backup Operations in SQL Server

In SQL Server 2005, backup can occur while the database is online and being used. However, the following restrictions exist.

Offline Data Cannot Be Backed Up

Any backup operation that implicitly or explicitly references data that is offline fails. Some typical examples include the following:

• You request a full database backup, but one filegroup of the database is offline. Because all filegroups are implicitly included in a full database backup, this operation fails.
  To back up this database, you can use a file backup and specify only the filegroups that are online.
• You request a partial backup, but a read/write filegroup is offline. Because all read/write filegroups are required for a partial backup, the operation fails.
• You request a file backup of specific files, but one of the files is not online. The operation fails. To back up the online files, you can omit the offline file from the file list and repeat the operation.

Typically, a log backup succeeds even if one or more data files are unavailable. However, if any file contains bulk-logged changes made under the bulk-logged recovery model, all the files must be online for the backup to succeed.

Concurrency Restrictions During Backup

SQL Server uses an online backup process to allow for a database backup while the database is still being used. During a backup, most operations are possible; for example, INSERT, UPDATE, or DELETE statements are allowed during a backup operation. However, if you try to start a backup operation while a database file is being created or deleted, the backup operation waits until the create or delete operation is finished or the backup times out.

Operations that cannot run during a database backup or transaction log backup include the following:

• File-management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options.
• Shrink database or shrink file operations. This includes auto-shrink operations.
• If you try to create or delete a database file while a backup operation is in progress, the create or delete operation fails.

If a backup operation overlaps with a file-management operation or shrink operation, a conflict occurs. Regardless of which of the conflicting operation began first, the second operation waits for the lock set by the first operation to time out. (The time-out period is controlled by a session time-out setting.) If the lock is released during the time-out period, the second operation continues. If the lock times out, the second operation fails.

See Also

Concepts

Restore Restrictions Under the Simple Recovery Model
Understanding How Restore and Recovery of Backups Work in SQL Server
Backup Under the Simple Recovery Model
Backup Under the Full Recovery Model
Backup Under the Bulk-Logged Recovery Model
Collation in BACKUP and RESTORE Operations

Help and Information

Getting SQL Server 2005 Assistance

Change History