BACKUP (Transact-SQL)

Specifies options to be used with a backup operation.

DIFFERENTIAL

Used only with BACKUP DATABASE, specifies that the database or file backup should consist only of the portions of the database or file changed since the last full backup. A differential backup usually takes up less space than a full backup. Use this option so that all individual log backups performed since the last full backup do not have to be applied.

Note
By default, BACKUP DATABASE creates a full backup.

For more information, see Differential Backups (SQL Server).

Backup Set Options

These options operate on the backup set that is created by this backup operation.

Note
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).

COPY_ONLY

Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.

Copy-only backups were introduced in SQL Server 2005 for use in situations in which a backup is taken for a special purpose, such as backing up the log before an online file restore. Typically, a copy-only log backup is used once and then deleted.

• When used with BACKUP DATABASE, the COPY_ONLY option creates a full backup that cannot serve as a differential base. The differential bitmap is not updated, and differential backups behave as if the copy-only backup does not exist. Subsequent differential backups use the most recent conventional full backup as their base.

  Important
  If DIFFERENTIAL and COPY_ONLY are used together, COPY_ONLY is ignored, and a differential backup is created.

• When used with BACKUP LOG, the COPY_ONLY option creates a copy-only log backup, which does not truncate the transaction log. The copy-only log backup has no effect on the log chain, and other log backups behave as if the copy-only backup does not exist.

For more information, see Copy-Only Backups (SQL Server).

{ COMPRESSION | NO_COMPRESSION }

In SQL Server 2008 Enterprise and later versions only, specifies whether backup compression is performed on this backup, overriding the server-level default.

At installation, the default behavior is no backup compression. But this default can be changed by setting the backup compression default server configuration option. For information about viewing the current value of this option, see View or Change Server Properties (SQL Server).

COMPRESSION

Explicitly enables backup compression.

NO_COMPRESSION

Explicitly disables backup compression.

DESCRIPTION = { 'text' | @text_variable }

Specifies the free-form text describing the backup set. The string can have a maximum of 255 characters.

NAME = { backup_set_name| @backup_set_var }

Specifies the name of the backup set. Names can have a maximum of 128 characters. If NAME is not specified, it is blank.

{ EXPIREDATE = 'date'| RETAINDAYS = days }

Specifies when the backup set for this backup can be overwritten. If these options are both used, RETAINDAYS takes precedence over EXPIREDATE.

If neither option is specified, the expiration date is determined by the media retention configuration setting. For more information, see Server Configuration Options (SQL Server).

Important
These options only prevent SQL Server from overwriting a file. Tapes can be erased using other methods, and disk files can be deleted through the operating system. For more information about expiration verification, see SKIP and FORMAT in this topic.

EXPIREDATE = { 'date'| @date_var }

Specifies when the backup set expires and can be overwritten. If supplied as a variable (@date_var), this date must follow the configured system datetime format and be specified as one of the following:

• A string constant (@date_var = date)

• A variable of character string data type (except for the ntext or text data types)

• A smalldatetime

• A datetime variable

For example:

• 'Dec 31, 2020 11:59 PM'

• '1/1/2021'

For information about how to specify datetime values, see Date and Time Types.

Note
To ignore the expiration date, use the SKIP option.

RETAINDAYS = { days| @days_var }

Specifies the number of days that must elapse before this backup media set can be overwritten. If supplied as a variable (@days_var), it must be specified as an integer.

Media Set Options

These options operate on the media set as a whole.

{ NOINIT | INIT }

Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).

Note
For information about the interactions between { NOINIT | INIT } and { NOSKIP | SKIP }, see "Remarks," later in this topic.

NOINIT

Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default.

For more information, see Media Sets, Media Families, and Backup Sets (SQL Server).

INIT

Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set on that device is overwritten, if conditions permit. By default, BACKUP checks for the following conditions and does not overwrite the backup media if either condition exists:

• Any backup set has not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.

• The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME option, earlier in this section.

To override these checks, use the SKIP option.

For more information, see Media Sets, Media Families, and Backup Sets (SQL Server).

{ NOSKIP | SKIP }

Controls whether a backup operation checks the expiration date and time of the backup sets on the media before overwriting them.

Note
For information about the interactions between { NOINIT | INIT } and { NOSKIP | SKIP }, see "Remarks," later in this topic.

NOSKIP

Instructs the BACKUP statement to check the expiration date of all backup sets on the media before allowing them to be overwritten. This is the default behavior.

SKIP

Disables the checking of backup set expiration and name that is usually performed by the BACKUP statement to prevent overwrites of backup sets. For information about the interactions between { INIT | NOINIT } and { NOSKIP | SKIP }, see "Remarks," later in this topic.

To view the expiration dates of backup sets, query the expiration_date column of the backupset history table.

{ NOFORMAT | FORMAT }

Specifies whether the media header should be written on the volumes used for this backup operation, overwriting any existing media header and backup sets.

NOFORMAT

Specifies that the backup operation preserves the existing media header and backup sets on the media volumes used for this backup operation. This is the default behavior.

FORMAT

Specifies that a new media set be created. FORMAT causes the backup operation to write a new media header on all media volumes used for the backup operation. The existing contents of the volume become invalid, because any existing media header and backup sets are overwritten.

Important
Use FORMAT carefully. Formatting any volume of a media set renders the entire media set unusable. For example, if you initialize a single tape belonging to an existing striped media set, the entire media set is rendered useless.

Specifying FORMAT implies SKIP; SKIP does not need to be explicitly stated.

MEDIADESCRIPTION = { text | @text_variable }

Specifies the free-form text description, maximum of 255 characters, of the media set.

MEDIANAME = { media_name | @media_name_variable }

Specifies the media name for the entire backup media set. The media name must be no longer than 128 characters, If MEDIANAME is specified, it must match the previously specified media name already existing on the backup volumes. If it is not specified, or if the SKIP option is specified, there is no verification check of the media name.

BLOCKSIZE = { blocksize | @blocksize_variable }

Specifies the physical block size, in bytes. The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64 KB) bytes. The default is 65536 for tape devices and 512 otherwise. Typically, this option is unnecessary because BACKUP automatically selects a block size that is appropriate to the device. Explicitly stating a block size overrides the automatic selection of block size.

If you are taking a backup that you plan to copy onto and restore from a CD-ROM, specify BLOCKSIZE=2048.

Note
This option typically affects performance only when writing to tape devices.

Data Transfer Options

BUFFERCOUNT = { buffercount | @buffercount_variable }

Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process.

The total space used by the buffers is determined by: buffercount * maxtransfersize.

Note
For important information about using the BUFFERCOUNT option, see the Incorrect BufferCount data transfer option can lead to OOM condition blog.

MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

Specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).

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.

{ NO_CHECKSUM | CHECKSUM }

Controls whether backup checksums are enabled.

NO_CHECKSUM

Explicitly disables the generation of backup checksums (and the validation of page checksums). This is the default behavior, except for a compressed backup.

CHECKSUM

Specifies that the backup operation will verify each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup. This is the default behavior for a compressed backup.

Using backup checksums may affect workload and backup throughput.

For more information, see Possible Media Errors During Backup and Restore (SQL Server).

{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

Controls whether a backup operation stops or continues after encountering a page checksum error.

STOP_ON_ERROR

Instructs BACKUP to fail if a page checksum does not verify. This is the default behavior.

CONTINUE_AFTER_ERROR

Instructs BACKUP to continue despite encountering errors such as invalid checksums or torn pages.

If you are unable to back up the tail of the log using the NO_TRUNCATE option when the database is damaged, you can attempt a tail-log log backup by specifying CONTINUE_AFTER_ERROR instead of NO_TRUNCATE.

For more information, see Possible Media Errors During Backup and Restore (SQL Server).

Compatibility Options

RESTART

Beginning with SQL Server 2008, has no effect. This option is accepted by the version for compatibility with previous versions of SQL Server.

Monitoring Options

STATS [ =percentage ]

Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.

The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.

Tape Options

These options are used only for TAPE devices. If a nontape device is being used, these options are ignored.

{ REWIND | NOREWIND }

REWIND

Specifies that SQL Server will release and rewind the tape. REWIND is the default.

NOREWIND

Specifies that SQL Server will keep the tape open after the backup operation. You can use this option to help improve performance when performing multiple backup operations to a tape.

NOREWIND implies NOUNLOAD, and these options are incompatible within a single BACKUP statement.

Note

If you use NOREWIND, the instance of SQL Server retains ownership of the tape drive until a BACKUP or RESTORE statement that is running in the same process uses either the REWIND or UNLOAD option, or the server instance is shut down. Keeping the tape open prevents other processes from accessing the tape. For information about how to display a list of open tapes and to close an open tape, see Backup Devices (SQL Server).

{ UNLOAD | NOUNLOAD }

Note
UNLOAD/NOUNLOAD is a session setting that persists for the life of the session or until it is reset by specifying the alternative.

UNLOAD

Specifies that the tape is automatically rewound and unloaded when the backup is finished. UNLOAD is the default when a session begins.

NOUNLOAD

Specifies that after the BACKUP operation the tape will remain loaded on the tape drive.

Note
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.

Note
If you do not want to take log backups, use the simple recovery model. For more information, see Recovery Models (SQL Server).

{ NORECOVERY | STANDBY =undo_file_name }

NORECOVERY

Backs up the tail of the log and leaves the database in the RESTORING state. NORECOVERY is useful when failing over to a secondary database or when saving the tail of the log before a RESTORE operation.

To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together.

STANDBY =standby_file_name

Backs up the tail of the log and leaves the database in a read-only and STANDBY state. The STANDBY clause writes standby data (performing rollback, but with the option of further restores). Using the STANDBY option is equivalent to BACKUP LOG WITH NORECOVERY followed by a RESTORE WITH STANDBY.

Using standby mode requires a standby file, specified by standby_file_name, whose location is stored in the log of the database. If the specified file already exists, the Database Engine overwrites it; if the file does not exist, the Database Engine creates it. The standby file becomes part of the database.

This file holds the rolled back changes, which must be reversed if RESTORE LOG operations are to be subsequently applied. There must be enough disk space for the standby file to grow so that it can contain all the distinct pages from the database that were modified by rolling back uncommitted transactions.

NO_TRUNCATE

Specifies that the log not be truncated and causes the Database Engine to attempt the backup regardless of the state of the database. Consequently, a backup taken with NO_TRUNCATE might have incomplete metadata. This option allows backing up the log in situations where the database is damaged.

The NO_TRUNCATE option of BACKUP LOG is equivalent to specifying both COPY_ONLY and CONTINUE_AFTER_ERROR.

Without the NO_TRUNCATE option, the database must be in the ONLINE state. If the database is in the SUSPENDED state, you might be able to create a backup by specifying NO_TRUNCATE. But if the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE. For information about database states, see Database States.

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).

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).

Note
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).

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.

Backup Devices in a Striped Media Set (a Stripe Set)

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

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:

Copy

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

Important
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).

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).

This table describes interactions between the { NOINIT | INIT } and { NOSKIP | SKIP } options.

Note
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.

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.

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.

The following example backs up the AdventureWorks2012  database to a disk file.

BACKUP DATABASE AdventureWorks2012 
 TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
   WITH FORMAT;
GO

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

Note
For a production database, back up the log regularly. Log backups should be frequent enough to provide sufficient protection against data loss.

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

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

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';

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';

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';

Note
NOINIT, which is the default, is shown here for clarity.

[Top of examples]

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;

[Top of examples]