BACKUP (Transact-SQL)
Backs up a complete SQL Server database to create a database backup, or one or more files or filegroups of the database to create a file backup (BACKUP DATABASE). Also, under the full recovery model or bulk-logged recovery model, backs up the transaction log of the database to create a log backup (BACKUP LOG).
![]() |
---|
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. |
Backing Up a Whole Database BACKUP DATABASE { database_name | @database_name_var } TO <backup_device> [ ,...n ] [ <MIRROR TO clause> ] [ next-mirror-to ] [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ] [;] Backing Up Specific Files or Filegroups BACKUP DATABASE { database_name | @database_name_var } <file_or_filegroup> [ ,...n ] TO <backup_device> [ ,...n ] [ <MIRROR TO clause> ] [ next-mirror-to ] [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ] [;] Creating a Partial Backup BACKUP DATABASE { database_name | @database_name_var } READ_WRITE_FILEGROUPS [ , <read_only_filegroup> [ ,...n ] ] TO <backup_device> [ ,...n ] [ <MIRROR TO clause> ] [ next-mirror-to ] [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ] [;] Backing Up the Transaction Log (full and bulk-logged recovery models) BACKUP LOG { database_name | @database_name_var } TO <backup_device> [ ,...n ] [ <MIRROR TO clause> ] [ next-mirror-to ] [ WITH { <general_WITH_options> | <log-specific_optionspec> } [ ,...n ] ] [;] <backup_device>::= { { logical_device_name | @logical_device_name_var } | { DISK | TAPE } = { 'physical_device_name' | @physical_device_name_var } } <MIRROR TO clause>::= MIRROR TO <backup_device> [ ,...n ] <file_or_filegroup>::= { FILE = { logical_file_name | @logical_file_name_var } | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } } <read_only_filegroup>::= FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } <general_WITH_options> [ ,...n ]::= --Backup Set Options COPY_ONLY | { COMPRESSION | NO_COMPRESSION } | DESCRIPTION = { 'text' | @text_variable } | NAME = { backup_set_name | @backup_set_name_var } | { EXPIREDATE = { 'date' | @date_var } | RETAINDAYS = { days | @days_var } } --Media Set Options { NOINIT | INIT } | { NOSKIP | SKIP } | { NOFORMAT | FORMAT } | MEDIADESCRIPTION = { 'text' | @text_variable } | MEDIANAME = { media_name | @media_name_variable } | BLOCKSIZE = { blocksize | @blocksize_variable } --Data Transfer Options BUFFERCOUNT = { buffercount | @buffercount_variable } | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable } --Error Management Options { NO_CHECKSUM | CHECKSUM } | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } --Compatibility Options RESTART --Monitoring Options STATS [ = percentage ] --Tape Options { REWIND | NOREWIND } | { UNLOAD | NOUNLOAD } --Log-specific Options { NORECOVERY | STANDBY = undo_file_name } | NO_TRUNCATE
WITH Options
Specifies options to be used with a backup operation.
Backup Set Options
These options operate on the backup set that is created by this backup operation.
![]() |
---|
To specify a backup set for a restore operation, use the FILE = <backup_set_file_number> option. For more information about how to specify a backup set, see "Specifying a Backup Set" in RESTORE Arguments (Transact-SQL). |
Media Set Options
These options operate on the media set as a whole.
Data Transfer Options
Error Management Options
These options allow you to determine whether backup checksums are enabled for the backup operation and whether the operation will stop on encountering an error.
Compatibility Options
Monitoring Options
Tape Options
These options are used only for TAPE devices. If a nontape device is being used, these options are ignored.
![]() |
---|
For a backup to a tape backup device, the BLOCKSIZE option to affect the performance of the backup operation. This option typically affects performance only when writing to tape devices. |
Log-specific Options
These options are only used with BACKUP LOG.
![]() |
---|
If you do not want to take log backups, use the simple recovery model. For more information, see Recovery Models (SQL Server). |
This section introduces the following essential backup concepts:
Working with Backup Devices and Media Sets
![]() |
---|
For an introduction to backup in SQL Server, see Backup Overview (SQL Server). |
Backup Types
The supported backup types depend on the recovery model of the database, as follows
-
All recovery models support full and differential backups of data.
Scope of backup
Backup types
Whole database
Database backups cover the whole database.
Optionally, each database backup can serve as the base of a series of one or more differential database backups.
Partial database
Partial backups cover read/-write filegroups and, possibly, one or more read-only files or filegroups.
Optionally, each partial backup can serve as the base of a series of one or more differential partial backups.
File or filegroup
File backups cover one or more files or filegroups, and are relevant only for databases that contain multiple filegroups. Under the simple recovery model, file backups are essentially restricted to read-only secondary filegroups.
Optionally, each file backup can serve as the base of a series of one or more differential file backups.
-
Under the full recovery model or bulk-logged recovery model, conventional backups also include sequential transaction log backups (or log backups), which are required. Each log backup covers the portion of the transaction log that was active when the backup was created, and it includes all log records not backed up in a previous log backup.
To minimize work-loss exposure, at the cost of administrative overhead, you should schedule frequent log backups. Scheduling differential backups between full backups can reduce restore time by reducing the number of log backups you have to restore after restoring the data.
We recommend that you put log backups on a separate volume than the database backups.
Note
Before you can create the first log backup, you must create a full backup.
-
A copy-only backup is a special-purpose full backup or log backup that is independent of the normal sequence of conventional backups. To create a copy-only backup, specify the COPY_ONLY option in your BACKUP statement. For more information, see Copy-Only Backups (SQL Server).
Transaction Log Truncation
To avoid filling up the transaction log of a database, routine backups are essential. Under the simple recovery model, log truncation occurs automatically after you back up the database, and under the full recovery model, after you back up the transaction log. However, sometimes the truncation process can be delayed. For information about factors that can delay log truncation, see The Transaction Log (SQL Server).
![]() |
---|
The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model. For more information, see View or Change the Recovery Model of a Database (SQL Server). |
Formatting Backup Media
Backup media is formatted by a BACKUP statement if and only if any of the following is true:
-
The FORMAT option is specified.
-
The media is empty.
-
The operation is writing a continuation tape.
Working with Backup Devices and Media Sets
Backup Devices in a Striped Media Set (a Stripe Set)
A stripe set is a set of disk files on which data is divided into blocks and distributed in a fixed order. The number of backup devices used in a stripe set must stay the same (unless the media is reinitialized with FORMAT).
The following example writes a backup of the AdventureWorks2012 database to a new striped media set that uses three disk files.
BACKUP DATABASE AdventureWorks2012 TO DISK='X:\SQLServerBackups\AdventureWorks1.bak', DISK='Y:\SQLServerBackups\AdventureWorks2.bak', DISK='Z:\SQLServerBackups\AdventureWorks3.bak' WITH FORMAT, MEDIANAME = 'AdventureWorksStripedSet0', MEDIADESCRIPTION = 'Striped media set for AdventureWorks2012 database; GO
After a backup device is defined as part of a stripe set, it cannot be used for a single-device backup unless FORMAT is specified. Similarly, a backup device that contains nonstriped backups cannot be used in a stripe set unless FORMAT is specified. To split a striped backup set, use FORMAT.
If neither MEDIANAME nor MEDIADESCRIPTION is specified when a media header is written, the media header field corresponding to the blank item is empty.
Working with a Mirrored Media Set
Typically, backups are unmirrored, and BACKUP statements simply include a TO clause. However, a total of four mirrors is possible per media set. For a mirrored media set, the backup operation writes to multiple groups of backup devices. Each group of backup devices comprises a single mirror within the mirrored media set. Every mirror must use the same quantity and type of physical backup devices, which must all have the same properties.
To back up to a mirrored media set, all of the mirrors must be present. To back up to a mirrored media set, specify the TO clause to specify the first mirror, and specify a MIRROR TO clause for each additional mirror.
For a mirrored media set, each MIRROR TO clause must list the same number and type of devices as the TO clause. The following example writes to a mirrored media set that contains two mirrors and uses three devices per mirror:
BACKUP DATABASE AdventureWorks2012 TO DISK='X:\SQLServerBackups\AdventureWorks1a.bak', DISK='Y:\SQLServerBackups\AdventureWorks2a.bak', DISK='Z:\SQLServerBackups\AdventureWorks3a.bak' MIRROR TO DISK='X:\SQLServerBackups\AdventureWorks1b.bak', DISK='Y:\SQLServerBackups\AdventureWorks2b.bak', DISK='Z:\SQLServerBackups\AdventureWorks3b.bak'; GO
![]() |
---|
This example is designed to allow you to test it on your local system. In practice, backing up to multiple devices on the same drive would hurt performance and would eliminate the redundancy for which mirrored media sets are designed. |
Media Families in Mirrored Media Sets
Each backup device specified in the TO clause of a BACKUP statement corresponds to a media family. For example, if the TO clauses lists three devices, BACKUP writes data to three media families. In a mirrored media set, every mirror must contain a copy of every media family. This is why the number of devices must be identical in every mirror.
When multiple devices are listed for each mirror, the order of the devices determines which media family is written to a particular device. For example, in each of the device lists, the second device corresponds to the second media family. For the devices in the above example, the correspondence between devices and media families is shown in the following table.
Mirror | Media family 1 | Media family 2 | Media family 3 |
---|---|---|---|
0 | Z:\AdventureWorks1a.bak | Z:\AdventureWorks2a.bak | Z:\AdventureWorks3a.bak |
1 | Z:\AdventureWorks1b.bak | Z:\AdventureWorks2b.bak | Z:\AdventureWorks3b.bak |
A media family must always be backed up onto the same device within a specific mirror. Therefore, each time you use an existing media set, list the devices of each mirror in the same order as they were specified when the media set was created.
For more information about mirrored media sets, see Mirrored Backup Media Sets (SQL Server). For more information about media sets and media families in general, see Media Sets, Media Families, and Backup Sets (SQL Server).
Restoring SQL Server Backups
To restore a database and, optionally, recover it to bring it online, or to restore a file or filegroup, use either the Transact-SQL RESTORE statement or the SQL Server Management Studio Restore tasks. For more information see Restore and Recovery Overview (SQL Server).
Interaction of SKIP, NOSKIP, INIT, and NOINIT
This table describes interactions between the { NOINIT | INIT } and { NOSKIP | SKIP } options.
![]() |
---|
If the tape media is empty or the disk backup file does not exist, all these interactions write a media header and proceed. If the media is not empty and lacks a valid media header, these operations give feedback stating that this is not valid MTF media, and they terminate the backup operation. |
|
NOINIT |
INIT |
---|---|---|
NOSKIP |
If the volume contains a valid media header, verifies that the media name matches the given MEDIANAME, if any. If it matches, appends the backup set, preserving all existing backup sets. If the volume does not contain a valid media header, an error occurs. |
If the volume contains a valid media header, performs the following checks:
If these checks pass, overwrites any backup sets on the media, preserving only the media header. If the volume does not contain a valid media header, generates one with using specified MEDIANAME and MEDIADESCRIPTION, if any. |
SKIP |
If the volume contains a valid media header, appends the backup set, preserving all existing backup sets. |
If the volume contains a valid1 media header, overwrites any backup sets on the media, preserving only the media header. If the media is empty, generates a media header using the specified MEDIANAME and MEDIADESCRIPTION, if any. |
1 Validity includes the MTF version number and other header information. If the version specified is unsupported or an unexpected value, an error occurs.
2 The user must belong to the appropriate fixed database or server roles to perform a backup operation.
Database or log backups can be appended to any disk or tape device, allowing a database and its transaction logs to be kept within one physical location.
The BACKUP statement is not allowed in an explicit or implicit transaction.
Cross-platform backup operations, even between different processor types, can be performed as long as the collation of the database is supported by the operating system.
![]() |
---|
By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If back up the log very frequently, these success messages accumulate quickly, resulting in huge error logs that can make finding other messages difficult. In such cases you can suppress these log entries by using trace flag 3226 if none of your scripts depend on those entries. For more information, see Trace Flags (Transact-SQL). |
SQL Server uses an online backup process to allow a database backup while the database is still in use. During a backup, most operations are possible; for example, INSERT, UPDATE, or DELETE statements are allowed during a backup operation.
Operations that cannot run during a database or transaction log backup include:
-
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 a backup operation overlaps with a file-management or shrink operation, a conflict arises. 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 timeout setting). If the lock is released during the time-out period, the second operation continues. If the lock times out, the second operation fails.
Beginning with SQL Server 2012 the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. It is still possible to restore backups created with passwords.
Permissions
BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.
This section contains the following examples:
-
D. Creating a differential file backup of the secondary filegroups
-
E. Creating and backing up to a single-family mirrored media set
-
F. Creating and backing up to a multifamily mirrored media set
![]() |
---|
The backup how-to topics contain additional examples. For more information, see Backup Overview (SQL Server). |
A. Backing up a complete database
The following example backs up the AdventureWorks2012 database to a disk file.
BACKUP DATABASE AdventureWorks2012 TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak' WITH FORMAT; GO
B. Backing up the database and log
The following example backups up the AdventureWorks2012 sample database, which uses the simple recovery model by default. To support log backups, the AdventureWorks2012 database is modified to use the full recovery model.
Next, the example uses sp_addumpdevice to create a logical backup device for backing up data, AdvWorksData, and creates another logical backup device for backing up the log, AdvWorksLog.
The example then creates a full database backup to AdvWorksData, and after a period of update activity, backs up the log to AdvWorksLog.
-- To permit log backups, before the full database backup, modify the database -- to use the full recovery model. USE master; GO ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO -- Create AdvWorksData and AdvWorksLog logical backup devices. USE master GO EXEC sp_addumpdevice 'disk', 'AdvWorksData', 'Z:\SQLServerBackups\AdvWorksData.bak'; GO EXEC sp_addumpdevice 'disk', 'AdvWorksLog', 'X:\SQLServerBackups\AdvWorksLog.bak'; GO -- Back up the full AdventureWorks2012 database. BACKUP DATABASE AdventureWorks2012 TO AdvWorksData; GO -- Back up the AdventureWorks2012 log. BACKUP LOG AdventureWorks2012 TO AdvWorksLog; GO
![]() |
---|
For a production database, back up the log regularly. Log backups should be frequent enough to provide sufficient protection against data loss. |
C. Creating a full file backup of the secondary filegroups
The following example creates a full file backup of every file in both of the secondary filegroups.
--Back up the files in SalesGroup1: BACKUP DATABASE Sales FILEGROUP = 'SalesGroup1', FILEGROUP = 'SalesGroup2' TO DISK = 'Z:\SQLServerBackups\SalesFiles.bck'; GO
D. Creating a differential file backup of the secondary filegroups
The following example creates a differential file backup of every file in both of the secondary filegroups.
--Back up the files in SalesGroup1: BACKUP DATABASE Sales FILEGROUP = 'SalesGroup1', FILEGROUP = 'SalesGroup2' TO DISK = 'Z:\SQLServerBackups\SalesFiles.bck' WITH DIFFERENTIAL; GO
E. Creating and backing up to a single-family mirrored media set
The following example creates a mirrored media set containing a single media family and four mirrors and backs up the AdventureWorks2012 database to them.
BACKUP DATABASE AdventureWorks2012 TO TAPE = '\\.\tape0' MIRROR TO TAPE = '\\.\tape1' MIRROR TO TAPE = '\\.\tape2' MIRROR TO TAPE = '\\.\tape3' WITH FORMAT, MEDIANAME = 'AdventureWorksSet0';
F. Creating and backing up to a multifamily mirrored media set
The following example creates a mirrored media set in which each mirror consists of two media families. The example then backs up the AdventureWorks2012 database to both mirrors.
BACKUP DATABASE AdventureWorks2012 TO TAPE = '\\.\tape0', TAPE = '\\.\tape1' MIRROR TO TAPE = '\\.\tape2', TAPE = '\\.\tape3' WITH FORMAT, MEDIANAME = 'AdventureWorksSet1';
G. Backing up to an existing mirrored media set
The following example appends a backup set to the media set created in the preceding example.
BACKUP LOG AdventureWorks2012 TO TAPE = '\\.\tape0', TAPE = '\\.\tape1' MIRROR TO TAPE = '\\.\tape2', TAPE = '\\.\tape3' WITH NOINIT, MEDIANAME = 'AdventureWorksSet1';
![]() |
---|
NOINIT, which is the default, is shown here for clarity. |
H. Creating a compressed backup in a new media set
The following example formats the media, creating a new media set, and perform a compressed full backup of the AdventureWorks2012 database.
BACKUP DATABASE AdventureWorks2012 TO DISK='Z:\SQLServerBackups\AdvWorksData.bak' WITH FORMAT, COMPRESSION;