The BACKUP command

Use the BACKUP command with the SQL Backup -SQL parameter to back up one or more databases, transaction logs, or filegroups.

• Syntax provides the syntax for the command; additional arguments and extended syntax are highlighted.
• Extended arguments describes the arguments that are provided by SQL Backup; for information about the standard arguments, refer to your SQL Server documentation.

Some example snippets are included in this topic. For more detailed examples, see Toolkit syntax 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.

Syntax

Note that the following arguments are available only with SQL Server 2005 and SQL Server 2008 instances:

CHECKSUM / NO_CHECKSUM
CONTINUE_AFTER_ERROR / STOP_ON_ERROR
COPY_ONLY

The READ_WRITE_FILEGROUPS keyword (used to create partial backups) is also available only with SQL Server 2005 and SQL Server 2008 instances.

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 | 4 } ]
    [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
    [ [ , ] COPY_ONLY ]
    [ [ , ] COPYTO = { 'target_folder_name' } [,...n] ]
    [ [ , ] DESCRIPTION = { 'text' } ]
    [ [ , ] DIFFERENTIAL ]
    [ [ , ] DISKRETRYCOUNT = { n } ]
    [ [ , ] DISKRETRYINTERVAL = { n } ]
    [ [ , ] ERASEFILES | ERASEFILES_ATSTART = { days | hours{h} | keep{b} } ]
    [ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
    [ [ , ] 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 | ... | 2097152 } ]
    [ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
    [ [ , ] MIRRORFILE = { 'physical_backup_device_name' } ] [ ,...n ]
    [ [ , ] NAME = { 'backup_set_name' } ]
    [ [ , ] NOCOMPRESSWRITE | NOWRITE ]
    [ [ , ] PASSWORD = { 'password' } ]
    [ [ , ] SINGLERESULTSET ]
    [ [ , ] THREADCOUNT = { n } ]
    [ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
    [ [ , ] USESIMPLECOPY ]
    [ [ , ] 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 | 4 } ]
    [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
    [ [ , ] COPY_ONLY ]
    [ [ , ] COPYTO = { 'target_folder_name' } [ ,...n ] ]
    [ [ , ] DESCRIPTION = { 'text' } ]
    [ [ , ] DIFFERENTIAL ]
    [ [ , ] DISKRETRYCOUNT = { n } ]
    [ [ , ] DISKRETRYINTERVAL = { n } ]
    [ [ , ] ERASEFILES | ERASEFILES_ATSTART = { days | hours{h} | keep{b} } ]
    [ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
    [ [ , ] 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 | ... | 2097152 } ]
    [ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
    [ [ , ] NAME = { 'backup_set_name' } ]
    [ [ , ] NOCOMPRESSWRITE | NOWRITE ]
    [ [ , ] PASSWORD = { 'password' } ]
    [ [ , ] SINGLERESULTSET ]
    [ [ , ] THREADCOUNT = { n } ]
    [ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
    [ [ , ] USESIMPLECOPY ]
    [ [ , ] VERIFY ]
]

Creating a partial filegroup backup

BACKUP DATABASE { database_name }
    READ_WRITE_FILEGROUPS [, <read_only_filegroup> [ ,...n ] ]
TO { DISK } = { 'physical_backup_device_name' | '<AUTO>' } [ ,...n ]
[ WITH
    [ [ , ] { CHECKSUM | NO_CHECKSUM } ]
    [ [ , ] COMPRESSION = { 0 | 1 | 2 | 3 | 4 } ]
    [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
    [ [ , ] COPY_ONLY ]
    [ [ , ] COPYTO = { 'target_folder_name' } [,...n] ]
    [ [ , ] DESCRIPTION = { 'text' } ]
    [ [ , ] DIFFERENTIAL ]
    [ [ , ] DISKRETRYCOUNT = { n } ]
    [ [ , ] DISKRETRYINTERVAL = { n } ]
    [ [ , ] ERASEFILES | ERASEFILES_ATSTART = { days | hours{h} | keep{b} } ]
    [ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
    [ [ , ] 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 | ... | 2097152 } ]
    [ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
    [ [ , ] MIRRORFILE = { 'physical_backup_device_name' } ] [ ,...n ]
    [ [ , ] NAME = { 'backup_set_name' } ]
    [ [ , ] NOCOMPRESSWRITE | NOWRITE ]
    [ [ , ] PASSWORD = { 'password' } ]
    [ [ , ] SINGLERESULTSET ]
    [ [ , ] THREADCOUNT = { n } ]
    [ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
    [ [ , ] USESIMPLECOPY ]
    [ [ , ] 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 | 4 } ]
    [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
    [ [ , ] COPY_ONLY ]
    [ [ , ] COPYTO = { 'target_folder_name' } [ ,...n ] ]
    [ [ , ] DESCRIPTION = { 'text' } ]
    [ [ , ] DISCONNECT_EXISTING ]
    [ [ , ] DISKRETRYCOUNT = { n } ]
    [ [ , ] DISKRETRYINTERVAL = { n } ]
    [ [ , ] ERASEFILES | ERASEFILES_ATSTART = { days | hours{h} | keep{b} } ]
    [ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
    [ [ , ] 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 | ... | 2097152 } ]
    [ [ , ] 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' } ]
    [ [ , ] SINGLERESULTSET ]
    [ [ , ] THREADCOUNT = { n } ]
    [ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
    [ [ , ] USESIMPLECOPY ]
    [ [ , ] 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 | 4 } ]
    [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
    [ [ , ] COPY_ONLY ]
    [ [ , ] COPYTO = { 'target_folder_name' } [ ,...n ] ]
    [ [ , ] DESCRIPTION = { 'text' } ]
    [ [ , ] DISCONNECT_EXISTING ]
    [ [ , ] DISKRETRYCOUNT = { n } ]
    [ [ , ] DISKRETRYINTERVAL = { n } ]
    [ [ , ] ERASEFILES | ERASEFILES_ATSTART = { days | hours{h} | keep{b} } ]
    [ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
    [ [ , ] 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 | ... | 2097152 } ]
    [ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
    [ [ , ] NAME = { 'backup_set_name' } ]
    [ [ , ] NOCOMPRESSWRITE | NOWRITE ]
    [ [ , ] NO_TRUNCATE ]
    [ [ , ] { NORECOVERY | STANDBY = 'undo_file_name' } ]
    [ [ , ] PASSWORD = { 'password' } ]
    [ [ , ] SINGLERESULTSET ]
    [ [ , ] THREADCOUNT = { n } ]
    [ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
    [ [ , ] USESIMPLECOPY ]
    [ [ , ] VERIFY ]
]

TO DISK argument

You can specify multiple DISK values, up to a maximum of 32. For example:

BACKUP DATABASE TO DISK = 'C:\Backups\File1.sqb', DISK = 'C:\Backups\File2.sqb'

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.

Extended arguments

COMPRESSION

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.

COPYTO

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.

If you are using SQL Backup Pro and want to prevent retries during copy operations, specify USESIMPLECOPY as well.

DATABASES

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

DISCONNECT_EXISTING

Kills any existing connections to the database before starting a transaction log backup. DISCONNECT_EXISTING is only valid when you are backing up the tail of the transaction log (with NORECOVERY or STANDBY).

DISKRETRYCOUNT

In combination with DISKRETRYINTERVAL, this argument controls network resilience behaviour.

DISKRETRYCOUNT specifies the maximum number of times to retry a failed data-transfer operation (writing or copying a backup file). If you omit this keyword, the default value of 10 is used; specify a value of 0 to prevent any retries following a failed data-transfer operation. If you specify a value for DISKRETRYCOUNT, you should also specify a value for DISKRETRYINTERVAL.

If you have also specified the COPYTO keyword, you can prevent retries for the copying operation only, by specifying USESIMPLECOPY.

DISKRETRYCOUNT is valid with SQL Backup Pro only.

DISKRETRYINTERVAL

In combination with DISKRETRYCOUNT, this argument controls network resilience behaviour.

DISKRETRYINTERVAL specifies the time interval between retries, in seconds, following a failed data-transfer operation (writing or copying a backup file). If you omit this keyword, the default value of 30 seconds is used. If you specify a value for DISKRETRYINTERVAL, you should also specify a value for DISKRETRYCOUNT.

If you have also specified the COPYTO keyword, you can prevent retries for the copying operation only, by specifying USESIMPLECOPY.

DISKRETRYINTERVAL is valid with SQL Backup Pro only.

ERASEFILES

Manages deletion of SQL Backup files from earlier backups in the primary backup folder (specified using DISK). The backup files are deleted only if the backup process completes successfully. To delete backup files prior to the start of the backup process, use ERASEFILES_ATSTART.

You can choose to delete SQL Backup files based on:

• Age: files older than the specified number of days or hours are deleted. Type a number for days, or type h after the number for hours. For example, ERASEFILES = 24 deletes files that are more than 24 days old; ERASEFILES = 24h deletes files that are more than 24 hours old.
• Number of files to keep: only the latest 'x' files will be kept. To specify the number of files to be kept, type b after the number. For example, ERASEFILES = 5b ensures the latest 5 files are kept; older files are deleted.

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 type (for example, full, or differential) must also match.

To prevent deletion of files that have their archive attribute set, use the FILEOPTIONS argument.

You can use ERASEFILES_REMOTE to manage deletion of files in the secondary backup folder (specified using COPYTO).

Note that if SQL Backup cannot list the contents of the folder that contains the files to be deleted, it cannot delete the files. Ensure that the user account from which you are running SQL Backup has permissions to list the folder contents.

ERASEFILES_ATSTART

Manages deletion of SQL Backup files from earlier backups in the primary backup folder (specified using DISK). The backup files are deleted before the backup process starts. To delete backup files only if the backup process completes successfully, use ERASEFILES.

You can choose to delete SQL Backup files based on:

• Age: files older than the specified number of days or hours are deleted. Type a number for days, or type h after the number for hours. For example, ERASEFILES = 24 deletes files that are more than 24 days old; ERASEFILES = 24h deletes files that are more than 24 hours old.
• Number of files to keep: only the latest 'x' files will be kept. To specify the number of files to be kept, type b after the number. For example, ERASEFILES = 5b ensures the latest 5 files are kept; older files are deleted.

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 type (for example, full, or differential) must also match.

To prevent deletion of files that have their archive attribute set, use the FILEOPTIONS argument.

You can use ERASEFILES_REMOTE to manage deletion of files in the secondary backup folder (specified using COPYTO).

Note that if SQL Backup cannot list the contents of the folder that contains the files to be deleted, it cannot delete the files. Ensure that the user account from which you are running SQL Backup has permissions to list the folder contents.

ERASEFILES_REMOTE

Manages deletion of remote SQL Backup files from earlier backups in the primary backup folder (specified using DISK) and secondary backup folder (specified using COPYTO). The backup files are deleted only if the backup process completes successfully.

You can choose to delete SQL Backup files based on:

• Age: files older than the specified number of days or hours are deleted. Type a number for days, or type h after the number for hours. For example, ERASEFILES = 24 deletes files that are more than 24 days old; ERASEFILES = 24h deletes files that are more than 24 hours old.
• Number of files to keep: only the latest 'x' files will be kept. To specify the number of files to be kept, type b after the number. For example, ERASEFILES = 5b ensures the latest 5 files are kept; older files are deleted.

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 type (for example, full, or differential) must also match.

To allow overwriting of existing backup files, use the FILEOPTIONS argument.

You can use ERASEFILES to manage deletion of files in the primary backup folder (specified using DISK).

Note that if SQL Backup cannot list the contents of the folder that contains the files to be deleted, it cannot delete the files. Ensure that the user account from which you are running SQL Backup has permissions to list the folder contents.

FILECOUNT

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_01.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 the 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:

• you cannot use TO DISK multiple times
• you cannot use the THREADCOUNT keyword to use multiple threads

FILEOPTIONS

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

INIT

Specifies that files with the same name in the primary backup folder should be overwritten.

Because SQL Backup supports a maximum of one backup set in a media set, specifying INIT also overwrites any media-set data (media header) associated with the existing backup files. This is equivalent to using the FORMAT argument in SQL Server.

KEYSIZE

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.

LOGS

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

LOGTO

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.

MAILTO

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.

MAXDATABLOCK

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

MAXTRANSFERSIZE

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)

MIRRORFILE

Indicates that you want to create a duplicate backup file (in addition to the 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.

NOCOMPRESSWRITE

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.

NOWRITE

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.

PASSWORD

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 that in standard Transact-SQL syntax, the PASSWORD argument attaches a password to the backup file, but does not encrypt the file contents.

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

SINGLERESULTSET

Specifies that the results returned by the BACKUP command should be limited to just one result set. This may be useful if you want to manipulate results using a Transact-SQL script. Such scripts can only manipulate results when a single result set is returned. The BACKUP command will return two result sets by default in most cases, unless you specify the SINGLERESULTSET keyword.

STANDBY

Backs up the tail of the transaction 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).

'undo_file_name'

Is a standby file, whose location is stored in the log of the database. This file holds the rolled back changes, which must be reversed if RESTORE LOG operations are to be subsequently applied.

When used with the BACKUP LOGS command (backing up multiple transaction logs) 'undo_file_name' must include the <DATABASE> tag.

When used with the BACKUP LOG command (backing up a single transaction log) 'undo_file_name' can include tags, but these are not required.

THREADCOUNT

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:

• you cannot use TO DISK multiple times
• you cannot use the FILECOUNT keyword to create multiple files

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

USESIMPLECOPY

Prevents retries occurring during copy operations (specified using the COPYTO keyword). With USESIMPLECOPY specified, copying behaviour is the same as in SQL Backup version 5.

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.

Deprecated extended arguments

The following extended arguments have been deprecated:

• THREADS (replaced by FILECOUNT)
• ERASEFILEOPTIONS (deprecated in version 4)
• FILEOPTIONS (value 1 only)

See also The RESTORE command Using the command line Toolkit syntax examples Toolkit parameters The Activity History Using the extended stored procedure Compression levels Creating backups File management options File location tags Email notification options Optimizing backup speed