Scripting SQL Backup Pro
Published 13 February 2013
SQL Backup Pro provides a command line interface and extended stored procedure so that you can run backups and restores using scripts:
- The command line executable SQLBackupC.exe allows you to run your backup and restore operations in script and batch files.
- The extended stored procedure, sqlbackup, allows you to run your backup and restore operations using an application such as Microsoft SQL Server Management Studio, or a database connectivity layer such as ADO or OLE DB.
Before you use the command line or extended stored procedure, you are recommended to use the backup and restore wizards until you are familiar with the SQL Backup Pro features and tools. On the final step of each wizard, you can view the backup or restore script in either command line or extended stored procedure format:
Using the command line
The SQL Backup Pro command line executable is SQLBackupC.exe. It is located in the folder in which you installed the SQL Backup Pro server components; for example, C:\Program Files\Red Gate\SQL Backup\<instance name>. For details, see Installing the server components on a SQL Server instance.
When running SQL Backup Pro from the command line, you can use both connection parameters and process parameters. Use a command of the format:
SQLBackupC.exe <parameters>
The following connection and process parameters are available:
| Parameter | Syntax | Description |
|---|---|---|
| Connection Parameters | ||
-U | -U <SQL_Server_user_name> | Specifies the SQL Server user name to use to log in if you are using SQL Server authentication. |
-P | -P <SQL_Server_password> | Specifies the password to use to log in if you are using SQL Server authentication. |
-I | -I <SQL_Server_instance> | Specifies the name of the SQL Server instance you want to log on to. If no value is entered, the default instance is used. To explicitly specify the default, use |
| Process Parameters | ||
-SQL | -SQL "T-SQL_statement" | Identifies the Transact-SQL statement for the backup or restore operation. For details of the standard commands you can use, and the extensions to the standard Transact-SQL syntax, see the following pages: |
-USE | -USE "template_name" | Indicates that you want to load the configuration values from the specified template. You save the template using the Back Up wizard. |
Examples
The following command restores the pubs database on the SQL Server default instance using Windows authentication:
SQLBackupC.exe -SQL "RESTORE DATABASE pubs FROM DISK ='C:\Backups\pubs.sqb' WITH PASSWORD = 'BackupPassword'"
The following command takes full, encrypted backup of the pubs database on a named instance, ServerInstance2, using SQL Server authentication
SQLBackupC.exe -I ServerInstance2 -U sa -P MyPassword -SQL "BACKUP DATABASE pubs TO DISK ='C:\Backups\pubs.sqb' WITH PASSWORD = 'BackupPassword'"
The following command loads a template for a backup job called Daily Full Backup on the SQL Server default instance using SQL Server authentication:
SQLBackupC.exe -USE "Daily Full Backup" -U sa -P MyPassword
Accessing SQLBackupC.exe from other locations
You can set up your system so that you can access the SQL Backup Pro command line executable SQLBackupC.exe from your current location without typing the full path.
To make the executable accessible to all users, add the SQL Backup Pro installation path to the System PATH environment variable; to make the executable accessible only to the current user, add the SQL Backup Pro installation path to the User PATH environment variable. You can access the Environment Variables property page from the Advanced tab of the System Properties. Refer to your Microsoft Windows documentation for details.
Using the extended stored procedure
The extended stored procedure is installed when you install the SQL Backup Pro server components on your SQL Server instance. For details, see Installing the server components on a SQL Server instance.
When running the extended stored procedure you can use both connection and process paramenters, in the form:
master..sqlbackup 'parameters'
You can use connection parameters with the extended stored procedure to back up or restore databases on a different SQL Server instance. If you do not specify connection parameters, the extended stored procedure will run on the instance you are already connected to.
The following connection and process parameters are available:
| Parameter | Syntax | Description |
|---|---|---|
| Connection Parameters | ||
-U | '-U <SQL_Server_user_name>' | Specifies the SQL Server user name to use to log in if you are connecting to a different SQL Server instance using SQL Server authentication. |
-P | '-P <SQL_Server_password>' | Specifies the password to use to log in if you are connecting to a different SQL Server instance using SQL Server authentication. |
-I | '-I <SQL_Server_instance>' | Specifies the name of the SQL Server instance you want to log on to. If no value is entered, the SQL Server you are connected to is used. To explicitly specify the default, use |
| Process Parameters | ||
-SQL | '-SQL "T-SQL_statement"' | Identifies the Transact-SQL statement for the backup or restore operation. For details of the standard commands you can use, and the extensions to the standard Transact-SQL syntax, see the following pages: |
-USE | '-USE "template_name"' | Indicates that you want to load the configuration values from the specified template. You save the template using the Back Up wizard. |
Note that the set of parameters must be enclosed by a pair of single quotes. Therefore, wherever you would use a single quote to delimit variables when using the command line, for the extended stored procedure you must use two single quotes so that SQL Server does not interpret it as a string delimiter. For example:
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [pubs] TO DISK = ''C:\Backups\pubs.sqb'' WITH PASSWORD = ''MyPassword'' " '
Alternatively, to make your code easier to read, you can use square brackets instead of two single quotes to delimit variables:
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [pubs] TO DISK = [C:\Backups\pubs.sqb] WITH PASSWORD = [MyPassword] " '
Feedback from the extended stored procedure
The extended stored procedure returns results in datasets:
- the SQL Backup messages are returned in the first dataset
- the SQL Backup exit code, the SQL Server error code, and the names of all the backup files are returned in the second dataset
To omit the second dataset from the results, use the SINGLERESULTSET keyword. This is useful if you want to process the first dataset within a Transact-SQL script. Transact-SQL scripts only allow manipulation of the returned data if a single dataset is returned.
For example:
To retrieve error codes from the sqlbackup extended stored procedure, call the extended stored procedure with two output parameters of type integer. SQL Backup Pro returns:
- the SQL Backup exit code in the first output parameter (or '0' if there are no SQL Backup errors)
- the SQL Server error code in the second output parameter (or '0' if there are no SQL Server errors)
For example:
Note that the sqlbackup extended stored procedure itself will not generate an error in the above example. To force the call to the extended stored procedure to generate an error when a SQL Backup error or SQL Server error is returned, you must use the RAISERROR keyword. For example:
Now, if the sqlbackup extended stored procedure encounters a SQL Backup error (code 500 or higher), the query will fail.
For more information on the warning codes and error codes, see SQL Backup warnings 1 - 499 and SQL Backup errors 500 - 5292.
