Using the Extended Stored Procedure

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 and Upgrading SQL Backup.

Using the extended stored procedure

You can use exactly the same parameters for the extended stored procedure as for the command line, in the form:

master..sqlbackup 'parameters'

For details of the parameters you can use with the extended stored procedure, see Toolkit Parameters.

Note that the extended stored procedure expects only one parameter, which must be delimited by single quotes. Therefore, wherever you would use a single quote for the command line parameters, 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, you can use square brackets instead of two single quotes:

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

Although this is not standard Transact-SQL, your code will be easier to read.

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 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
the SQL Server error code in the second output parameter

For example:

For more information on the warning codes and error codes, see Errors.