Syntax examples - SQL Backup

This topic provides examples of Transact-SQL statements for the SQL Backup command line and extended stored procedure -SQL parameter. The command line version (SQLBackupC.exe) is given first, followed by the extended store procedure (EXECUTE master..sqlbackup).

The command line examples specify the instance parameter and the SQL parameter. You may also wish to specify the user name and password parameters for connecting to the SQL Server. See Toolkit parameters for more information.

Note that the extended stored procedure expects only one parameter (normally -SQL), which must be delimited by single quotes. Therefore, wherever a single quote is used for the arguments in the command line examples, two single quotes are used for the extended stored procedure so that SQL Server does not interpret it as a string delimiter.

For more information, see Using the command line and Using the extended stored procedure.

Examples are provided for the following commands:

• BACKUP command
• RESTORE command
• RESTORE SQBHEADERONLY command
• CONVERT command
• ERASE command

For full details of the standard commands you can use, and the extensions to the standard Transact-SQL syntax, see the topics in the Scripting SQL Backup section of the Help.

BACKUP command

A. Back up a database to a single file

This example creates a full backup of the pubs database in a single file.

SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASE [pubs] TO DISK = 'C:\Backups\pubs_01.sqb' "

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [pubs] TO DISK = ''C:\Backups\pubs_01.sqb'' " '

B. Create a mirrored backup

This example simultaneously creates a full backup of the pubs database and two duplicate backup files.

SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASE [pubs] TO DISK = 'C:\Backups\pubs_01.sqb' WITH MIRRORFILE = 'E:\Backups\pubs_01_alt1.sqb' MIRRORFILE = 'F:\Backups\pubs_01_alt2.sqb' "

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [pubs] TO DISK = ''C:\Backups\pubs_01.sqb'' WITH MIRRORFILE = ''E:\Backups\pubs_01_alt1.sqb'' MIRRORFILE = ''F:\Backups\pubs_01_alt2.sqb'' " '

C. Split the backup file

This example creates a full backup of the pubs database and splits the backup across two files.

SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASE [pubs] TO DISK = 'C:\Backups\pubs_01.sqb', DISK = 'C:\Backups\pubs_02.sqb' "

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [pubs] TO DISK = ''C:\Backups\pubs_01.sqb'', DISK = ''C:\Backups\pubs_02.sqb'' " '

D. Create copies of the backup file

This example creates a full backup of the pubs database and then copies the backup file to two remote folders on completion of the backup process.

SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASE [pubs] TO DISK = 'C:\Backups\pubs_01.sqb' WITH COPYTO = '\\BACKUPSERVER001\share', COPYTO = '\\BACKUPSERVER002\share' "

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [pubs] TO DISK = ''C:\Backups\pubs_01.sqb'' WITH COPYTO = ''\\BACKUPSERVER001\share'', COPYTO = ''\\BACKUPSERVER002\share'' " '

E. Specify the compression level for a backup

This example creates a full backup of the pubs database using compression level 4.

SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASE [pubs] TO DISK = 'C:\Backups\pubs_01.sqb' WITH COMPRESSION = 4"

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [pubs] TO DISK = ''C:\Backups\pubs_01.sqb'' WITH COMPRESSION = 4" '

F. Encrypt the backup file

This example creates a full backup of the pubs database and encrypts the backup file with password MyPassword and a 256-bit key.

SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASE [pubs] TO DISK = 'C:\Backups\pubs_01.sqb' WITH PASSWORD = 'MyPassword', KEYSIZE = 256"

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [pubs] TO DISK = ''C:\Backups\pubs_01.sqb'' WITH PASSWORD = ''MyPassword'', KEYSIZE = 256" '

G. Check the backup file

This example creates a full backup of the pubs database, testing any page checksums in the process. A backup checksum is generated and tested, and the backup is checked to ensure it is complete and readable.

SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASE [pubs] TO DISK = 'C:\Backups\pubs_01.sqb' WITH CHECKSUM, VERIFY"

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [pubs] TO DISK = ''C:\Backups\pubs_01.sqb'' WITH CHECKSUM, VERIFY" '

H. Send email notification of the backup process

This example creates a full backup of the pubs database and sends an email of the completion log to two users upon completion of the backup process.

SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASE [pubs] TO DISK = 'C:\Backups\pubs_01.sqb' WITH MAILTO = 'dba01@myco.com;dba02@myco.com' "

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [pubs] TO DISK = ''C:\Backups\pubs_01.sqb'' WITH MAILTO = ''dba01@myco.com;dba02@myco.com'' " '

I. Back up multiple databases with exclusions

This example creates a full backup of all the databases except master, msdb, and model (all user databases).

-SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASES EXCLUDE [master, msdb, model] TO DISK = 'C:\Backups\<AUTO>' "

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES EXCLUDE [master, msdb, model] TO DISK = ''C:\Backups\<AUTO>'' " '

J. Back up multiple databases to split files

This example creates full backups of the northwind and pubs databases, splitting each into two files.

SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASES [northwind, pubs] TO DISK = 'C:\Backups\<AUTO>' WITH FILECOUNT = 2"

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [northwind, pubs] TO DISK = ''C:\Backups\<AUTO>'' WITH FILECOUNT = 2" '

K. Back up multiple databases to split files on different disks

This example creates full backups of the northwind and pubs databases, splitting both backups to separate files on different disks.

SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASES [northwind, pubs] TO DISK = 'C:\Backups\<AUTO>_01', TO DISK = 'D:\Backups\<AUTO>_02' "

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [northwind, pubs] TO DISK = ''C:\Backups\<AUTO>_01'', TO DISK = ''D:\Backups\<AUTO>_02'' " '

L. Back up transaction logs for multiple databases

This example creates transaction log backups for databases northwind and pubs in the default location.

SQLBackupC.exe -I {instance name} -SQL "BACKUP LOGS [northwind, pubs] TO DISK = '<AUTO>' "

EXECUTE master..sqlbackup '-SQL "BACKUP LOGS [northwind, pubs] TO DISK = ''<AUTO>'' " '

M. Create differential backups for multiple databases using threads

This example creates differential backups for databases northwind and pubs using two threads.

SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASES [northwind, pubs] TO DISK = 'C:\Backups\<AUTO>' WITH THREADCOUNT = 2, DIFFERENTIAL "

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [northwind, pubs] TO DISK = ''C:\Backups\<AUTO>'' WITH THREADCOUNT = 2, DIFFERENTIAL " '

N. Create a partial filegroup backup

This example backs up the read/write files in the FileGroupTest database using the standard Transact-SQL argument READ_WRITE_FILEGROUPS. For detailed information about this argument, refer to your SQL Server documentation about the BACKUP command.

SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASE [FileGroupTest] READ_WRITE_FILEGROUPS TO DISK = 'C:\Backups\<AUTO>' "

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [FileGroupTest] READ_WRITE_FILEGROUPS TO DISK = ''C:\Backups\<AUTO>'' " '

O. Create a partial differential filegroup backup

This example creates a differential backup of the read/write files in the FileGroupTest database using the standard Transact-SQL argument READ_WRITE_FILEGROUPS. For detailed information about this argument, refer to your SQL Server documentation about the BACKUP command.

SQLBackupC.exe -I {instance name} -SQL "BACKUP DATABASE [FileGroupTest] READ_WRITE_FILEGROUPS TO DISK = 'C:\Backups\<AUTO>' WITH DIFFERENTIAL"

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [FileGroupTest] READ_WRITE_FILEGROUPS TO DISK = ''C:\Backups\<AUTO>'' WITH DIFFERENTIAL" '

RESTORE command

A. Restore a database from a single file

This example restores a full backup of the pubs database from a single file.

SQLBackupC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DISK = 'C:\Backups\pubs_01.sqb' WITH REPLACE"

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK = ''C:\Backups\pubs_01.sqb'' WITH REPLACE" '

B. Restore a database from multiple (split) backup files

This example restores a full backup of the pubs database from two files.

SQLBackupC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DISK = 'C:\Backups\pubs_01.sqb', DISK = 'C:\Backups\pubs_02.sqb' WITH REPLACE"

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK = ''C:\Backups\pubs_01.sqb'', DISK = ''C:\Backups\pubs_02.sqb'' WITH REPLACE" '

C. Restore a database to a new name and move the database files

This example restores a full backup of the pubs database and restores it to a new database called pubs02. It also renames the database data and log files and moves them to a new location.

SQLBackupC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs02] FROM DISK = 'C:\Backups\pubs_01.sqb' WITH MOVE 'pubs' TO 'E:\Data\pubs02.mdf', MOVE 'pubs_log' TO 'E:\Data\pubs02.ldf' "

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [pubs02] FROM DISK = ''C:\Backups\pubs_01.sqb'' WITH MOVE ''pubs'' TO ''E:\Data\pubs02.mdf'', MOVE ''pubs_log'' TO ''E:\Data\pubs02.ldf'' " '

D. Restore a database from the latest full backup and move the database files

This example restores the most recent full backup of the pubs database that matches the file name pattern and moves the database files to different locations. The specified locations must already exist.

SQLBackupC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DISK = 'C:\Backups\pubs*.sqb' LATEST_FULL WITH MOVE DATAFILES TO 'E:\Data', MOVE LOGFILES TO 'E:\Logs', REPLACE"

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK = ''C:\Backups\pubs*.sqb'' LATEST_FULL WITH MOVE DATAFILES TO ''E:\Data'', MOVE LOGFILES TO ''E:\Logs'', REPLACE" '

E. Restore a database from the latest full backup on different disks

This example searches multiple disk locations for full backups of the pubs database that match the file name pattern and restores the latest full backup.

SQLBackupC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DISK = 'C:\Backups\*pubs*.sqb', DISK = 'D:\Backups\*pubs*.sqb', DISK = 'E:\Backups\*pubs*.sqb' LATEST_FULL WITH REPLACE"

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK = ''C:\Backups\*pubs*.sqb'', DISK = ''D:\Backups\*pubs*.sqb'', DISK = ''E:\Backups\*pubs*.sqb'' LATEST_FULL WITH REPLACE" '

F. Restore a database from the latest backup set on different disks

This example searches multiple disk locations for full, differential and transaction log backups of the pubs database that match the file name pattern, and restores the most recent full backup, followed by the most recent differential backup and the most recent transaction log backups.

SQLBackupC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DISK = 'C:\Backups\*pubs*.sqb', DISK = 'D:\Backups\*pubs*.sqb', DISK = 'E:\Backups\*pubs*.sqb' LATEST_ALL WITH REPLACE"

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK = ''C:\Backups\*pubs*.sqb'', FROM DISK = ''D:\Backups\*pubs*.sqb'', DISK = ''E:\Backups\*pubs*.sqb'' LATEST_ALL WITH REPLACE" '

G. Restore to a new database from the latest backup set and check for orphaned users

This example restores the most recent full backup of the pubs database, followed by the most recent differential backup and the most recent transaction log backups available from C:\Backups to a new database called pubs02 and checks for orphaned users.

SQLBackupC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs02] FROM DISK = 'C:\Backups\pubs*.sqb' SOURCE = 'pubs' LATEST_ALL WITH ORPHAN_CHECK"

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [pubs02] FROM DISK = ''C:\Backups\pubs*.sqb'' SOURCE = ''pubs'' LATEST_ALL WITH ORPHAN_CHECK" '

H. Restore a database from an encrypted backup file

This example restores an encrypted backup of the pubs database, specifying the password MyPassword.

SQLBackupC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DISK = 'C:\Backups\pubs_01.sqb' WITH PASSWORD = 'MyPassword' "

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK = ''C:\Backups\pubs_01.sqb'' WITH PASSWORD = ''MyPassword'' " '

I. Restore a database in NORECOVERY mode

This example restores a full backup of the pubs database, specifying that the database is to be left in an unrecovered state so that differential and transaction log backups can be restored to it.

SQLBackupC.exe -I {instance name} -SQL "RESTORE DATABASE pubs FROM DISK = 'C:\Backups\pubs_01.sqb' WITH NORECOVERY"

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE pubs FROM DISK = ''C:\Backups\pubs_01.sqb'' WITH NORECOVERY" '

J. Restore a database in READ-ONLY mode

This example restores a full backup of the pubs database, specifying that the database should be left in an unrecovered, read-only state so that its data can be viewed and differential and transaction log backups can be restored to it. The location of the standby file is specified.

SQLBackupC.exe -I {instance name} -SQL "RESTORE DATABASE pubs FROM DISK = 'C:\Backups\pubs_01.sqb' WITH STANDBY = 'C:\Standby\pubs_log.DAT' "

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE pubs FROM DISK = ''C:\Backups\pubs_01.sqb'' WITH STANDBY = ''C:\Standby\pubs_log.DAT'' " '

RESTORE SQBHEADERONLY command

A. Restore a header file

This example retrieves the header information for the pubs.sqb database backup file.

SQLBackupC.exe -I {instance name} -SQL "RESTORE SQBHEADERONLY FROM DISK = 'C:\Backups\pubs.sqb' "

EXECUTE master..sqlbackup '-SQL "RESTORE SQBHEADERONLY FROM DISK = ''C:\Backups\pubs.sqb'' " '

B. Restore multiple header files

This example retrieves the header information for all database backup files in the Backups folder.

SQLBackupC.exe -I {instance name} -SQL "RESTORE SQBHEADERONLY FROM DISK = 'C:\Backups\*.sqb' "

EXECUTE master..sqlbackup '-SQL "RESTORE SQBHEADERONLY FROM DISK = ''C:\Backups\*.sqb'' " '

CONVERT command

A. Convert a SQL Backup file to a Microsoft Tape Format file

SQLBackupC.exe -I {instance name} -SQL "CONVERT 'C:\Backups\pubs.sqb' TO 'C:\Backups\pubs.bak' "

EXECUTE master..sqlbackup '-SQL "CONVERT ''C:\Backups\pubs.sqb'' TO ''C:\Backups\pubs.bak'' " '

ERASE command

A. Delete full backups of the 'pubs' database in 'C:\Backups' that are more than 12 hours old

SQLBackupC.exe -I {instance name} -SQL "ERASE FULL_BACKUPS FOR pubs FROM DISK = 'C:\Backups' KEEP = 12h WITH PASSWORD = '8hhsj2&@' "

EXECUTE master..sqlbackup '-SQL "ERASE FULL_BACKUPS FOR pubs FROM DISK = ''C:\Backups'' KEEP = 12h WITH PASSWORD = ''8hhsj2&@'' " '