BACKUP

Backs up an entire database, transaction log, or one or more files or filegroups. For more information about database backup and restore operations, see Backing Up and Restoring Databases.

Syntax

Backing up an entire database:

BACKUP DATABASE { database_name | @database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
    [ BLOCKSIZE = { blocksize | @blocksize_variable } ]
    [ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
    [ [ , ] DIFFERENTIAL ]
    [ [ , ] EXPIREDATE = { date | @date_var }
        | RETAINDAYS = { days | @days_var } ]
    [ [ , ] PASSWORD = { password | @password_variable } ]
    [ [ , ] FORMAT | NOFORMAT ]
    [ [ , ] { INIT | NOINIT } ]
    [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
    [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
    [ [ , ] { NOSKIP | SKIP } ]
    [ [ , ] { NOREWIND | REWIND } ]
    [ [ , ] { NOUNLOAD | UNLOAD } ]
    [ [ , ] RESTART ]
    [ [ , ] STATS [ = percentage ] ]
]

Backing up specific files or filegroups:

BACKUP DATABASE { database_name | @database_name_var }
    < file_or_filegroup > [ ,...n ]
TO < backup_device > [ ,...n ]
[ WITH
    [ BLOCKSIZE = { blocksize | @blocksize_variable } ]
    [ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
    [ [ , ] DIFFERENTIAL ]
    [ [ , ] EXPIREDATE = { date | @date_var }
        | RETAINDAYS = { days | @days_var } ]
    [ [ , ] PASSWORD = { password | @password_variable } ]
    [ [ , ] FORMAT | NOFORMAT ]
    [ [ , ] { INIT | NOINIT } ]
    [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
    [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
    [ [ , ] { NOSKIP | SKIP } ]
    [ [ , ] { NOREWIND | REWIND } ]
    [ [ , ] { NOUNLOAD | UNLOAD } ]
    [ [ , ] RESTART ]
    [ [ , ] STATS [ = percentage ] ]
]

Backing up a transaction log:

BACKUP LOG { database_name | @database_name_var }
{
    TO < backup_device > [ ,...n ]
    [ WITH
        [ BLOCKSIZE = { blocksize | @blocksize_variable } ]
        [ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
        [ [ ,] EXPIREDATE = { date | @date_var }
            | RETAINDAYS = { days | @days_var } ]
        [ [ , ] PASSWORD = { password | @password_variable } ]
        [ [ , ] FORMAT | NOFORMAT ]
        [ [ , ] { INIT | NOINIT } ]
        [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
        [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
        [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
        [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
        [ [ , ] NO_TRUNCATE ]
        [ [ , ] { NORECOVERY | STANDBY = undo_file_name } ]
        [ [ , ] { NOREWIND | REWIND } ]
        [ [ , ] { NOSKIP | SKIP } ]
        [ [ , ] { NOUNLOAD | UNLOAD } ]
        [ [ , ] RESTART ]
        [ [ , ] STATS [ = percentage ] ]
    ]
}

< backup_device > ::=
    {
        { logical_backup_device_name | @logical_backup_device_name_var }
        |
        { DISK | TAPE } =
            { 'physical_backup_device_name' | @physical_backup_device_name_var }
    }

< file_or_filegroup > ::=
    {
        FILE = { logical_file_name | @logical_file_name_var }
        |
        FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
    }

Truncating the transaction log:

BACKUP LOG { database_name | @database_name_var }
{
    [ WITH
        { NO_LOG | TRUNCATE_ONLY } ]
}

Arguments

DATABASE

Specifies a complete database backup. If a list of files and filegroups is specified, only those files and filegroups are backed up.

Note  During a full database or differential backup, Microsoft® SQL Server™ backs up enough of the transaction log to produce a consistent database for when the database is restored. Only a full database backup can be performed on the master database.

{ database_name | @database_name_var }

Is the database from which the transaction log, partial database, or complete database is backed up. If supplied as a variable (@database_name_var), this name can be specified either as a string constant (@database_name_var = database name) or as a variable of character string data type, except for the ntext or text data types.

< backup_device >

Specifies the logical or physical backup device to use for the backup operation. Can be one or more of the following:

{ logical_backup_device_name } | { @logical_backup_device_name_var }

Is the logical name, which must follow the rules for identifiers, of the backup device(s) (created by sp_addumpdevice) to which the database is backed up. If supplied as a variable (@logical_backup_device_name_var), the backup device name can be specified either as a string constant (@logical_backup_device_name_var = logical backup device name) or as a variable of character string data type, except for the ntext or text data types.

{ DISK | TAPE } =

'physical_backup_device_name' | @physical_backup_device_name_var

Allows backups to be created on the specified disk or tape device. The physical device specified need not exist prior to executing the BACKUP statement. If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device.

When specifying TO DISK or TO TAPE, enter the complete path and file name. For example, DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Mybackup.dat' or TAPE = '\\.\TAPE0'.

Note  If a relative path name is entered for a backup to disk, the backup file is placed in the default backup directory. This directory is set during installation and stored in the BackupDirectory registry key under KEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer.

If using a network server with a Uniform Naming Convention (UNC) name or using a redirected drive letter, specify a device type of disk.

When specifying multiple files, logical file names (or variables) and physical file names (or variables) can be mixed. However, all devices must be of the same type (disk, tape, or pipe).

Backup to tape is not supported on Windows 98.

n

Is a placeholder that indicates multiple backup devices may be specified. The maximum number of backup devices is 64.

BLOCKSIZE = { blocksize | @blocksize_variable }

Specifies the physical block size, in bytes. On Windows NT systems, the default is the default block size of the device. Generally, this parameter is not required as SQL Server will choose a blocksize that is appropriate to the device. On Windows 2000-based computers, the default is 65,536 (64 KB, which is the maximum size SQL Server supports).

For DISK, BACKUP automatically determines the appropriate block size for disk devices.

Note  To transfer the resulting backup set to a CD-ROM and then restore from that CD-ROM, set BLOCKSIZE to 2048.

The default BLOCKSIZE for tape is 65,536 (64 KB). Explicitly stating a block size overrides SQL Server's selection of a block size.

DESCRIPTION = { 'text' | @text_variable }

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

DIFFERENTIAL

Specifies 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 since the last full backup do not need to be applied. For more information, see Differential Database Backups and File Differential Backups.

Note  During a full database or differential backup, SQL Server backs up enough of the transaction log to produce a consistent database when the database is restored.

EXPIREDATE = { date | @date_var }

Specifies the date when the backup set expires and can be overwritten. If supplied as a variable (@date_var), this date is specified as either a string constant (@date_var = date), as a variable of character string data type (except for the ntext or text data types), a smalldatetime, or datetime variable, and must follow the configured system datetime format.

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.

Important  If EXPIREDATE or RETAINDAYS is not specified, expiration is determined by the media retention configuration setting of sp_configure. 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.

PASSWORD = { password | @password_variable }

Sets the password for the backup set. PASSWORD is a character string. If a password is defined for the backup set, the password must be supplied to perform any restore operation from the backup set.

Important  A backup set password protects the contents of the backup set from unauthorized access through SQL Server 2000 tools, but does not protect the backup set from being overwritten.

For more information about using passwords, see the Permissions section.

FORMAT

Specifies that the media header should be written on all volumes used for this backup operation. Any existing media header is overwritten. The FORMAT option invalidates the entire media contents, ignoring any existing content.

Important  Use FORMAT carefully. Formatting one backup device or medium renders the entire media set unusable. For example, if a single tape belonging to an existing striped media set is initialized, the entire media set is rendered useless.

By specifying FORMAT, the backup operation implies SKIP and INIT; these do not need to be explicitly stated.

NOFORMAT

Specifies the media header should not be written on all volumes used for this backup operation and does not rewrite the backup device unless INIT is specified.

INIT

Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set data on that device is overwritten.

The backup media is not overwritten if any one of the following conditions is met:

• All backup sets on the media have 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 clause.

Use the SKIP option to override these checks. For more information about interactions when using SKIP, NOSKIP, INIT, and NOINIT, see the Remarks section.

Note  If the backup media is password protected, SQL Server does not write to the media unless the media password is supplied. This check is not overridden by the SKIP option. Password-protected media may be overwritten only by reformatting it. For more information, see the FORMAT option.

NOINIT

Indicates that the backup set is appended to the specified disk or tape device, preserving existing backup sets. NOINIT is the default.

The FILE option of the RESTORE command is used to select the appropriate backup set at restore time. For more information, see RESTORE.

If a media password is defined for the media set, the password must be supplied.

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, a maximum of 128 characters, for the entire backup media set. If MEDIANAME is specified, it must match the previously specified media name already existing on the backup volume(s). If not specified or if the SKIP option is specified, there is no verification check of the media name.

MEDIAPASSWORD = { mediapassword | @mediapassword_variable }

Sets the password for the media set. MEDIAPASSWORD is a character string.

If a password is defined for the media set, the password must be supplied to create a backup set on that media set. In addition, that media password also must be supplied to perform any restore operation from the media set. Password-protected media may be overwritten only by reformatting it. For more information, see the FORMAT option.

For more information about using passwords, see the Permissions section.

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.

NORECOVERY

Used only with BACKUP LOG. 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 prior to a RESTORE operation.

STANDBY = undo_file_name

Used only with BACKUP LOG. Backs up the tail of the log and leaves the database in read-only and standby mode. The undo file name specifies storage to hold rollback changes which must be undone if RESTORE LOG operations are to be subsequently applied.

If the specified undo file name does not exist, SQL Server creates it. If the file does exist, SQL Server overwrites it. For more information, see Using Standby Servers.

NOREWIND

Specifies that SQL Server will keep the tape open after the backup operation. NOREWIND implies NOUNLOAD. SQL Server will retain ownership of the tape drive until a BACKUP or RESTORE command is used WITH REWIND.

If a tape is inadvertently left open, the fastest way to release the tape is by using the following RESTORE command:

RESTORE LABELONLY FROM TAPE = <name> WITH REWIND

A list of currently open tapes can be found by querying the sysopentapes table in the master database.

REWIND

Specifies that SQL Server will release and rewind the tape. If neither NOREWIND nor REWIND is specified, REWIND is the default.

NOSKIP

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

SKIP

Disables the backup set expiration and name checking usually performed by the BACKUP statement to prevent overwrites of backup sets. For more information, see the Remarks section.

NOUNLOAD

Specifies the tape is not unloaded automatically from the tape drive after a backup. NOUNLOAD remains set until UNLOAD is specified. This option is used only for tape devices.

UNLOAD

Specifies that the tape is automatically rewound and unloaded when the backup is finished. UNLOAD is set by default when a new user session is started. It remains set until that user specifies NOUNLOAD. This option is used only for tape devices.

RESTART

Specifies that SQL Server restarts an interrupted backup operation. The RESTART option saves time because it restarts the backup operation at the point it was interrupted. To RESTART a specific backup operation, repeat the entire BACKUP statement and add the RESTART option. Using the RESTART option is not required but can save time.

Important  This option can only be used for backups directed to tape media and for backups that span multiple tape volumes. A restart operation never occurs on the first volume of the backup.

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.

< file_or_filegroup >

Specifies the logical names of the files or filegroups to include in the database backup. Multiple files or filegroups may be specified.

FILE = { logical_file_name | @logical_file_name_var }

Names one or more files to include in the database backup.

FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }

Names one or more filegroups to include in the database backup.

Note  Back up a file when the database size and performance requirements make a full database backup impractical. To back up the transaction log separately, use BACKUP LOG.

Important  To recover a database using file and filegroup backups, a separate backup of the transaction log must be provided by using BACKUP LOG. For more information about file backups, see Backing up Using File Backups.

File and filegroup backups are not allowed if the recovery model is simple.

n

Is a placeholder indicating that multiple files and filegroups may be specified. There is no maximum number of files or filegroups.

LOG

Specifies a backup of the transaction log only. The log is backed up from the last successfully executed LOG backup to the current end of the log. Once the log is backed up, the space may be truncated when no longer required by replication or active transactions.

Note  If backing up the log does not appear to truncate most of the log, an old open transaction may exist in the log. Log space can be monitored with DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log Backups.

NO_LOG | TRUNCATE_ONLY

Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.

After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.

NO_TRUNCATE

Allows backing up the log in situations where the database is damaged.

Remarks

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.

SQL Server uses an online backup process to allow a database backup while the database is still in use. The following list includes operations that cannot run during a database or transaction log backup:

• File management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options; INSERT, UPDATE, or DELETE statements are allowed during a backup operation.
  
  
• Shrink database or shrink file. This includes autoshrink operations.

If a backup is started when one of these operations is in progress, the backup ends. If a backup is running and one of these operations is attempted, the operation fails.

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. For more information, see SQL Server Collation Fundamentals.

Backup File Format

SQL Server backups can coexist on tape media with Windows NT backups because the SQL Server 2000 backup format conforms to Microsoft Tape Format (MTF); the same format used by Windows NT tape backups. To ensure interoperability, the tape should be formatted by NTBackup.

Backup Types

Backup types supported by SQL Server include:

• Full database backup, which backs up the entire database including the transaction log.
  
  
• Differential database backup performed between full database backups.
  
  
• Transaction log backup.

  A sequence of log backups provides for a continuous chain of transaction information to support recovery forward from database, differential, or file backups.

• File(s) and Filegroup(s) backup.

  Use BACKUP to back up database files and filegroups instead of the full database when time constraints make a full database backup impractical. To back up a file instead of the full database, put procedures in place to ensure that all files in the database are backed up regularly. Also, separate transaction log backups must be performed. After restoring a file backup, apply the transaction log to roll the file contents forward to make it consistent with the rest of the database.

Backup devices used in a stripe set must always be used in a stripe set (unless reinitialized at some point with FORMAT) with the same number of devices. After a backup device is defined as part of a stripe set, it cannot be used for a single devicebackup unless FORMAT is specified. Similarly, a backup device that contains nonstriped backups cannot be used in a stripe set unless FORMAT is specified. Use FORMAT to split a striped backup set.

If neither MEDIANAME nor MEDIADESCRIPTION is specified when a media header is written, the media header field corresponding to the blank item is empty.

BACKUP LOG cannot be used if the recovery model is SIMPLE. Use BACKUP DATABASE instead.

Interaction of SKIP, NOSKIP, INIT, and NOINIT

This table shows how the { INIT | NOINIT } and { NOSKIP | SKIP } clauses interact.

Note  In all these interactions, if the tape media is empty or the disk backup file does not exist, write a media header and proceed. If the media is not empty and does not contain a valid media header, give feedback that this is not valid MTF media and abort the backup.

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 and provide the correct media password to perform a backup operation.

Note  To maintain backward compatibility, the DUMP keyword can be used in place of the BACKUP keyword in the BACKUP statement syntax. In addition, the TRANSACTION keyword can be used in place of the LOG keyword.

Backup History Tables

SQL Server includes these backup history tables that track backup activity:

• backupfile
  
  
• backupmediafamily
  
  
• backupmediaset
  
  
• backupset

When a RESTORE is performed, the backup history tables are modified.

Compatibility Considerations

Caution  Backups created with Microsoft® SQL Server™ 2000 cannot be restored in earlier versions of SQL Server.

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.

In addition, the user may specify passwords for a media set, a backup set, or both. When a password is defined on a media set, it is not enough that a user is a member of appropriate fixed server and database roles to perform a backup. The user also must supply the media password to perform these operations. Similarly, restore is not allowed unless the correct media password and backup set password are specified in the restore command.

Defining passwords for backup sets and media sets is an optional feature in the BACKUP statement. The passwords will prevent unauthorized restore operations and unauthorized appends of backup sets to media using SQL Server 2000 tools, but passwords do not prevent overwrite of media with the FORMAT option.

Thus, although the use of passwords can help protect the contents of media from unauthorized access using SQL Server tools, passwords do not protect contents from being destroyed. Passwords do not fully prevent unauthorized access to the contents of the media because the data in the backup sets is not encrypted and could theoretically be examined by programs specifically created for this purpose. For situations where security is crucial, it is important to prevent access to the media by unauthorized individuals.

It is an error to specify a password for objects that were not created with associated passwords.

BACKUP creates the backup set with the backup set password supplied through the PASSWORD option. In addition, BACKUP will normally verify the media password given by the MEDIAPASSWORD option prior to writing to the media. The only time that BACKUP will not verify the media password is when it formats the media, which overwrites the media header. BACKUP formats the media only:

• If the FORMAT option is specified.
  
  
• If the media header is invalid and INIT is specified.
  
  
• If the operation is writing a continuation volume.

If BACKUP writes the media header, BACKUP will assign the media set password to the value specified in the MEDIAPASSWORD option.

For more information about the impact of passwords on SKIP, NOSKIP, INIT, and NOINIT options, see the Remarks section.

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

Examples

A. Back up the entire MyNwind database

Note  The MyNwind database is shown for illustration only.

This example creates a logical backup device in which a full backup of the MyNwind database is placed.

-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1', 
   DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'

-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1

B. Back up the database and log

This example creates both a full database and log backup. The database is backed up to a logical backup device called MyNwind_2, and then the log is backed up to a logical backup device called MyNwindLog1.

Note  Creating a logical backup device needs to be done only once.

-- Create the backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_2',
   'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_2.dat'

--Create the log backup device.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwindLog1',
   'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwindLog1.dat'

-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_2

-- Update activity has occurred since the full database backup.

-- Back up the log of the MyNwind database.
BACKUP LOG MyNwind 
   TO MyNwindLog1

See Also

Backup Formats

DBCC SQLPERF

RESTORE

RESTORE FILELISTONLY

RESTORE HEADERONLY

RESTORE LABELONLY

RESTORE VERIFYONLY

sp_addumpdevice

sp_configure

sp_dboption

sp_helpfile

sp_helpfilegroup

Using Identifiers

Using Media Sets and Families