Using the extended stored procedure - SQL Backup

SQL Backup provides an extended stored procedure so that you can 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.

You are recommended to use the graphical user interface (GUI) before you use the extended stored procedure, so that you are familiar with the SQL Backup features and tools.

The extended stored procedure is installed when you install the SQL Backup server components on your SQL Server instance. For details, see Installing or upgrading on a SQL Server instance.

Extended stored procedure parameters

When running the extended stored procedure you can use both connection and process paramenters, in the form:

master..sqlbackup 'parameters'

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, to back up the pubs database, you might use the following on the command line:

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

For the extended stored procedure you would use:

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:

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

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

'-U SQL_Server_user_name'

The -U parameter 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. For example:

'-U sa'

'-P SQL_Server_password'

The -P parameter specifies the password to use to log in if you are connecting to a different SQL Server instance using SQL Server authentication. For example:

'-P MyPassword'

'-I SQL_Server_instance'

The -I parameter 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. For example:

'-I ServerInstance2'

To explicitly specify the default, use:

'-I (local)'

Process parameters

'-SQL "Transact-SQL_statement" '

The -SQL parameter identifies the Transact-SQL statement for the backup or restore operation. For example

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

For details of the standard commands you can use, and the extensions to the standard Transact-SQL syntax, see the following topics:

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

Some examples are provided in Syntax examples.

'-USE "template_name" '

The -USE parameter indicates that you want to load the configuration values from the specified template. You save the template using the Back Up wizard. For example, you may have saved a template called Daily Full Backup:

'-USE "Daily Full Backup" '

This might be equivalent to using the -SQL parameter as follows:

'-SQL "BACKUP DATABASE pubs TO DISK =''<AUTO>''" '

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

For example:

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.

To retrieve error codes from the sqlbackup extended stored procedure, call the extended stored procedure with two output parameters of type integer. SQL Backup 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' is 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 Error code reference.