Toolkit Syntax Examples

This topic provides examples of Transact-SQL statements for the SQL Backup command line and extended stored procedure -SQL parameter.

For example, if you are using the command line, use:

>SQLBackupC -SQL "Transact-SQL_statement"...

If you are using the extended stored procedure, use:

master..sqlbackup '-SQL "Transact-SQL_statement" ...'

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 arguments in the examples below, for the extended stored procedure you must use two single quotes 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

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

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

-SQL "BACKUP DATABASE pubs TO DISK = 'C:\Backups\pubs_01.sqb' "

B. Specify the compression level for a backup

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

-SQL "BACKUP DATABASE pubs TO DISK = 'C:\Backups\pubs_01.sqb' WITH COMPRESSION = 2"

C. Split the backup file

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

-SQL "BACKUP DATABASE pubs TO DISK = 'C:\Backups\pubs_01.sqb', DISK = 'C:\Backups\pubs_02.sqb' "

D. Create a mirrored backup

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

-SQL "BACKUP DATABASE pubs TO DISK = 'C:\Backups\pubs_01.sqb' WITH MIRRORFILE = 'E:\Backups\pubs_01_alt.sqb' "

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

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

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

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

G. Encrypt the backup file

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

-SQL "BACKUP DATABASE pubs TO DISK = 'C:\Backups\pubs_01.sqb' WITH PASSWORD = 'MyPassword' "

H. Back up multiple databases with exclusions

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

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

I. Back up multiple databases to split files

This example creates transaction log backups for databases northwind and pubs each into two files.

-SQL "BACKUP LOGS [northwind, pubs] TO DISK = 'C:\Backups\<AUTO>' WITH FILECOUNT = 2 "

J. Back up multiple databases using threads

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

-SQL "BACKUP LOGS [northwind, pubs] TO DISK = 'C:\Backups\<AUTO>' WITH THREADCOUNT = 2 "

K. Back up transaction logs for multiple databases

This example creates transaction log backups for databases northwind and pubs each into a single backup file.

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

L. Create a partial filegroup backup

This example backs up the database FileGroupTest 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.

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

M. Create a partial differential filegroup backup

This example creates a differential backup of the database FileGroupTest 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.

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

-SQL "RESTORE DATABASE pubs FROM DISK = 'C:\Backups\pubs_01.sqb' "

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

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

-SQL "RESTORE DATABASE pubs FROM DISK = 'C:\Backups\pubs_01.sqb', DISK = 'C:\Backups\pubs_02.sqb' "

C. Restore a database to a new name and move the backup 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.

-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 an encrypted backup file

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

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

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

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

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

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

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

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

CONVERT Command

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

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