The BACKUP Command |
See Also |
Use the BACKUP command with the SQL Backup -SQL parameter to back up one or more databases, transaction logs, or filegroups.
Some example snippets are included in this topic. For more detailed examples, see Toolkit Examples. Note that the extended stored procedure expects only one parameter, which must be delimited by single quotes. Therefore, wherever a single quote is used for the parameters below, for the extended stored procedure you must use two single quotes so that SQL Server does not interpret it as a string delimiter.
Note that the following arguments are only available with SQL Server 2005 instances:
CHECKSUM / NO_CHECKSUM
CONTINUE_AFTER_ERROR / STOP_ON_ERROR
COPY_ONLY
Backing up a database to a single file or split files
BACKUP DATABASE { database_name }
< file_or_filegroup > [ ,...n ]
TO { DISK } = { 'physical_backup_device_name' | '<AUTO>' } [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] COMPRESSION = { 0 | 1 | 2 | 3 } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] COPY_ONLY ]
[ [ , ] COPYTO = { 'target_folder_name' } [,...n] ]
[ [ , ] DESCRIPTION = { 'text' } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] ERASEFILES | ERASEFILES_ATSTART = { 'days' | 'hours'h } ]
[ [ , ] ERASEFILES_REMOTE = { 'days' | 'hours'h } ]
[ [ , ] FILECOUNT = { n } ]
[ [ , ] FILEOPTIONS = { 1 | 2 | 3 | 4 | 5 | 6 | 7 } ]
[ [ , ] INIT ]
[ [ , ] KEYSIZE = { 128 | 256 } ]
[ [ , ] LOGTO = { 'target_folder_name' | 'file_name' } ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MAXDATABLOCK = { 65536 | 131072 | ... | 4194304 } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] MIRRORFILE = { 'physical_backup_device_name' } ] [ ,...n ]
[ [ , ] NAME = { 'backup_set_name' } ]
[ [ , ] NOCOMPRESSWRITE | NOWRITE ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] THREADCOUNT = { n } ]
[ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
[ [ , ] VERIFY ]
]
Backing up multiple databases to single files or split files
BACKUP DATABASES [EXCLUDE] { '[ list_of_databases ]' }
TO { DISK } = { 'physical_backup_device_name' | '<AUTO>' } [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] COMPRESSION = { 0 | 1 | 2 | 3 } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] COPY_ONLY ]
[ [ , ] COPYTO = { 'target_folder_name' } [ ,...n ] ]
[ [ , ] DESCRIPTION = { 'text' } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] ERASEFILES | ERASEFILES_ATSTART = { 'days' | 'hours'h } ]
[ [ , ] ERASEFILES_REMOTE = { 'days' | 'hours'h } ]
[ [ , ] FILECOUNT = { n } ]
[ [ , ] FILEOPTIONS = { 1 | 2 | 3 | 4 | 5 | 6 | 7 } ]
[ [ , ] INIT ]
[ [ , ] KEYSIZE = { 128 | 256 } ]
[ [ , ] LOGTO = { 'target_folder_name' | 'file_name' } ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MAXDATABLOCK = { 65536 | 131072 | ... | 4194304 } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] NAME = { 'backup_set_name' } ]
[ [ , ] NOCOMPRESSWRITE | NOWRITE ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] THREADCOUNT = { n } ]
[ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
[ [ , ] VERIFY ]
]
Backing up a transaction log to a single file or split files
BACKUP LOG { database_name }
TO { DISK } = { 'physical_backup_device_name' | '<AUTO>' } [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] COMPRESSION = { 0 | 1 | 2 | 3 } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] COPY_ONLY ]
[ [ , ] COPYTO = { 'target_folder_name' } [ ,...n ] ]
[ [ , ] DESCRIPTION = { 'text' } ]
[ [ , ] ERASEFILES | ERASEFILES_ATSTART = { 'days' | 'hours'h } ]
[ [ , ] ERASEFILES_REMOTE = { 'days' | 'hours'h } ]
[ [ , ] FILECOUNT = { n } ]
[ [ , ] FILEOPTIONS = { 1 | 2 | 3 | 4 | 5 | 6 | 7 } ]
[ [ , ] INIT ]
[ [ , ] KEYSIZE = { 128 | 256 } ]
[ [ , ] LOGTO = { 'target_folder_name' | 'file_name' } ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MAXDATABLOCK = { 65536 | 131072 | ... | 4194304 } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] MIRRORFILE = {'physical_backup_device_name' } ] [ ,...n ]
[ [ , ] NAME = { 'backup_set_name' } ]
[ [ , ] NOCOMPRESSWRITE | NOWRITE ]
[ [ , ] NO_TRUNCATE ]
[ [ , ] { NORECOVERY | STANDBY = 'undo_file_name' } ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] THREADCOUNT = { n } ]
[ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
[ [ , ] VERIFY ]
]
Backing up multiple transaction logs to single files or split files
BACKUP LOGS [EXCLUDE] { '[ list_of_databases ]' }
TO { DISK } = { 'physical_backup_device_name' | '<AUTO>' } [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] COMPRESSION = { 0 | 1 | 2 | 3 } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] COPY_ONLY ]
[ [ , ] COPYTO = { 'target_folder_name' } [ ,...n ] ]
[ [ , ] DESCRIPTION = { 'text' } ]
[ [ , ] ERASEFILES | ERASEFILES_ATSTART = { 'days' | 'hours'h } ]
[ [ , ] ERASEFILES_REMOTE = { 'days' | 'hours'h } ]
[ [ , ] FILECOUNT = { n } ]
[ [ , ] FILEOPTIONS = { 1 | 2 | 3 | 4 | 5 | 6 | 7 } ]
[ [ , ] INIT ]
[ [ , ] KEYSIZE = { 128 | 256 } ]
[ [ , ] LOGTO = { 'target_folder_name' | 'file_name' } ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MAXDATABLOCK = { 65536 | 131072 | ... | 4194304 } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] NAME = { 'backup_set_name' } ]
[ [ , ] NOCOMPRESSWRITE | NOWRITE ]
[ [ , ] NO_TRUNCATE ]
[ [ , ] { NORECOVERY | STANDBY = 'undo_file_name' } ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] THREADCOUNT = { n } ]
[ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
[ [ , ] VERIFY ]
]
TO DISK = '<AUTO>'
If you specify <AUTO>, SQL Backup generates the backup file path and file name using the File Management options specified in the graphical user interface (GUI). If no backup location options have been set up, SQL Backup uses the SQL Server instance's default backup folder, and the default format for file names. For details, see File Management Options.
TO DISK = 'path\<AUTO>'
If you specify a path and <AUTO>, SQL Backup uses the specified path, and generates the file name using the Backup Location options. If no backup location options have been set up, SQL Backup uses the default format for file names.
You can use tags in the path. For example, if you are backing up multiple databases and you want the backup files for each database to be created in a separate folder, you can specify the <DATABASE> tag. The following example creates the backups in a different folder for each database name:
BACKUP DATABASES TO DISK = 'C:\Backups\<DATABASE>\<AUTO>'
You can specify more than one tag in the path. In the following example, backups are grouped by database name, date, and type of backup:
BACKUP DATABASES TO DISK = 'C:\Backups\<DATABASE>\<DATETIME yyyymmdd>\<TYPE>\<AUTO>'
For details of the tags you can use, see File Location Tags.
TO DISK = 'file_name'
If you specify a file name with the TO DISK argument, SQL Backup uses the Backup Location options to generate the backup file path, and uses the specified name for the file. If no backup settings have been set up, SQL Backup uses the SQL Server instance's default backup folder.
TO DISK = '<AUTO>.001'
If you specify <AUTO> with a file extension, SQL Backup uses the Backup Location options to generate the backup file path and file name. If no backup settings have been set up, SQL Backup uses the SQL Server instance's default backup folder, and the default format for file names. This is useful when you generate split backup files.
Specifies the compression level. The default value is 1. If you do not want to compress the backups, specify 0. For more information, see Compression Levels.
Note that if SQL Backup Lite is installed on the SQL Server, you can specify only compression level 0 (no compression) or compression level 1.
Specifies that a copy of the backup files is to be created in the specified folder when the backup process completes. For example:
COPYTO = '\\BACKUPSERVER001\Folder1'
You can use tags in the path. To create a copy of the backup in more than one folder, use multiple COPYTO arguments. For example:
COPYTO = '\\BACKUPSERVER001\Folder1', COPYTO = '\\BACKUPSERVER001\Folder2'
You must ensure that you have sufficient rights to the specified folders.
Specifies multiple full or differential database backups.
'[ list_of_databases ] '
Is a comma-separated list of the names of the databases that are to be backed up (up to a maximum of 32). For example:
BACKUP DATABASES [ Database1, Database2, Database3 ]
The list must be enclosed in square brackets [ ], and the list must not contain any other square brackets. A single wildcard character can be used. For example:
BACKUP DATABASES [*]
This backs up all databases that are currently online and operational, including read-only databases; databases that are unrecovered or unavailable (for example, offline) are not backed up.
EXCLUDE
Specifies that all online databases except those listed are to be backed up. For example:
BACKUP DATABASES EXCLUDE [ Database1, Database2, Database3 ]
This backs up all databases that are currently online and operational, except for Database1, Database2, and Database3. You cannot use wildcard characters in exclusion lists.
TO { DISK } =
{ 'physical_backup_device_name' | '<AUTO>' } [,...n]
Creates the backups on the specified disk. You are recommended to include the <AUTO> parameter to ensure that each database is backed up to a unique file name. For details, see TO DISK argument. For example:
TO DISK = 'C:\Backups\<AUTO>'
Alternatively, you could set up your own unique path using tags (for example, you could use the <DATETIME> tag).
Deletes all SQL Backup files for the database that are older than the specified number of days (or hours), and are located in the backup folder. The files are deleted only if the name of the SQL Server, instance (if applicable), and database recorded in the file header match the details of the database that is being backed up.
The backup files are deleted only if the backup process completes successfully.
To specify the age of files to be deleted in hours, type h after the number. For example, ERASEFILES = '24' deletes files that are more than 24 days old; ERASEFILES = '24h' deletes files that are more than 24 hours old.
You can use FILEOPTIONS to specify that files will also be deleted in the COPYTO folder.
To delete backup files prior to the start of the backup process, use ERASEFILES_ATSTART.
Deletes all SQL Backup files for the database that are older than the specified number of days (or hours) and are located in the backup folder, before the backup process starts. For example, you may want to do this to create space for the new backup files. However, note that if the backup fails, the old files will have been deleted; you are therefore recommended to use this option only if you have a copy of the existing backups.
To specify the age of files to be deleted in hours, type h after the number. For example, ERASEFILES_ATSTART = '24' deletes files that are more than 24 days old; ERASEFILES_ATSTART = '24h' deletes files that are more than 24 hours old.
You can use FILEOPTIONS to specify that files will also be deleted in the COPYTO folder.
To delete backup files only if the backup process completes successfully, use ERASEFILES.
Deletes all SQL Backup files for the database that are older than the specified number of days (or hours), and are located on a network share. You use this argument if you want to specify different deletion intervals for local files and files located on network shares. If not specified, SQL Backup uses the ERASEFILES value.
The backup files are deleted only if the backup process completes successfully.
To specify the age of files to be deleted in hours, type h after the number. For example, ERASEFILES_REMOTE = '24' deletes files that are more than 24 days old; ERASEFILES_REMOTE = '24h' deletes files that are more than 24 hours old.
You can use FILEOPTIONS to specify that files will also be deleted in the COPYTO folder.
Specifies the number of backup files to be generated if you are splitting the backup across a number of files, where n is an integer between 2 and 32 inclusive. The name of the file specified for TO DISK is used as the base name for the generated files. For example:
BACKUP DATABASE Pubs TO DISK = 'C:\Backups\Pubs.sqb' WITH FILECOUNT = 4
This example generates four backup files:
C:\Backups\Pubs.sqb
C:\Backups\Pubs_02.sqb
C:\Backups\Pubs_03.sqb
C:\Backups\Pubs_04.sqb
Can be used together with the <AUTO> keyword (see TO DISK argument). For example:
BACKUP DATABASE Pubs TO DISK = '<AUTO>' WITH FILECOUNT = 4
In this example, SQL Backup generates the additional files using the <AUTO> naming convention, appending _02, _03, _04 and so on to the root name.
Note that if you are using the FILECOUNT keyword to create multiple files:
Specifies whether old backup files are to be deleted or overwritten in the primary backup folder and any COPYTO folders. Specify the sum of the values that correspond to the options you require:
| 1 | Delete old backup files in the secondary backup folders (specified using COPYTO) if they are older than the number of days or hours specified in ERASEFILES, ERASEFILES_ATSTART, or ERASEFILES_REMOTE. |
| 2 | Delete old backup files in the primary backup folder (specified using DISK) if they are older than the number of days or hours specified in ERASEFILES, ERASEFILES_ATSTART, or ERASEFILES_REMOTE unless they have the ARCHIVE flag set. |
| 4 | Overwrite existing files in the COPYTO folder. |
Valid values are 1 to 7.
If you specify option 1 or 2 you must also set the age of the files to delete using ERASEFILES, ERASEFILES_ATSTART, or ERASEFILES_REMOTE. For example, to delete old backup files in the COPYTO folder that are older than 5 days:
BACKUP DATABASE... WITH COPYTO = ... , ERASEFILES = 5, FILEOPTIONS = 1
To overwrite any existing files in the COPYTO folder and also delete old backup files in the COPYTO folder that are older than 5 days (values 1 + 4):
BACKUP DATABASE... WITH COPYTO = ... , ERASEFILES = 5, FILEOPTIONS = 5
Specifies the size of the encryption key to use; you must also use the PASSWORD keyword to specify a password for the encrypted backups. If SQL Backup Pro is installed on the SQL Server, you can specify a 128-bit key or a 256-bit key; if SQL Backup Lite is installed on the SQL Server, you cannot encrypt the backup.
If you do not specify the key size, SQL Backup Pro uses a 256-bit key.
Note that you can restore 128-bit key and 256-bit key encrypted backups using SQL Backup Pro or SQL Backup Lite.
Specifies multiple transaction log backups.
'[ list_of_databases ] '
Is a comma-separated list of the names of the databases for which the transaction logs are to be backed up (up to a maximum of 32). The databases must use the FULL or BULK-LOGGED recovery model. For example:
BACKUP LOGS [ Database1, Database2, Database3 ]
The list must be enclosed in square brackets [ ], and the list must not contain any other square brackets. A single wildcard character can be used. For example:
BACKUP LOGS [*]
This backs up transaction logs for all databases that are currently online, operational, and using the FULL or BULK-LOGGED recovery models.
EXCLUDE
Specifies that transaction logs of all online databases except those listed are to be backed up. For example:
BACKUP LOGS EXCLUDE [ Database1, Database2, Database3 ]
This backs up the transaction logs of all databases that are currently online and operational, except for Database1, Database2, and Database3.
TO { DISK } =
{ 'physical_backup_device_name' | '<AUTO>' } [,...n]
Creates the backups on the specified disk. Must include the <AUTO> parameter to ensure that each transaction log is backed up to a unique file name. For details, see TO DISK argument. For example:
TO DISK = 'C:\Backups\<AUTO>'
Specifies that a copy of the log file is to be saved.
By default, the primary log file is created in the folder C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log; you can change this location in your log file options. To create a copy with the same name as the primary log file, specify the folder. For example:
LOGTO = 'C:\Logs'
To create a copy with a different name from the primary log file, specify the folder and file name. For example:
LOGTO = 'C:\Logs\SQBSecondaryLog.txt'
To copy the log file to more than one location, use multiple LOGTO commands.
Specifies that a copy of the log file is to be sent to one or more users by email. SQL Backup uses the settings specified in your email options to send the email. To specify multiple recipients, separate the email addresses with a semi-colon (;). For example:
MAILTO = 'dba01@myco.com;dba02@myco.com'
If you have not defined email settings, the email will not be sent and a warning will be reported.
Note that if SQL Backup Lite is installed on the SQL Server, you cannot use email notification.
MAILTO_ONERROR
Specifies that a copy of the log file is to be sent to one or more users by email if SQL Backup encounters an error during the backup process or the backup completes successfully but with warnings. SQL Backup uses the settings specified in your email options to send the email. To specify multiple recipients, separate the email addresses with a semi-colon (;). For example:
MAILTO_ONERROR = 'dba01@myco.com;dba02@myco.com'
If you have not defined email settings, the email will not be sent and a warning will be reported.
Note that if SQL Backup Lite is installed on the SQL Server, you cannot use email notification.
MAILTO_ONERRORONLY
Specifies that a copy of the log file is to be sent to one or more users by email if SQL Backup encounters an error during the backup process. SQL Backup uses the settings specified in your email options to send the email. To specify multiple recipients, separate the email addresses with a semi-colon (;). For example:
MAILTO_ONERRORONLY = 'dba01@myco.com;dba02@myco.com'
If you have not defined email settings, the email will not be sent and a warning will be reported.
Note that if SQL Backup Lite is installed on the SQL Server, you cannot use email notification.
Specifies the maximum size of data blocks to be used when SQL Backup stores backup data. Valid values are integers in multiples of 65536, up to a maximum value of 4194304. For example:
MAXDATABLOCK = 655360
If not specified, SQL Backup uses the value defined in the following DWORD registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\MAXDATABLOCK (32-bit)
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\MAXDATABLOCK (64-bit)
Specifies the maximum size of each block of memory to be used when SQL Backup stores backup data. You may want to specify this argument if a SQL Server reports that it has insufficient memory to service requests from SQL Backup.
Valid values are integers in multiples of 65536, up to a maximum value of 1048576.
For example:
MAXTRANSFERSIZE = 262144
If not specified, defaults to 1048576. However, if you have created the following DWORD registry key, SQL Backup uses the defined value as the default value:
HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\MAXTRANSFERSIZE (32-bit)
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\MAXTRANSFERSIZE (64-bit)
Indicates that you want to create a duplicate backup file (in addition to the primary backup file named in the TO DISK argument). Specify the value as described for the TO DISK argument.
You cannot use MIRRORFILE if you are backing up multiple databases, if you specify multiple DISK options, or if you use the FILECOUNT option.
To create more than one duplicate backup file, use multiple MIRRORFILE arguments (up to a maximum of 32 files).
Before the backup process starts, the locations specified in the MIRRORFILE arguments are verified; if a location does not exist, or the file cannot be created at that location, then the backup will not start.
During the backup process, if any of the files cannot be written a warning is raised. However, the backup process continues as long as at least one specified backup file can be written. If none of the files can be written, an error is raised and the backup process is stopped.
Determines the maximum backup process throughput of your SQL Server. When you specify this argument, SQL Backup simulates a backup process without compression; no backup files are created.
You can compare the results obtained using NOCOMPRESSWRITE and NOWRITE to deduce the effects of compression on the backup throughput. For details, see Optimizing Backup Speed.
Determines the maximum backup process throughput of your SQL Server using compression. When you specify this argument, SQL Backup simulates a backup process using the specified compression level; no backup files are created.
You can compare the results obtained using NOCOMPRESSWRITE and NOWRITE to deduce the effects of compression on the backup throughput. For details, see Optimizing Backup Speed.
Specifies the password to be used with encrypted backup files. You must supply the same password when you restore the backup.
You can specify the size of the encryption key using the KEYSIZE keyword.
Note In standard Transact-SQL syntax, the PASSWORD argument attaches a password to the backup file, but does not encrypt the file contents.
Note If you subsequently convert the SQL Backup file to a Microsoft Tape Format (MTF) file, you must supply the password; the MTF file will not be protected by the password.
Specifies the number of threads to be used to create the backup, where n is an integer between 2 and 32 inclusive. For example:
BACKUP DATABASE Pubs TO DISK = 'C:\Backups\Pubs.sqb' WITH THREADCOUNT = 4
This example creates a single backup files using four threads.
Note that if you are using the THREADCOUNT keyword to use multiple threads:
THREADPRIORITY
Sets the SQL Backup thread priority when the backup or restore process is run. Valid values are 0 to 6, and correspond to the following priorities:
0 |
Idle |
1 |
Very Low |
2 |
Low |
3 |
Normal |
4 |
High |
5 |
Very High |
6 |
Time Critical |
VERIFY
Defines whether the backup files are verified when the backup process has completed. If specified, runs a procedure to check the backup file when the backup has completed, to ensure that the file is not corrupted and can be read by SQL Backup for restoring. The results are displayed as text output when the backup completes. You can also check the results later by viewing the Activity History in the graphical user interface.
The following extended arguments have been deprecated:
| See Also |
File Location Tags | Using the Command Line | Using the Extended Stored Procedure | Toolkit Parameters | The RESTORE Command | File Management Options | Toolkit Syntax Examples | Creating Backups | Email Notification Options | Compression Levels | Optimizing Backup Speed | The Activity History
© Red Gate Software Ltd 2008. All Rights Reserved.