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.
Note
SQL Server backup and restore work across all supported operating systems, whether they are 64-bit or 32-bit systems. For information about the supported operating systems, see Hardware and Software Requirements for Installing SQL Server 2008 R2.
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.
Backup type |
Description |
|---|---|
A full backup of the whole database. Database backups represent the whole database at the time the backup finished. |
|
A backup of all files in the database. This backup contains only the data extents that were modified since the most recent database backup of each file. |
Partial Backups
Partial and differential partial backups were introduced 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 2008 supports the following types of file backups.
Backup type |
Description |
|---|---|
A backup of all the full data in the primary filegroup, every read/write filegroup, and any optionally specified read-only files or filegroups. A partial backup of a read-only database contains only the primary filegroup. |
|
A backup that contains only the data extents that were modified since the most recent partial backup of the same set of filegroups. |
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 |
|---|---|
A full backup of all the data in one or more files, or filegroups. .gif "Important note") 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.
|
|
A backup of one or more files that contain data extents that were changed since the most recent full backup of each file. .gif "Note") Note
Under the simple recovery model, this assumes that the data has been changed to read-only since the full backup.
|
Note
You can back up and restore full-text catalogs. For more information, see Backing Up and Restoring a SQL Server 2008 Full-Text Catalog and Piecemeal Restore and Full-Text Indexes.
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.
Important
To limit the number of log backups that you need to restore, it is essential to routinely back up your data. For example, you might schedule a weekly full database backup and daily differential database 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, copy-only backups were introduced in SQL Server 2005. 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.
Backup Compression
SQL Server 2008 Enterprise and later versions support compressing backups, and every SQL Server 2008 and later versions can restore a compressed backup. For more information, see Backup Compression (SQL Server).
Restrictions on Backup Operations in SQL Server
In SQL Server 2005 and later versions, 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.
Note
For information about how to create backups, see Creating Full and Differential Backups of a SQL Server Database and Working with Transaction Log Backups.
See Also