Backs up an entire database, transaction log, or one or more files or filegroups. For more information about database back up and restore operations, see Backing Up and Restoring Databases.
Caution Backups created with Microsoft® SQL Server™ version 7.0 cannot be restored in earlier versions of SQL Server.
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}]
[[,] FORMAT | NOFORMAT]
[[,] {INIT | NOINIT}]
[[,] MEDIADESCRIPTION = {text | @text_variable}]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] [NAME = {backup_set_name | @backup_set_name_var}]
[[,] {NOSKIP | SKIP}]
[[,] {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}]
[[,] EXPIREDATE = {date | @date_var}
| RETAINDAYS = {days | @days_var}]
[[,] FORMAT | NOFORMAT]
[[,] {INIT | NOINIT}]
[[,] MEDIADESCRIPTION = {text | @text_variable}]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] [NAME = {backup_set_name | @backup_set_name_var}]
[[,] {NOSKIP | SKIP}]
[[,] {NOUNLOAD | UNLOAD}]
[[,] [RESTART]
[[,] STATS [= percentage]]
]
Backing up a transaction log:
BACKUP LOG {database_name | @database_name_var}
{
[WITH
{ NO_LOG | TRUNCATE_ONLY }]
}
|
{
TO <backup_device> [,...n]
[WITH
[BLOCKSIZE = {blocksize | @blocksize_variable}]
[[,] DESCRIPTION = {text | @text_variable}]
[[,] EXPIREDATE = {date | @date_var}
| RETAINDAYS = {days | @days_var}]
[[,] FORMAT | NOFORMAT]
[[,] {INIT | NOINIT}]
[[,] MEDIADESCRIPTION = {text | @text_variable}]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] [NAME = {backup_set_name | @backup_set_name_var}]
[[,] NO_TRUNCATE]
[[,] {NOSKIP | SKIP}]
[[,] {NOUNLOAD | UNLOAD}]
[[,] [RESTART]
[[,] STATS [= percentage]]
]
}
<backup_device> ::=
{
{backup_device_name | @backup_device_name_var}
|
{DISK | TAPE | PIPE} =
{'temp_backup_device' | @temp_backup_device_var}
}
<file_or_filegroup> ::=
{
FILE = {logical_file_name | @logical_file_name_var}
|
FILEGROUP = {logical_filegroup_name | @logical_filegroup_name_var}
}
Note During a full database or differential backup, 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.
Allows backups to be created on the named disk, tape, or pipe device. When using a temporary disk backup device, the temporary disk backup devices must not exist prior to specifying the BACKUP statement. When using a tape backup device, specify WITH FORMAT to ensure that the backup device is a valid Microsoft Tape Format (MTF) data set.
Important Use FORMAT carefully because it overwrites all contents on the current tape.
With disk and tape, specify the complete path and file name. For example, DISK = ‘C:\Mssql7\Backup\Mybackup.dat’ or TAPE = \\.\TAPE0. With pipe, specify the name of the named pipe that will be used by the client application. If supplied as a variable (@temp_backup_name_var), the temporary backup device name can be specified as a string constant or as a variable of character string data type, except for the ntext or text data types.
Pipe files have been added to allow third-party vendors a flexible and powerful way to connect their own software. For typical Transact-SQL use, pipe files are not used.
Note When performing a backup to a named pipe, you may need to adjust the number of minutes that SQL Server waits for the client to connect before reporting a failure. To adjust this time-out interval from the default of 2 minutes, create the BackupNamedPipeConnectTimeout registry key and add the following registry value:
HKEY_LOCAL_MACHINE
\SOFTWARE
\Microsoft
\MSSQLServer
Data Type: REG_DWORD
Data: Value other than the default of 2
Radix: Decimal
If you are using a network server with a UNC name or using a redirected drive letter, specify a device type of disk.
Important When specifying multiple files, logical file names (or variables) and temporary file names (or variables) can be mixed. However, all devices must be of the same type (disk, tape, or pipe).
For DISK, BACKUP automatically determines the appropriate block size for disk devices.
Note If you intend to transfer the resulting backup set to a CD-ROM and then restore from that CD-ROM, you must set BLOCKSIZE to 2048.
For tape media, BLOCKSIZE only applies if the tape is being overwritten by using FORMAT. When using tape media, the backup operation selects an appropriate block size. Explicitly stating a block size overrides the backup operation’s selection of a block size.
For PIPE, the backup operation uses 65,536 unless BLOCKSIZE is specified.
The DIFFERENTIAL option is specified for full database backups only.
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.
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 still be erased using other methods, and disk files can still be deleted through the operating system.
Important Use FORMAT carefully. Initializing one backup device or medium renders the entire backup set it contains unusable. For example, if a single tape belonging to an existing striped backup set is initialized, the entire backup set is rendered useless.
By specifying FORMAT, the backup operation implies SKIP and INIT; these do not need to be explicitly stated.
The backup media is not overwritten if either of the following conditions is met:
Use the SKIP clause 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 or encrypted, SQL Server does not write to the media. To overwrite media that is either password-protected or encrypted, specify the WITH FORMAT option.
Note If FORMAT is specified, MEDIANAME specifies the media name that is written. In addition, tapes shared between SQL Server database backups and Microsoft Windows NT® backups must have a nonnull MEDIANAME.
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.
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 Creating File or Filegroup Backups.
File and filegroup backups are disallowed if trunc. log on chkpt. is enabled for the database. If trunc. log on chkpt. is enabled, the log has been truncated and the log backups required to restore individual files and filegroups are not available.
The BACKUP statement requires that entire filegroups affected by a CREATE INDEX statement since the last filegroup backup be backed up as a unit:
The BACKUP statement detects all of these filegroup situations and communicates to the backup user the minimum filegroups that must be backed up.
Note If backing up the log does not appear to truncate most of the log, you may have a very old open transaction in the log. Log space can be monitored with DBCC SQLPERF (LOGSPACE).
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.
If the log of a published (replicated) database becomes full and absolutely must be truncated, unsubscribe all subscriptions to the publications of that database. This allows you to truncate all distributed transactions.
Database or log backups can be appended to any disk or tape device, allowing you to keep a database and its transaction logs all within one physical location.
SQL Server uses online backup to allow a database backup while the database is still in use. However, here are some operations that are not allowed during a database or transaction log backup:
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 backups, even between different processor types, can be performed, providing the following sort order and character set restrictions are met:
SQL Server backups can coexist on tape media with Windows NT backups because the SQL Server version 7.0 backup format conforms to Microsoft Tape Format (MTF), which is the same format used by Windows NT tape backups.
Here are the types of backups SQL Server supports:
Use BACKUP LOG when changes made since either the last differential database backup or last full database backup are important to maintain.
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 file backup 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 with FORMAT, the media header field corresponding to the blank item is empty.
Use BACKUP to back up database files and filegroups instead of the full database when time constraints make a full database backup impractical. If you choose to back up a file instead of the full database, put procedures in place to assure 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 contents of the file forward to make it consistent with the rest of the database.
SQL Server must be able to read and write the device. Therefore, ownership and permission problems on the backup device’s physical file can interfere with a backup operation. sp_addumpdevice, which adds an entry for a device in the system tables, does not check file access permission. In fact, the physical resource is not accessed until a backup or restore in attempted. Any backup sets with passwords will not be able to be accessed.
You cannot execute the BACKUP LOG statement while the trunc. log on chkpt. database option is enabled, nor can you execute it after enabling select into/bulkcopy and making unlogged changes to the database with SELECT INTO, UPDATETEXT, WRITETEXT, or a bulk-copy operation. Use BACKUP DATABASE instead.
When a RESTORE is performed, the backup history tables are modified.
Caution VOLUME is not an available option in SQL Server version 7.0. If VOLUME is specified, SQL Server returns an error message and halts the operation.
This table shows how the {INIT | NOINIT} and {NOSKIP | SKIP} clauses interact.
| INIT | NOINIT | |
|---|---|---|
| SKIP | If the volume contains a valid** media header, verify the media password* and overwrite any backup sets on the media, preserving only the media header. If the tape media is empty or the disk media is nonexistent, write a media header and proceed. If the media is not empty, however, give feedback that this is not valid MTF media and abort the backup. |
If the volume contains a valid media header, verify the media password* and append the backup set, preserving all existing backup sets. If the volume does not contain a valid media header, an error occurs. If the tape media is empty or the disk media is nonexistent, write a media header and proceed. If the media is not empty, however, give feedback that this is not valid MTF media and cancel the backup. |
| NOSKIP | If the volume contains a valid media header, perform the following checks: Verify the media password***. If MEDIANAME was specified, verify that the given media name matches the media header’s media name. Verify that there are no unexpired backup set(s) already on the media. If there are, abort the backup. If these checks pass, overwrite any backup sets on the media, preserving only the media header. If the tape media is empty or the disk media is nonexistent, write a media header and proceed. If the media is not empty, however, give feedback that this is not valid MTF media and cancel the backup. |
If the volume contains a valid media header, verify the media password* and verify that the media name matches the given MEDIANAME, if any. If it matches, append the backup set, preserving all existing backup sets. If the volume does not contain a valid media header, an error occurs. |
| * This capability is not supported by SQL Server version 7.0.
** Validity includes the MTF version number and other header information. If the version specified is unsupported or an unexpected value, an error occurs. |
||
| *** The presence of a media password prevents the backup operation. To override the backup operation, specify FORMAT. | ||
Important 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.
SQL Server includes these backup history tables that track backup activity :
BACKUP DATABASE and BACKUP LOG permissions default to members of the db_owner fixed database role, who can transfer permissions to other users, and to members of the db_backupoperator fixed database role.
Note The MyNwind database is shown for illustration only.
This example creates a backup device called MyNwind_1 that contains the full database backup of the MyNwind database.
-- Create the backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1', 'c:\mssql7\backup\MyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
This example creates both a full database and log backup. The database is backed up to a backup device called MyNwind_2, and then the log is backed up to a file called MyNwindLog1.
-- Create the backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_2', 'c:\mssql7\backup\MyNwind_2.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_2
-- Create the log backup device.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwindLog1',
'c:\mssql7\backup\MyNwindLog1.dat'
-- Update activity has occurred before this point.
-- Back up the log of the MyNwind database.
BACKUP LOG MyNwind
TO MyNwindLog1