SQL Backup Latest version: 6.4

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 or upgrading on a SQL Server instance.

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 set of parameters must be enclosed by a pair of 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, to make your code easier to read, 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] " '

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