Backup Overview (SQL Server)
This topic introduces the SQL Server backup component. Backing up your SQL Server database is essential for protecting your data. This discussion covers backup types, and backup restrictions. The topic also introduces SQL Server backup devices and backup media.
Note
|
|---|
|
Starting with SQL Server 2012 SP1 Cumulative Update 2, SQL Server backup to the Windows Azure Blob storage service is supported. For more information, see Backup and Restore Enhancements, and SQL Server Backup and Restore with Windows Azure Blob Storage Service. |
In this Topic:
Types of Backups
Backup Media Terms and Definitions
|
Note
|
|---|
|
Starting with SQL Server 2012 SP1 Cumulative Update 2, SQL Server backup to the Windows Azure Blob storage service is supported. For more information, see Backup and Restore Enhancements, and SQL Server Backup and Restore with Windows Azure Blob Storage Service. |
SQL Server 2008 Enterprise and later versions support compressing backups, and SQL Server 2008 and later versions can restore a compressed backup. For more information, see Backup Compression (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.
To work with backup devices and backup media
-
Define a Logical Backup Device for a Tape Drive (SQL Server)
-
View the Properties and Contents of a Logical Backup Device (SQL Server)
To create a backup
|
Note
|
|---|
|
For partial or copy-only backups, you must use the Transact-SQL BACKUP statement with the PARTIAL or COPY_ONLY option, respectively. |
