Using the command line - SQL Backup

SQL Backup provides a command line interface so that you can run your backup and restore operations in script and batch files. Note that you cannot use the command line to back up or restore databases on remote servers; you must use the extended stored procedure or the graphical user interface with SQL Backup Pro.

Before you use the command line, you are recommended to use the graphical user interface until you are familiar with the SQL Backup features and tools.

The SQL Backup command line executable is SQLBackupC.exe. It is located in the folder in which you installed the SQL Backup server components; for example, C:\Program Files\Red Gate\SQL Backup\<instance name>. For details, see Installing or upgrading on a SQL Server instance.

Command line parameters

When running SQL Backup from the command line, you can use both connection parameters and process parameters. Use a command of the format:

>SQLBackupC.exe parameters

Connection parameters

-U SQL_Server_user_name

The -U parameter specifies the SQL Server user name to use to log in if you are 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 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 default instance 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' WITH PASSWORD = 'MyPassword'"

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>'"

Examples

The following command restores the pubs database on the SQL Server's default instance using Windows authentication:

>SQLBackupC.exe -SQL "RESTORE DATABASE pubs FROM DISK ='C:\Backups\pubs.sqb' WITH PASSWORD = 'BackupPassword'"

To log on to a named instance using SQL Server authentication, you would use the following:

>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 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 installation path to the System PATH environment variable; to make the executable accessible only to the current user, add the SQL Backup 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.