Getting started

SQL Backup™ enables you to create smaller, faster, more robust and reliable backups of Microsoft® SQL Server™ databases, much more quickly than you can create native backup files.

With SQL Backup's compression, your backup files are usually 50% to 95% smaller than native SQL Server backups. You can also encrypt your backup files using 128- or 256-bit key Advanced Encryption Standard encryption. The network resilience feature in the Pro edition enables backup and log shipping activities to resume automatically if they are interrupted by a network outage. SQL Backup also features an interactive time line for quick access to a graphical overview of completed backup and restore activities, and jobs scheduled for the future. SQL Backup Pro works with remote SQL Servers and SQL Server clusters.

SQL Backup is compatible with SQL Server 2008, 2005 and 2000.

You can use SQL Backup to:

  • back up multiple databases and transaction logs (and set up scheduled backup jobs)
  • restore multiple backups
  • set up scheduled transaction log shipping
  • generate automatic email notification when the backup or restore operation is completed, or if errors have occurred during the process
  • provide extra resilience against network outages for backups and log shipping
  • back up and restore databases from the command line interface
  • run SQL Backup processes while you are connected to the database server remotely using an application such as Microsoft SQL Server Management Studio, or connectivity tools such as ADO, OLEDB, ODBC
  • recover individual objects from a SQL Backup file with SQL Object Level Recovery Pro
  • generate Microsoft Tape Format (MTF) files from SQL Backup files

Technical notes

SQL Backup Lite does not support encryption of backups. However, you can restore encrypted backups using SQL Backup Pro or SQL Backup Lite.

SQL Backup Lite does not support compression of backups using compression level 2, 3, or 4. However, you can restore any compressed backup using SQL Backup Pro or SQL Backup Lite.

SQL Backup does not support cumulative backups in a single file.

To restore a backup file that was created by SQL Backup v6, onto a SQL Server instance running SQL Backup v5, you must first convert the backup file.

Getting more information

  • You can display hints for some features and options in the interface by clicking helpanchor
  • What's new in this version of SQL Backup?
  • Find out about activating SQL Backup.
  • How can I check for updates to SQL Backup?
  • Find out more about Red Gate.

© Red Gate Software Ltd. All Rights Reserved.


Installing or upgrading SQL Backup

In this chapter

  • Installation overview
  • Technical requirements
  • Licensing
  • Installing or upgrading the graphical user interface
  • Installing or upgrading the server components
  • Upgrading from SQL Backup 4 or 5
  • Uninstalling SQL Backup
  • Security model


Installation overview

SQL Backup comprises two main parts:

  • The SQL Backup graphical user interface (GUI) for setting up and monitoring your backup jobs.

    The GUI is not licensed and can be installed on as many computers as you wish. You can install only one version of the SQL Backup GUI on each computer.

  • The SQL Backup server components installed on each SQL Server instance.

    This is the licensed part of SQL Backup and contains the SQL Backup extended stored procedures and the SQL Backup Agent Service. You can install only one version of the SQL Backup server components on each SQL Server instance.

To fully install or upgrade SQL Backup:

  1. Install the SQL Backup graphical user interface (GUI).

    For details, see Installing or upgrading the graphical user interface.

  2. Use the GUI to register the SQL Server instances on which you want to use SQL Backup.

    For details, see Adding SQL Server instances.

  3. Install or upgrade the SQL Backup server components on the SQL Server instances as necessary.

    You can use the graphical user interface to do this, or you can run the setup wizard manually. Both methods are described in Installing or upgrading on a SQL Server instance.

    When you have installed or upgraded the SQL Backup server components, if your license has not already been activated on the relevant servers, the instances are displayed in trial mode [image].

  4. Activate the licenses on the servers as necessary.

    For details, see Activating SQL Backup.

If, having installed and activated SQL Backup, you find that you cannot run backup and restore activities, check that:

  • your permissions have been set up correctly

    For details of the SQL Backup security model, see Security model

  • your database SQL Server instance is running

    Ensure that you have started the Microsoft SQL Server services.

    If you are using the command line and use named instances instead of default instances, ensure that you have set the -I command line parameter to the named instance.


Technical requirements

Microsoft SQL Server support

SQL Backup Pro supports 32-bit, 64-bit, and Itanium versions of Microsoft SQL Server 2008, SQL Server 2005 and SQL Server 2000. SQL Backup Lite supports 32-bit versions. The following editions are supported:

  • Developer Edition
  • Standard Edition
  • Enterprise Edition
  • Web Edition

SQL Backup provides limited support for SQL Server Express Edition; the SQL Backup graphical user interface does not support scheduled backup jobs or log shipping with this edition.

For SQL Server 2008, Service Pack 1 is supported (but not required). For SQL Server 2005, Service Packs 1, 2 and 3 are supported (but not required). For SQL Server 2000, Service Pack 3a or later is required.

The graphical user interface requires .NET version 2 or later.

Microsoft Windows support

SQL Backup supports the following Microsoft Windows operating systems:

  • Windows 2000 Professional Service Pack 4
  • Windows 2000 Server Service Pack 4
  • Windows 2000 Advanced Server Service Pack 4
  • Windows XP Professional Service Pack 1, 2 and 3
  • Windows 2003 Server Standard Edition and Windows 2003 Server Enterprise Edition:

    For 32-bit versions of Windows, Service Pack 1 and Service Pack 2 are supported but not required.

    For 64-bit versions of Windows, Service Pack 1 is required. You must install this separately for Itanium; for x64 it is installed automatically. Service Pack 2 is supported but not required.

  • Windows 2008 Server Service Pack 1 and 2 (Standard Edition and Windows 2008 Server Enterprise Edition)
  • Windows Vista Service Pack 1 and 2


Licensing

You can add any SQL Server instance to the SQL Backup GUI, but you must have a license for each server on which you want to use the backup and restore features.

For a 32-bit, local server, you can use a SQL Backup Pro or SQL Backup Lite license. Note that the trial version offers the same features as a SQL Backup Pro license.

For the following servers, you must use a SQL Backup Pro license:

  • remote servers
  • 64-bit servers
  • clustered servers (one license per cluster node)

The following features are only available with a SQL Backup Pro license:

  • backup encryption
  • backup compression (levels 2, 3, and 4)
  • email notification when a SQL Backup job has run
  • log shipping
  • network resilience
  • reporting
  • check for orphaned users after restoring
  • kill existing connections before restoring
  • object level recovery using SQL Object Level Recovery Pro

If a particular feature is not available on a server due to licensing restrictions, it is marked with a [image] icon on the SQL Backup GUI.

Details of how you add SQL Server instances to the SQL Backup GUI are provided in Adding SQL Server instances.

Details of how you activate SQL Backup on a SQL Server once you have purchased a license are provided in Activating SQL Backup.


Installing or upgrading the graphical user interface

Install the SQL Backup graphical user interface (GUI) on the computer from which you want to run the backups, using the SQL Backup setup program SQL Backup.exe. If an earlier version of the SQL Backup GUI is already installed on the computer, it will be upgraded.

If you are upgrading from SQL Backup version 5, you will be asked to upgrade the activity cache when you run the SQL Backup version 6 GUI for the first time. See Upgrading from SQL Backup version 5 for more information.

Note that you cannot install more than one version of the SQL Backup graphical user interface on the same computer.

The following files are installed in your Program Files/Red Gate/SQL Backup 6 folder:

RedGate.SQLBackup.UI.exe

The SQL Backup graphical user interface.

SQBServerSetup.exe

The server components setup program.

ProductActivation.exe

The program for activating SQL Backup without using the graphical user interface. For example, you can use this program to activate cluster nodes. Read more about activation.

CompressionAnalyzer.exe

The SQL Backup program to compare the effectiveness of different compression levels on a full backup of a selected database. Read more about the Compression Analyzer.

SQBConverter.exe

and

SQBConverterGUI.exe

SQBConverter.exe is a command-line program to convert SQL Backup files to Microsoft Tape Format (MTF) files. This program will also convert files created using SQL Backup version 6 to files compatible with SQL Backup version 5 and earlier.

SQBConverterGUI.exe is a graphical user interface for the SQBConverter.exe command-line program.

You can distribute this program as required; you do not need the full product installation to use it. For more information see SQL Backup File Converter.

sqb2mtf.exe

A command-line program to convert SQL Backup files to Microsoft Tape Format (MTF) files.

This program is superseded by the SQBConverter.exe program described above. sqb2mtf.exe is provided with SQL Backup v6 so that any existing scripts you may have written that use this program will continue to work.

You can distribute this program as required; you do not need the full product installation to use it.

SQBMaintPlanConv.exe

This program converts maintenance plans created with SQL Server Management Studio or SQL Server Enterprise Manager. It disables native backup tasks and replaces them with tasks for SQL Backup jobs, enabling you to specify compression and encryption settings.

For more information see Using the maintenance plan conversion wizard.

SQLBackup6.chm

The SQL Backup help.

Additionally, the SQBObjectLevelrRecovery folder contains SQLObjectLevelRecoveryPro.exe, the executable file for the SQL Object Level Recovery Pro application.


Installing or upgrading the server components

In this chapter

  • Installing or upgrading on a SQL Server instance
  • Installing on a SQL Server cluster


Installing or upgrading on a SQL Server instance

You can install or upgrade the SQL Backup server components on a SQL Server instance in two ways:

  • using the SQL Backup GUI
  • installing manually

Usually, using the SQL Backup GUI is the easiest way to install or upgrade the server components.

You may prefer to install the server components manually if your SQL Server security does not allow you to install remotely, or if you want to choose the location of the SQL Backup files.

Note that version 6 of SQL Backup introduces changes to the SQL Backup file format (.sqb). To restore backup files created by SQL Backup 6 using an earlier version of SQL Backup, you must use the SQL Backup File Converter to convert the files first. When upgrading to version 6 of the server components, we recommend that you upgrade all SQL Server instances to maintain compatibility.

Installing or upgrading server components using the GUI

  1. Start the graphical user interface.
  2. If the SQL Server instance is not listed, add the SQL Server instance with which you want to use SQL Backup.

    The SQL Server instance is displayed in the Registered SQL Servers pane. If one of the following icons is displayed to the right of the name of the SQL Server instance, you can install or upgrade the server components on the SQL Server:

    SQL Backup is installed and activated on this SQL Server, but the version of server components is not up-to-date compared with your version of the graphical user interface.

    Move the mouse pointer over the icon for further information:

    You can continue to use SQL Backup with this SQL Server instance without upgrading. However, you are recommended to upgrade to get the latest enhancements and bug fixes.

    This icon does not show you when new versions of SQL Backup become available for download; you can use Check for updates for this.

    SQL Backup Version 4 (or earlier) server components are installed on the SQL Server. You must upgrade the server components to use SQL Backup 6 with this SQL Server instance.

    You have not installed the SQL Backup server components on the SQL Server. You must install the server components to use SQL Backup 6 with this SQL Server instance.
  3. Click the icon, or right-click the SQL Server instance, and click Install or Upgrade Server Components.

    If you are installing SQL Backup for the first time on this server, the wizard guides you through the installation process.

  4. Ensure that no other SQL Backup processes are currently running on the SQL Server instance, and click Finish to start the installation.

    A message dialog box shows the progress of the installation process.

  5. When the installation has completed, click Close.

You may also be prompted to install or upgrade the server components when setting up a backup or restore, or configuring log shipping. When you select a server that does not have the SQL Backup server components installed, a warning icon is displayed and you will not be able to click Next to proceed.

Click the Install Server Components link to install the components.

Installing or upgrading server components manually

You may wish to install or upgrade SQL Backup on your SQL Server instances manually using the SQBServerSetup.exe executable if your SQL Server security does not allow you to install remotely, or if you want to customize the installation.

The server components of SQL Backup comprise:

SQBCoreService.exe | The SQL Backup Agent Service application.
xp_sqlbackup.dll | The SQL Backup extended stored procedure dynamic-link library. This will be located in a subfolder called Win32, x64, or Itanium depending on the SQL Server version.
SQBShellExt.dll | The SQL Backup Windows shell extension library.
SQBShellExt.reg | The registry information used to register the SQL Backup shell extension.
SQLBackupC.exe | The SQL Backup command line interface.
zlib1.dll | The compression library used by SQBCoreService.exe and SQLBackupC.exe

To install these files manually:

  1. Copy SQBServerSetup.exe from your SQL Backup installation folder to the server on which you want to install the components.

    By default, for 32-bit servers the installation folder is C:\Program Files\Red Gate\SQL Backup 6; for 64-bit servers it is C:\Program Files (x86)\Red Gate\SQL Backup 6

  2. Run SQBServerSetup.exe from the server. The Setup wizard guides you through the installation process. You can specify:
    • the instance on which you want to install the server components
    • the startup user for the SQL Backup Agent Service application
    • the authentication method to be used for the SQL Server
    • the folder in which you want to create the SQL Backup data store
    • the folder in which you want to store SQL Backup log files
    • the folder in which you want to install files relating to the SQL Server Compact database (SQL Backup uses a SQL Server Compact database to store information about backup and restore operations)

      Note that SQBShellExt.dll and SQBShellExt.reg are always installed in Program Files\Common Files\Red Gate\SQL Backup for 32-bit versions of Windows (Win32), or Program Files (x86)\Common Files\Red Gate\SQL Backup for 64-bit versions of Windows (x64 and Itanium).

  3. Use the SQL Backup GUI to activate SQL Backup on the SQL Server instances as required.


Installing on a SQL Server cluster

You can install the SQL Backup Pro server components on all SQL Server instances (nodes) in a cluster at the same time. If you have a SQL Backup Pro multi-server license, you can then activate SQL Backup on all of the nodes.

You can also activate a number of servers that are not clustered at the same time if you have purchased a SQL Backup Pro multi-server license. However, you must first install the SQL Backup server components on each of the SQL Server instances individually.

Note that if you are using SQL Backup with clustered SQL Servers, you are strongly recommended to save backup files on a shared drive within the cluster. This ensures that the backups are available in the event of failure of one of the clustered SQL Servers.

Installing on clusters

This section provides an overview of how you install the SQL Backup server components on clusters. For a more detailed description, see the article Installing SQL Backup server components on clusters.

When you install the server components on a cluster, the SQL Backup Agent Service application is clustered so that it can run backup and restore operations on whichever cluster node is active. In addition, the following registry keys in the SQL Backup branch are replicated:

  • BackupSettingsGlobal\<instance name>
  • BackupSettings\<instance name>

These registry keys contain details of saved SQL Server settings, templates, and other resources that are specific to SQL Backup. Note that SQL Backup scheduled backup jobs and log shipping jobs are replicated by the SQL Server instance.

You can install the server components from within the SQL Backup graphical user interface, or by using a standalone program:

  • use the SQL Backup graphical user interface if you want to install remotely

    To do this, register the cluster using the Add SQL Server dialog box.

    By default, the Add SQL Server dialog box provides the option to install or upgrade the server components on the registered cluster. If you clear this option, you can install or upgrade the components at a later date from the Registered SQL Servers pane; to do this, right-click on the SQL Server instance, and then click Install or Upgrade Server Components.

    Specify the alias for the clustered SQL Server instance on the Options tab of the Add SQL Server dialog box.

  • use the standalone program to install locally on each node

    Copy SQBServerSetup.exe to the active node on the cluster, and run the program. When you install in this way, more information is provided if a problem occurs (particularly for post-installation checks). However, you cannot use this method to install remotely.

The Setup wizard guides you through the installation process. When you specify the SQL Server instance, the Setup wizard detects that the SQL Server instance is part of a cluster, and provides the option to install SQL Backup on all the cluster nodes.

If you do not select this option, SQL Backup is installed only on the specified SQL Server instance, and it is not used as a clustered node for SQL Backup. You must enter an account name and password for a user that is an administrator on all nodes on the cluster.

When you enter the account name and password that the SQL Backup Agent Service application will run as, you are recommended to use a domain account. The account must have sufficient permissions to run as a service, including the Log on as a service permission.

The account that the SQL Backup Agent Service application will use to connect to the cluster node must have sysadmin privileges.

The Setup wizard also provides the option to automatically fail over to another cluster node if the SQL Backup Agent Service cannot be started. Note that this option is not available if you are upgrading the server components.

The SQL Backup server components are installed in the same location on each node in turn.

Activating on clusters and multiple SQL Servers

For general information about activating SQL Backup, see Activating SQL Backup. In addition, when you activate on clusters or multiple servers, note the following points.

To activate SQL Backup on a cluster or on multiple servers:

  • you must have a multi-server license key
  • you must use the standalone program ProductActivation.exe (rather than the SQL Backup graphical user interface) to launch the Product Activation wizard

    This is installed in your Program Files\Red Gate\SQL Backup 6 folder.

The Activation wizard offers you the Multiple servers option. In addition, if the Activation wizard detects that the local server is clustered, it offers you the option to activate all the nodes in the cluster.

Click All nodes on cluster if you want SQL Backup to run the activation on all the nodes automatically; the nodes detected by SQL Backup will be listed in the next page of the wizard. Click Multiple servers if you want to specify the servers in the cluster manually, or if you are activating multiple servers that are not in a cluster; you must then type the name of each server on the next page of the wizard.

You must enter the credentials of a user with administration rights on all the servers that you are activating. Only the user name and password is taken into account.

If you choose to activate by email, for each server in the cluster you must copy the activation text provided and send it in a separate email to the activation server. You will receive a separate response for each server; paste each response into the appropriate tab in the wizard. For more information on activating by email see Activating SQL Backup.

The final page of the wizard displays the final status of activation on all of the servers. If activation has failed on any servers, click Finish to close the Activation wizard, and repeat the activation for those servers.

See also

Activating SQL Backup

Adding SQL Server instances

About the graphical user interface

Installation overview

The Registered SQL Servers pane


Upgrading from SQL Backup 4 or 5

Upgrading from SQL Backup version 5

If you are upgrading from SQL Backup version 5, you do not need to import SQL Servers that you previously registered. Your servers will be added automatically to the SQL Backup 6 Registered SQL Servers pane.

If you have not yet installed the SQL Backup 6 server components on a registered SQL Server, [image] is displayed to the right of the SQL Server name in the Registered SQL Servers pane. You can click the icon to upgrade the components, as described in Installing or upgrading server components using the GUI.

If you have already upgraded the SQL Backup GUI, the same icon [image] may be shown in various locations when you are working with a SQL Server instance that still has version 5 server components installed. This indicates that certain features are unavailable until you upgrade the server components to version 6.

Upgrading from SQL Backup version 5 is straightforward. However, version 6 of SQL Backup introduces changes to the backup file format. When you upgrade the SQL Backup server components to the latest version 6 components, SQL Backup creates new backup files in the version 6 format. The backup file extension is unchanged (.sqb).

  • SQL Backup 6 is fully compatible with files created by earlier SQL Backup versions. You can work with any backup file version using SQL Backup 6.
  • SQL Backup 5 (version 5.4 and earlier) cannot be used to restore a version 6 backup file. To restore a version 6 backup file to a server that has not been upgraded, you must first convert the backup file.

Once you have upgraded, you must activate the SQL Server. If you have purchased a support contract, you can use the same license key as you used in version 5 (SQL Backup will remember this for you). Otherwise, you can use a trial version of SQL Backup 6 for 14 days, but then you must purchase a license.

The scripts and jobs you created using version 5 are supported in version 6, and will work in the same way.

Upgrading the activity cache

The activity cache speeds up the display of backup and restore history in SQL Backup by storing a copy of the backup and restore history for your registered SQL Servers.

The cache data is located on the same computer as the SQL Backup graphical user interface, in C:\Documents and Settings\<user name>\Local Settings\Application Data\Red Gate\SQL Backup\Server Data by default.

When you start the graphical user interface for the first time, you may be prompted to upgrade the activity cache. This will only happen if an activity cache from SQL Backup version 5 is found on your computer.

If you accept the default settings (Upgrade now; Compact the cache), SQL Backup will upgrade and compact the whole activity cache before the main SQL Backup graphical user interface is displayed. This may take several minutes, but happens only once.

If you would rather start the SQL Backup graphical user interface immediately, choose Upgrade later. SQL Backup will automatically upgrade portions of the activity cache when you select a server on the time line. It may take a few minutes to display activity history when you first select a server.

Note that if you choose Upgrade later, you will not be prompted about upgrading the activity cache again.

Upgrading from SQL Backup version 4

If you are upgrading SQL Backup from version 4, you can import all your registered SQL Servers into SQL Backup 6; you do not have to register them all again. For details, see Adding SQL Server instances.

When you register a SQL Server, if you have not yet installed the SQL Backup 6 server components on it, [image] is displayed to the right of the SQL Server name in the Registered SQL Servers pane. You can click the icon to upgrade the components, as described in Installing or upgrading server components using the GUI.

You must then activate the SQL Server. If you have purchased a support contract, you can use the same license key as you used in version 4 (SQL Backup will remember this for you). Otherwise, you can use a trial version of SQL Backup 6 for 14 days, but then you must purchase a license.

If you do not upgrade the server components, SQL Backup retrieves the details of activities and jobs on these SQL Servers when you first start SQL Backup. The information is displayed in the time line, the activity history, and the Jobs tab. However, this information cannot be refreshed while SQL Backup is running.

Any options you set in version 4 are imported into version 6. However, note that backup alerts are no longer available; instead you can now use the time line or create a report of backup activity to see overdue backups. For more information about the options available, see Options.

The scripts and jobs you created using version 4 are supported in version 6, and will work in the same way.

If you saved any backup settings in version 4, you can use them in version 6. However, in version 6 they are called templates.

Some features are restricted for activities and jobs from a previous version of SQL Backup. For example, you cannot display the properties of a backup performed by SQL Backup version 4.


Uninstalling SQL Backup

SQL Backup creates separate entries for the SQL Backup graphical user interface, and the SQL Backup server components in your Add or Remove Programs control panel. You can add or remove each of these programs in the usual way.

Removing the server components will also result in the removal of the extended stored procedures, the SQL Backup Agent service, and the SQL Backup binaries from the SQL Server.

You will be prompted to confirm whether you want to remove the SQL Backup file that contains the history of your backup and restore activity. If you intend to reinstall SQL Backup, you are recommended to click No to retain the file for future reference; click Yes to remove this file only if you are removing SQL Backup from your computer permanently.


Security model

The SQL Backup process is controlled by a dedicated SQL Backup Agent Service application for each SQL Server instance.

To use the SQL Backup graphical user interface or extended stored procedure to perform backup and restore operations, the startup user for the SQL Backup Agent Service application must be a member of the SQL Server sysadmin fixed server role. If you are working with network files (for example, for log shipping), the user must have access to those network shares; for more information, see the article Using SQL Backup to back up to a network share. In addition, the SQL Backup user must have:

  • the usual SQL Server backup permissions
  • execute permissions on the SQL Backup extended stored procedure sqlbackup (for example, must be a member of the public database role in the master database)
  • for the graphical user interface, execute permissions on the following extended stored procedures:

    sqbdata

    sqbstatus

    sqbdir

    sqbutility

    sqbmemory

    sqlbackup
  • for the compression analyzer, execute permissions on the following extended stored procedures:

    sqbtest

    sqbtestcancel

    sqbteststatus

When you install the server components, you are asked to provide 'log on' details for the SQL Backup Agent Service to connect to the SQL Server. By default, the SQL Backup Agent Service connects to the SQL Server instance using Windows authentication, but if required, you can specify SQL Server Authentication in the installation wizard. If you want to change the credentials at a later date, use the sqbsetlogin extended stored procedure to provide the login name and password. You will need to add the stored procedure before you use it, and you are recommended to remove it when you have finished using it. For example:

sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'

sqbsetlogin 'sa', 'sqbpassword'

sp_dropextendedproc sqbsetlogin

To revert to Windows authentication, call sqbsetlogin with blank values:

sqbsetlogin '', ''

If you encounter errors related to permissions and access rights, ensure that the startup user for the SQL Backup Agent Service application has been granted the necessary permissions.

The SQL Backup command line program communicates with SQL Server directly; it does not use the service application. To run SQLBackupC.exe, the user must have the SQL Server sysadmin fixed role.

Using a different security model

You may want to use a different security model, for example if you want to back up locally but copy the backup to a locked down network share. The following procedure assumes that you are working in a single domain.

  1. Create domain account with minimal permissions.
  2. Create a SQL Server logon account that has the ability to back up and restore databases.

    To do this, add the account to the sysadmin or db_backupoperator fixed role, or if you are using SQL Server 2005 or 2008, you can use the GRANT BACKUP command.

  3. Add the domain account that you created in step 1 to a security group on the Windows server on which the SQL Server is installed; the security group must have sufficient permissions to run as a service.
  4. When you install the SQL Backup server components on the SQL Server:
    • For the SQL Backup Agent Service credentials, select This account and enter the domain account you created in step 1.
    • For the SQL Server credentials, select SQL Server Authentication, and specify the credentials for the SQL Server logon account you created.
  5. Create the folder on the local server in which you want to create the backups, and a folder on a network share to which you want to copy the backup files.
  6. Confirm that the permissions on both folders are set such that the domain user you created in step 1 can access and write to them.

To check that all the accounts have the appropriate permission, use the graphical user interface to create a backup job that backs up to a local folder and copies the backup to a network share.

You could also run the following query to ensure that the domain account has sufficient permissions on the network share:

EXECUTE master..sqbutility 999, 'RWE', '\\testsrv\backup'

If this is successful and the agent as read (R), write (W), and execute (E) permissions, the query will return:

<SQBUTILITYRESULT>:1:

If there is a problem, the query will return a value of 0, followed by a message, for example:

<SQBUTILITYRESULT>:0:Folder does not exist : \\testsrv\backup

If the SQL Server and the network share server do not participate in the same Windows domain, you can use matching Windows local user names and passwords on each server to perform the same task. This practice is commonly known as 'matching accounts'.

See also

Getting started

Activating SQL Backup

About the graphical user interface

Configuring log shipping

Using the command line

Using the extended stored procedure


Activating SQL Backup

When you download and install SQL Backup, you have a 14 day trial period in which you can evaluate the product. When your trial period expires, you are invited to purchase the product. If you need more time to evaluate the product, contact licensing@red-gate.com

When you purchase SQL Backup, you are sent an invoice that contains your serial number. You use the serial number to activate the product. If you cannot find your invoice, you can review your serial numbers at http://www.red-gate.com/myserialnumbers

SQL Backup is licensed per physical server. If you have a number of SQL Server instances registered with SQL Backup that are all on the same physical server, you need to activate only one of the instances. The status of SQL Backup on the server for a registered SQL Server instance is displayed in the Registered SQL Servers pane in the graphical user interface. You can activate SQL Backup on a server if one of the following icons is displayed to the right of the name of the SQL Server instance:

SQL Backup is activated for a trial period on this server.

The SQL Backup trial period has expired. To use SQL Backup with this SQL Server instance, you must activate SQL Backup for the server.

When you activate SQL Backup, the licensing and activation program sends an activation request to the Red Gate activation server, using checksums of attributes from your computer. The checksums that are sent to the activation server do not contain any details that might pose a security risk. The activation server returns an activation response and an encrypted key to unlock the software. The licensing and activation program should activate SQL Backup within a few seconds.

If you experience problems, you can try manual activation. You can use manual activation if:

  • your computer is not connected to the Internet
  • your network uses a proxy server that interrupts contact between the product and the Red Gate activation server
  • activation is unsuccessful (see Troubleshooting licensing and activation errors)

Note that you cannot automate activation using the command line. However, if you are not able to activate a server using the graphical user interface, you can use the program ProductActivation.exe which is provided with SQL Backup. For example, you may wish to use this program to activate cluster nodes. ProductActivation.exe is located in the folder in which you installed SQL Backup: for example, C:\Program Files\Red Gate\SQL Backup 6. You can also start the program from the SQL Backup GUI by selecting Tools > Utilities > Multi-Server/Cluster Activation Wizard from the menu.

For general questions about licensing, see Licensing and activation FAQs.

To activate SQL Backup on a server

  1. To activate SQL Backup on a server:
    • click the [image] or [image] icon next to the server you want to activate
    • or, right-click the name of the SQL Server instance and click Activate License

      The activation dialog box is displayed:

  2. Enter your serial number and click Activate.

    Your activation request is sent to the Red Gate activation server.

    When your activation has been confirmed, the Activation successful page is displayed:

    If there is a problem with your activation request, an error dialog box is displayed. For information about activation errors and what you can do to resolve them, see Troubleshooting licensing and activation errors. Depending on the error, you may want to try manual activation.

  3. Click Close.

    You can now continue to use your product.

Activating clusters and multiple servers

If you have purchased a SQL Backup license for multiple servers, you can activate these servers at the same time. For example, you may want to do this if you are installing on a cluster.

For more information, see Installing on a SQL Server cluster.

Deactivating SQL Backup

You may wish to deactivate SQL Backup on a server so that you can reuse the serial number on another server. You can do this using the SQL Backup GUI, or the Red Gate deactivation tool.

To deactivate a serial number, the server on which the SQL Backup server components are installed must have a connection to the internet.

Deactivating SQL Backup using the GUI

In the Registered SQL Servers pane, right-click the SQL Server instance and click Deactivate License. If you have more than one SQL Server instance on the same computer, all the instances on that computer will be deactivated.

Note that you can deactivate a SQL Server using the GUI only if the latest server components are installed.

Deactivating SQL Backup using the deactivation tool

If you are unable to deactivate SQL Backup using the GUI (perhaps because the SQL Server is using old SQL Backup server components), you can use the Red Gate deactivation tool on the server instead.

You will need to download and run the deactivation tool on the computer where the SQL Server instance is installed. See Deactivating your products for more information.

Re-activating SQL Servers

If you need to re-install SQL Backup server components on the same computer, for example following installation of a new operating system, you can re-activate using the same serial number. If you need to change the computer on which the server components are installed and your current serial number does not work, contact licensing@red-gate.com

Manual activation

Manual activation enables you to activate products when your computer does not have an Internet connection or your Internet connection does not allow SOAP requests. You will need access to another computer that has an Internet connection.

You can use manual activation whenever the Activation Error dialog box is displayed and the Activate Manually button is available, for example:

To activate manually:

  1. On the error dialog box, click Activate Manually.

    The Activate using the Red Gate Web site dialog box is displayed:

  2. Copy all of the activation request.

    Alternatively you can save the activation request, for example to a location on your network or to a USB device.

  3. Leaving the activation dialog box open, on a computer that has an Internet connection, go to the Manual Activation page of the Red Gate Web site at http://www.red-gate.com/activate and paste the activation request into the box under Step 1.

  4. Click Get Activation Response.
  5. When the activation response is displayed under Step 2, copy all of it.

    Alternatively you can save the activation response.

  6. On the computer where the licensing and activation program is running, paste the activation response or if you saved it, load it from the file.

  7. Click Finish.

    The Activation successful page is displayed.

  8. Click Close.

    You can now continue to use SQL Backup on this computer.

See also

Getting started

About the graphical user interface

Installation overview


Registering SQL Servers with SQL Backup

In this chapter

  • Adding SQL Server instances
  • Managing SQL Server groups


Adding SQL Server instances

To back up or restore a database, you must first add the SQL Server instance to SQL Backup. SQL Backup displays the list of SQL Server instances that have been added in the Registered SQL Servers pane.

You can add the SQL Server instances by:

  • specifying the instances by name
  • importing registered SQL Servers from SQL Backup version 4, Microsoft SQL Server Management Studio, or Microsoft SQL Server Enterprise Manager.

Note that if you are upgrading from SQL Backup version 5, you do not need to import SQL Servers that you previously registered; these servers will be added automatically to the SQL Backup 6 Registered SQL Servers pane.

When you have added a SQL Server instance, you can:

  • edit the registration to change the authentication or connection properties
  • delete an individual SQL Server instance registration, or an entire group

Adding SQL Server instances by name

  1. Click the tab for the location in which you want to register the SQL Server instance.
  2. On the File menu, click [image] Add SQL Server.

    The Add SQL Server dialog box is displayed.

  3. Type or select the name of the SQL Server instance in the SQL Server box.

    If you experience problems selecting a SQL Server instance that is not running on the LAN, for example if you are accessing the SQL Server instance via an internet connection, you may need to create a SQL Server alias using TCP/IP; refer to your SQL Server documentation for details. You can then type the SQL Server alias name in the SQL Server box to connect to the remote SQL Server. (Note that this SQL Server alias is not the same as the SQL Backup alias, in the Options tab.)

  4. Select the authentication method, and for SQL Server authentication enter the User name and Password.

    For Windows authentication, the credentials of the current user are used.

    For SQL Server authentication, you can select the Remember password check box. The password will be encrypted. If you do not select Remember password, when you next start SQL Backup [image] will be displayed next to the SQL Server instance name to indicate that SQL Backup has not attempted to connect to the SQL Server instance; double-click the icon or click [image] to enter the authentication details.

  5. By default, SQL Backup imports the last 1 month of history for native backup and restore operations into an internal cache.

    You can change the amount of native activity history to import, but should be aware that it may take a long time to import and display all the activity history for the SQL Server. The time taken to import the data depends on the number of databases on the SQL Server, the length of time the SQL Server has been in service, and the amount of activity on the SQL Server. You can choose None, 1 week, 1 month, 3 months, 6 months, 1 year, or All.

    Note that if the SQL Server instance has previously been used with SQL Backup (version 4 or above), all activities that were created by SQL Backup are imported, irrespective of the time period you select for importing native activity history.

  6. By default, when you add a SQL Server instance, the Install or Upgrade Server Components wizard is displayed so that you can install or upgrade the server components; SQL Backup requires server components to be installed on each SQL Server instance so that it can perform backup and restore operations.

    If you do not want to install or upgrade the server components now, clear the Install or upgrade server components on connection check box.

    For more information about server components, see Security model and Installing and upgrading SQL Backup.

  7. On the Options tab, under Location and group, select the location in which the SQL Server is situated.

    For more information about locations, see Time settings and locations in The Time Line.

  8. From the Group list, select the SQL Server group to which you want to add the SQL Server instance.

    For more information about groups, see Managing SQL Server groups.

  9. To set up a SQL Backup alias for the SQL Server instance, in Alias type the alias that you want to use.

    The alias will be displayed throughout the SQL Backup graphical user interface.

  10. To change the connection properties from their default values, under Connection Properties edit the values as required.

    For details, see Connection properties.

  11. Click Connect.

SQL Backup adds the SQL Server instance. This may take a few minutes. The SQL Server instance is displayed in the Registered SQL Servers pane.

If there was a problem with the authentication, [image] is displayed next to the SQL Server instance name. For example, this is displayed if you have entered the password incorrectly. Click the icon to correct the authentication details.

If there was a problem connecting to the SQL Server instance, [image] is displayed next to the SQL Server instance name. Click the icon to see further details of the problem. You can also try the following to rectify the problem:

  1. Verify that the SQL Server is online and that the SQL Server name is listed in your LAN by pinging the address.

    For example, open a command prompt and run the following command:

    ping <ServerName>

    where ServerName is the name of your SQL Server.

  2. If the SQL Server is online, verify that you are connecting to the correct port.

    If your SQL Server is not running on the default port (1433), type the following in the SQL Server box on the Add SQL Server dialog:

    <ServerName\Instance>,<Port>

    where ServerName\Instance is the name of your SQL Server and instance, and Port is the number of the port on which your SQL Server is running. For the default instance, use:

    <ServerName>,<Port>

  3. If you are sure that you are connecting to the correct port, force SQL Backup to use the TCP/IP network protocol when it makes the connection by setting the Connection Properties for the SQL Server.

If you chose to install or upgrade the server components, the Install or Upgrade Server Components wizard is displayed to guide you through the process. For more information about this process, see Security model and Installing or upgrading on a SQL Server instance.

Connection properties

To change the connection properties:

  1. On the Add SQL Server dialog box, select the Options tab.
  2. Select the required network protocol from the Network protocol list.

    The available client protocols are those configured in Microsoft SQL Server using the Client Network Configuration in Computer Management.

  3. In Network packet size, type or select the size of the network packets to be sent.

    The default value is 4096 bytes.

  4. In Connection time-out type or select the number of seconds to wait for a connection to be established before timing out.

    The default setting is 15 seconds.

  5. In Execution time-out, type or select the number of seconds to wait before execution of a task is stopped.

    The default value is zero seconds (no time-out).

  6. To force the connection to be encrypted, select the Encrypt connection check box.

Importing SQL Server instances

To select SQL Server registrations that exist already on SQL Backup version 4, SQL Server Management Studio, or SQL Server Enterprise Manager:

  1. On the File menu, click Import SQL Servers.

    The Import SQL Server Registrations dialog box is displayed showing any existing SQL Server registrations in a tree structure.

  2. Select the check boxes for the SQL Server registrations that you want to import.
  3. In the Location list, select the location into which you want to import the SQL Servers.

    For more information about locations, see Time settings and locations.

  4. Click Import.

SQL Backup imports the SQL Server instances.

Editing a SQL Server instance registration

To change the SQL Server authentication or the connection properties:

  1. Right-click the name of the SQL Server instance in the Registered SQL Servers pane and click Edit.
  2. Edit the registration settings and connection properties as required.
  3. Click Connect.

Removing SQL Servers

To remove a SQL Server instance from the Registered SQL Servers pane, do one of the following:

  • Right-click the name of SQL Server instance in the Registered SQL Servers pane, and click Delete.
  • Click the name of SQL Server instance in the Registered SQL Servers pane, then on the Edit menu, click Delete.

To remove all SQL Server instances in a SQL Server group and delete the group, do one of the following:

  • Right-click the name of the group in the Registered SQL Servers pane, click Delete, and then click Delete on the confirmation dialog box.
  • Click the name of the group in the Registered SQL Servers pane, on the Edit menu, click Delete, and then click Delete on the confirmation dialog box.

See also

Managing SQL Server groups

The Registered SQL Servers pane


Managing SQL Server groups

SQL Backup displays the list of registered SQL Server instances in the Registered SQL Servers pane. By default, the instances are listed at the top level (under the location heading). You can create your own hierarchy of groups to organize the registered instances.

When you add a SQL Server instance, you can allocate the instance to an existing group.

Creating SQL Server groups

To create a new group or subgroup:

  1. On the File menu, click [image] New Group.
  2. In the Group name box, type the name for the new group.
  3. Select the Location in which you want to create the group.
  4. Choose to create a top-level group, or a group within a group.

    To create a lower-level group, click Sub-group of, and then select the group in which you want to create the new sub-group.

  5. Click Add Group.

Deleting SQL Server groups

To delete a group or sub-group, do one of the following:

  • Right-click the name of the group in the Registered SQL Servers pane, click Delete, and then click Delete on the confirmation dialog box.
  • Click the name of the group in the Registered SQL Servers pane, in the Edit menu, click Delete, and then click Delete on the confirmation dialog box.

Note that if you delete a group that contains registered SQL Server instances, all instances in the group are removed.

See also

Adding SQL Server instances

The Registered SQL Servers pane


Exploring the graphical user interface

In this chapter

  • About the graphical user interface
  • The Registered SQL Servers pane
  • The Time Line
  • The Activity History
  • The In Progress tab
  • The Log Copy Queue
  • The Jobs tab


About the graphical user interface

The SQL Backup graphical user interface enables you to become familiar with how to create and restore backups using SQL Backup. It also provides features and tools to help you to manage your backups.

To start the graphical user interface, select SQL Backup 6 from your Start menu in the usual way.

If you have upgraded the SQL Backup graphical user interface, SQL Backup displays a message box to inform you that you need to install or upgrade the SQL Backup server components. For more information about server components, see Installing or upgrading on a SQL Server instance.

By default, SQL Backup displays a welcome screen.

If you have previously registered SQL Servers with SQL Backup 5, these will appear automatically in SQL Backup 6. If you are using SQL Backup for the first time, you may wish to click Import servers to import the registered SQL Servers from Microsoft SQL Server Management Studio, or Microsoft SQL Server Enterprise Manager. You can also import registered SQL Servers from SQL Backup version 4. Alternatively, you can click Add a server to register SQL Servers individually. For more information about importing and adding SQL Servers, see Adding SQL Servers. You can also choose to view a demo video, or browse the splashscreens. If you do not want to see this welcome screen when you next start SQL Backup, click Don't show this again.

SQL Backup then displays the main window:

The Registered SQL Servers pane displays a list of the SQL Server instances that are registered with SQL Backup, and their databases. You use this area to select the SQL Server instance or database that you want to work with. For details about how to register new servers so that you can back them up using SQL Backup, see Adding SQL Servers.

The time line is a key area; it provides quick and easy access to a graphical overview of backup and restore activities, both completed and scheduled for the future. You can look at an overview of all activities for a SQL Server instance, or activities on individual databases. It clearly shows activities that have failed, which you can select to see more details. It also shows potential conflicts for scheduled jobs. A demo of the time line, and further information about the other features in SQL Backup 6 are available from the SQL Backup product page.

You can see details of activities and scheduled jobs in the lower pane:

  • the Activity History tab shows the backup and restore operations that have completed or have failed
  • the In Progress tab shows the SQL Backup activities that are currently in progress
  • the Log Copy Queue tab shows any transaction log backups that are due to be copied to another location (a network share, for example)
  • the Jobs tab shows all scheduled SQL Backup jobs, and native SQL Server jobs

If necessary, you can refresh the SQL Server connection for the selected SQL Server instance. This will update both the time line, and the contents of the tabs in the lower pane. You can refresh the connection:

  • on the View menu click [image] Refresh Connection
  • or click the name of the SQL Server instance in the Registered SQL Servers pane and press F5
  • or right-click the name of the SQL Server instance in the Registered SQL Servers pane and click Refresh Connection

While the connection to the instance is being refreshed, [image] is displayed against the instance on the Registered SQL Servers pane. You can still interact with the time line while a connection is being refreshed.

Getting help

To display the SQL Backup help from the graphical user interface:

  • press F1
  • or, on the toolbar, click
  • or, on the Help menu, click [image] Contents

You can also display hints on some individual items in the graphical user interface, where you see the [image] icon. For example, in the Backup wizard, you can display a hint for the Compress Backup option:

Click [image] to display the hint; click on the wizard or click the close button on the popup box to close the hint window.

To get SQL Server documentation (Web only), on the Help menu, click SQL Server Books Online.

Splashscreens

Each time you start SQL Backup, the 'splashscreen' shows a feature of SQL Backup. You can review the splashscreens by clicking Browse Splashscreens on the Help menu.


The Registered SQL Servers pane

The Registered SQL Servers pane displays a list of the SQL Server instances you have registered with SQL Backup, and their databases.

You use this list to select the SQL Server instance or database that you want to back up or restore, or for which you want to view details such as the activity history.

Navigating the Registered SQL Servers pane

The SQL Server instances are organized into groups. You choose the group to which you want an instance to belong when you register it. To change the group to which an instance belongs, in the Registered SQL Servers pane, drag the SQL Server instance to the group. For information about creating and managing groups, see Managing SQL Server groups.

The SQL Server groups, instances, and databases are displayed in a tree structure. To expand an item, click [image]. The time line also expands to show activities for the databases. To collapse an item, click [image].

You can scroll through the list of SQL Server instances and databases by using the scroll bar to the right of the time line, or by moving the mouse pointer over the Registered SQL Servers pane and rotating your mouse wheel button.

Display

The connection status of each SQL Server instance is displayed to the left of the instance:

Connected.

SQL Backup has not attempted to connect to the SQL Server because you are using SQL Server authentication and you chose not to save the password when you entered the authentication details. Double-click the icon or click [image], and then type the password. If you want to save the password in future, select the Remember Password check box.
The corresponding row in the time line is shaded [image] indicating that there is no connection to the server.

SQL Backup could not connect to the SQL Server because of an authentication error, such as an incorrect password. Click the icon to enter the correct details.
The corresponding row in the time line is shaded [image] indicating that there is no connection to the server.

SQL Backup could not connect to the SQL Server. Click the icon to see the associated error messages. Note that you can specify the time period for which SQL Backup will attempt to connect to each SQL Server in the connection properties for that server (right-click the server and click Edit, then select the Options tab). For more information see Adding SQL Server instances.
The corresponding row in the time line is shaded [image] indicating that there is no connection to the server.

SQL Backup is connecting to the SQL Server instance, or refreshing the connection. You can continue to interact with the time line while a connection is being refreshed. The corresponding row in the time line is shaded [image] while connecting.

You may also see the following icons displayed to the right of the SQL Server instance name:

SQL Backup is installed and activated on this SQL Server, but the version of server components is not up-to-date compared with your version of the graphical user interface. Click the icon to upgrade the server components. Note that this icon does not show you when new versions of SQL Backup become available for download; you can use Check for updates for this.

SQL Backup is activated for a trial period on this SQL Server. Click the icon if you have purchased a license and you would like to activate it. For more information, see Activating SQL Backup.

The SQL Backup trial period has expired. To use SQL Backup with this SQL Server instance, you must activate SQL Backup for the SQL Server. Click the icon if you have purchased a license and you would like to activate it. For more information, see Activating SQL Backup.

SQL Backup version 4 or earlier is installed on the SQL Server. You must upgrade the server components to use SQL Backup 6 with this SQL Server instance. Click the icon to upgrade the version of SQL Backup on the SQL Server. For more information, see Installing or upgrading on a SQL Server instance.

You have not installed the SQL Backup server components on the SQL Server. Click the icon to install the server components. For more information, see Installing or upgrading on a SQL Server instance.

For each connected SQL Server instance, you can click [image] to see the databases on the instance. The status of each database is displayed:

Operational. The database is fully usable. Transaction log backups and differential backups cannot be restored to this database.

The database is being restored, or it has been left in a non-operational state (using WITH NORECOVERY) so that differential backups and transaction log backups can be restored to it.

The database is read-only. If the database is read-only because it has been left in a standby state (using WITH STANDBY), transaction log backups and differential backups can be restored to this database.

The database is unavailable because it has been set to a SUSPECT or EMERGENCY state. Refer to your SQL Server documentation for more information.

The database is offline or in an unknown state.

Refreshing the data

For the selected SQL Server, and for all databases that are visible in the time line, SQL Backup refreshes the display every minute.

To refresh the display for a SQL Server, click the SQL Server in the Registered SQL Servers pane, and press F5. is displayed next to the SQL Server instance while SQL Backup refreshes the connection. Note that if the SQL Server is busy, there may be a short delay before the display refreshes. You can continue to interact with the time line while a connection is being refreshed.

Interaction

Whenever applicable, the SQL Backup features and displays act on the item you select in the Registered SQL Servers pane. For example, if you have a database selected and you click [image] Backup, the Backup wizard pre-selects the database for you (you can change the selection in the wizard).

Similarly, the Activity History, In Progress, Log Copy Queue or Jobs tab is updated with information related to the selected item. For example, if the Activity History tab is displayed and you click a SQL Server instance name, all activities that have occurred on that instance are shown in the tab; if you then click a database, only those activities that occurred on the selected database are shown in the tab.

SQL Server and database properties

You can display properties for a SQL Server instance or a database.

To do this:

  • Right-click the SQL Server instance or database in the Registered SQL Servers pane and click Properties.

SQL Backup must be connected to the SQL Server for properties to be displayed.

SQL Backup displays details of the selected SQL Server instance, and database, as applicable. For example:

To see the properties of another SQL Server or database, click on it; you do not need to close the Properties window first.

You can display the properties of backup and restore activities from the Activity History. For details, see "Activity properties" in Activity History.

See also

The Jobs tab

The In Progress tab

The Activity History

Adding SQL Server instances

Managing SQL Server groups

The Time Line


The Time Line

The time line provides a graphical view of SQL Backup backup and restore activities. For a demonstration of the time line, see the SQL Backup walk-throughs and demonstration videos.

The time line shows activities that have been set up using the SQL Backup graphical user interface, and, where possible, any other SQL Backup activities, such as those initiated using the command line. However, note that not all SQL Backup activities set up outside of the graphical interface can be recognized by SQL Backup.

Object level recovery activities (performed using the SQL Object Level Recovery Pro application) are not recorded on the time line.

Display

For each registered SQL Server instance that is connected, the time line shows a summary of the activities on that SQL Server instance.

A gray block [image] indicates a successful activity or a scheduled job; a red block [image] indicates a failed activity or conflicting backup jobs.

To see the activities for the individual databases, click [image] to expand the SQL Server instance in the Registered SQL Servers pane so that the databases are listed. The activities that correspond to the databases are shown.

Activities that have already occurred are shown as solid blocks, as illustrated above. Backup operations are green [image]; restore operations are blue [image]. The type of backup is indicated by the height of the block. File or filegroup backups are the same height as differential backups.

An operation that failed is shown as a red block

Jobs that are in progress are shown as striped blocks, for example:

Jobs scheduled for the future are shown as hollow blocks, for example: . SQL Backup estimates the time duration of the job based on the last successful backup on the same database and of the same type. (Note that for multi-step jobs that include native SQL Server steps, the time estimate takes account of the SQL Backup backup step only.) Full and differential restore operations that are scheduled for the future show the icon for restoring a full backup, because SQL Backup cannot determine the backup type. Disabled jobs are not shown in the time line.

You can move your mouse pointer over a block to see more details.

If a database has two or more operations represented on the time line that are too close together to be visually distinguished, the operations are represented by a gray block [image]. You can zoom in to the gray block to see the individual operations. For details of how to zoom in, see Navigating the time line.

If more than one backup or restore operation is scheduled at the same time on the same SQL Server instance, the time slot in which the conflict occurs is highlighted pink, and the conflicting operations are highlighted red. To see a list of all the visible jobs that conflict, right-click in the shaded area.

If a job comprises multiple schedules, the schedule that causes the conflict is also shown in the list. Note that SQL Backup lists only those jobs for the time period that you can currently see in the time line; for a full list of all conflicting jobs for the time slot, ensure that you can see the entire time slot. For details of how to change the time line view, see Navigating the time line. You can click the name of the job or schedule in the list to select it in the Jobs tab.

Navigating the Time Line

The current time is indicated with a yellow marker:

If the current time marker is not visible in the view, a small, yellow arrow is displayed in the top left [image] (if you are looking at future jobs) or the top right [image] (if you are looking at activity history).

You can navigate to the current time by clicking the arrow, or by clicking [image]. For more information on the current time marker, see time settings and locations.

The date corresponding to the left edge of the time line view is displayed above the Registered SQL Servers pane. To change the date, click the arrow and select the date you require from the calendar:

There are a number of ways to scroll through the time line:

You can scroll to a different time or date by clicking the left and right arrow buttons [image] [image] to move in the direction indicated. To scroll more quickly, drag the central slider [image] left or right; the further you drag the slider, the faster the time line moves.

To move the time line and SQL Server Registration pane up or down so that you can see details for databases that are not shown in the view, click the up and down arrow buttons, use the scroll bar, or press Ctrl and rotate your mouse wheel button.

Alternatively, you can move your mouse pointer to an area that does not show any activities and drag the time line to the required position (up, down, left, or right) using the grab hand

To zoom out, drag the Zoom slider to the left or click [image]; to zoom in, drag the Zoom slider to the right or click [image]:

Alternatively, with the mouse pointer over the time line, rotate your mouse wheel button to zoom in or out.

When you perform a backup or restore operation, on completion, SQL Backup automatically scrolls to the activity in the time line. Similarly, when you schedule a job, on completion of the job creation SQL Backup scrolls to the first run of the new job.

Refreshing the data

For the selected SQL Server, and for all databases that are visible in the time line, SQL Backup refreshes the display every minute.

In the example above, the information for the LOCAL SQL Server instance is up-to-date, but the orange shading for SQL2000 and STANDBY shows that they have not been refreshed for approximately one minute.

You can move your mouse pointer over the shaded area to see the precise time at which the SQL Server was last updated. For more details about the different times that are provided, see Time settings and locations.

Red shading [image] is used on the time line to show that the connection to a SQL Server instance has failed. Green shading [image] is used to show that data is being collected for that particular section of the time line.

To refresh the display for a SQL Server instance, click the instance in the Registered SQL Servers pane, and press F5. While the connection to the instance is being refreshed, [image] is displayed. You can continue to interact with the time line while a connection is being refreshed.

If you have registered a large number of instances and associated databases, you can reduce the time it takes to refresh the time line by creating groups and distributing your instances between them; data is not refreshed for collapsed groups.

Time settings and locations

SQL Backup uses locations so that you can manage SQL Servers that are situated in different time zones.

SQL Backup sets up an initial location for you to register your local SQL Server instances. This default location uses the time set on the client computer (the computer on which you are running the graphical user interface). The time zone is reflected in the tab at the top of the time line.

If there is a difference between the location time and the time set on the server (the server time), SQL Backup reflects this in the time line. For example, this may occur if the clocks on your servers have not been synchronized exactly. In the example below, the time on the server has been set to an hour earlier than the location time (in reality, the time difference may often be only a few minutes).

For the default location, the client time and location time are always the same.

Note that if your network is slow, or if your server is overloaded, you may see a difference between the server time and the client time even though their clocks are synchronized, due to the delay in SQL Backup receiving the data.

If you have servers situated in different world time zones, you can set up a separate time line for each time zone so that the current time marker shows the time local to the servers.

To create a new location:

  1. On the File menu, click [image] New Location, or click the blank tab at the top of the time line:

  2. In the New Location dialog box, in the Time zone box, select the time zone in which the servers are located.
  3. If required, in Location name type a name for the location.

    For example:

  4. Click Add Location.

The new location is displayed.

Note that SQL Backup uses the client time to calculate the location time by adding or subtracting the appropriate offset. Therefore, if your client time is incorrect, the location time will be incorrect by the same amount.

SQL Backup takes into account adjustments to time zone differences for daylight saving.

You can add SQL Servers to the new location in the usual way.

To edit or delete a location, click the tab, and on the Edit menu, click Edit or Delete as required. Alternatively you can right-click on the appropriate tab, and then click Edit or Delete. Note that if you delete a location that contains registered SQL Server instances, all instances registered to the location are removed.

Interaction

Click an activity that has completed (to the left of the current server time) to select the corresponding entry in the Activity history.

Double-click a past activity in the time line to see its properties; for details, see "Activity properties" in Activity history.

Click an activity that is scheduled for the future (to the right of the current server time) to select it in the Jobs tab. Note that a number of activities on the time line can correspond to a single job in the Jobs tab.

Double-click a future activity to edit the job. The Edit Backup Job wizard is displayed. This is identical to the Schedule Backup Jobs wizard, but contains the settings for the selected job for you to edit. For details, see Scheduling backup jobs.

Note that not all jobs can be edited using SQL Backup's graphical user interface. For example, restore jobs and filegroup backup jobs must be edited using a SQL application such as SQL Server Management Studio or Enterprise Manager.

You can click an activity in the Activity History, or a job in the Jobs tab, to select it in the time line; selected activities are shown in the time line with shading, for example: for backup operations or [image] for restore operations that have already occurred.

You can right-click an activity to see a context-menu of available features.

See also

The Registered SQL Servers pane

The Activity History

Scheduling backup jobs

The Jobs tab


The Activity History

Select the Activity History tab on the main window to view a history of backup and restore operations.

Select the SQL Server instance or database for which you want to see the activity history in the Registered SQL Servers pane.

To update the details, on the View menu, click [image] Refresh Connection.

Display

In the first column, [image] indicates that the activity was performed using SQL Backup (version 5 or later). If no icon is shown, the activity was performed using an earlier version of SQL Backup, or another program (such as SQL Server native backup).

In the second column, [image] indicates that the backup is encrypted.

Type shows the type of activity. An icon is displayed so that you can scan the list easily:

Full backup | Full restore
Differential backup | Differential restore
Transaction log backup | Transaction log restore
File or filegroup backup | File or filegroup restore

Note that if you have a dark background colour, the colours of the icons do not reverse, but the outline changes. For example:

Full backup

Full restore

Date shows the date and time of the activity in the time zone local to the SQL Server.

Duration shows the time taken to perform the backup or restore operation. Note that this does not include the time taken for any activities done on completion, such as erasing existing files, copying backups to a network share, and sending notification emails.

Uncompressed Size shows the total amount of uncompressed data passed to SQL Backup by SQL Server. This may be larger than the Data size and Log size reported in the database properties because data is passed to SQL Backup in blocks of a minimum of 64 KB. For example, a 512 byte block of data from SQL Server is passed to SQL Backup as a 64 KB block. You can use the sp_spaceused stored procedure if you want to check how much of the data and log file allocation is used; for details, refer to your SQL Server documentation.

Compressed Size shows the size of the backup file(s) on disk.

Compressed By shows the percentage compression; SQL Backup calculates the percentage compression of a backup by comparing the size of the SQL Backup backup with the total database size. For example, if a database comprises a 10 GB data file and a 3 GB transaction log file and SQL Backup generates a full backup of the database to create a backup file that is 3 GB, the compression for this backup is calculated as 77%, [1-(3/13)]x100.

Compression Level shows the compression level used for the backup.

If you chose to verify a backup, Verified displays Yes if the backup files are not corrupted and can be read by SQL Backup for restoring or Failed if any of the backup files are corrupted. If you did not verify the backup, No is displayed. For backups made by SQL Backup version 4 or earlier, this column is blank.

Files shows the total number of files created for a backup, or the number of backup files restored for a restore operation.

User Name displays the login used to access the SQL Server.

The information displayed is extracted from the backupset table in the msdb database. When you add the SQL Server instance to SQL Backup, you can restrict the number of days of data that is extracted from the backupset table. For more information about this table refer to your SQL Server documentation. Note that if you have changed the name of your SQL Server, SQL Backup displays only activities that have occurred since the name was changed.

Interaction

By default, the activities are listed in date and time order, with the most recent first. You can change the sort order by clicking the header for the column by which you want to sort the list. Click the header again to reverse the order. You can also resize the columns as required.

When you click an activity in the Activity History, the time line view changes to show the activity and it is highlighted. Similarly, when you click a completed activity in the time line, it is selected in the Activity History.

To restore a backup, right-click the backup and click Restore. The restore wizard is displayed.

To see a list of the files for a backup or restore operation, right-click the activity in the Activity History and then click Properties, or double-click the activity. For details, see "Activity properties" below. You can view properties only for activities that were performed by SQL Backup version 5 or later.

To view the SQL Backup log file for a backup or restore operation, right-click the activity and click Show Log. The Activity Log dialog is displayed.

Activity properties

To display the properties of a backup or restore operation, right-click the activity in the Activity History, and click Properties, or double-click the activity in the time line or Activity History. Activity properties are available only for activities performed by SQL Backup (version 5 or later).

File Name displays the name of each backup file associated with the backup or restore operation.

In the backup properties, File Size displays the total amount of uncompressed data passed to SQL Backup by SQL Server. This may be larger than the Data size and Log size reported in the database properties because data is passed to SQL Backup in blocks of a minimum of 64 KB. For example, a 512 byte block of data from SQL Server is passed to SQL Backup as a 64 KB block. You can use the sp_spaceused stored procedure if you want to check how much of the data and log file allocation is used; for details, refer to your SQL Server documentation.

In the restore properties:

  • for single file backups, File Size displays the total amount of uncompressed data passed to SQL Backup, as described for backup properties
  • for split file backups, File Size displays the native backup data size of each file

Compressed Size shows the size of the backup file on disk.

Errors and Warnings displays any error or warning messages associated with the selected backup or restore operation.

To view the SQL Backup log file for the backup or restore operation, click Show Log. The Activity Log dialog is displayed.

See also

Activity properties

Compression levels

The In Progress tab

The Jobs tab

The Registered SQL Servers pane

The Time Line

About the graphical user interface


The In Progress tab

The In Progress tab displays the progress of SQL Backup activities that are running for the SQL Server instance or database selected in the Registered SQL Servers pane.

Note that transaction logs that are currently being copied are shown on the Log Copy Queue tab.

The time line also displays the progress of the activity (using striped blocks [image] ).

You can also use the sqbstatus extended stored procedure to view the active SQL Backup processes from within your SQL application (for example, SQL Server Management Studio).

To update the details, on the View menu, click [image] Refresh Connection.

When an activity completes, its details are removed from the In Progress tab, and added to the Activity History.

See also

The Activity History

The Jobs tab

The Registered SQL Servers pane

The Time Line

About the graphical user interface


The Log Copy Queue

When you create a transaction log backup file using SQL Backup, and request that the file is copied to another location (either using the COPYTO keyword, or with the "Copy to location" setting in the SQL Backup wizards), the backup files are placed in the log copy queue before they are copied. SQL Backup checks the log copy queue regularly for transaction log files that are waiting to be copied (see below for details of the copy schedule). Files are then copied in the correct order; log files for lower LSNs are copied first.

If there is a problem during the copying process (caused by an extended network outage, for example) and the copy fails, the files remain on the log copy queue, ready for another copy attempt.

Note that to revert to the old (SQL Backup version 5) copy behavior for transaction log backups, you must edit the backup script and specify the USESIMPLECOPY keyword in the BACKUP command. You cannot specify the old copy behavior directly from the Log Shipping wizard.

Display

The Log Copy Queue tab shows every transaction log backup file in the log copy queue for the SQL Server instance or database that you have selected in the Registered SQL Servers pane.

To update the details, on the View menu, click [image] Refresh Connection (or press F5).

The Log Copy Queue tab shows basic information such as the source database, file name, creation date, and copy location for each file on the queue. The Status column indicates the current copy status of each file:

Pending | The file is due to be copied to the location shown in the Copy Location column. If a previous attempt to copy was unsuccessful, the Last Attempt to Copy column indicates when the last copy attempt occurred. To show a log of all copy attempts for a file, double-click the item. The Copy attempt log is displayed.
Copying | The file is in the process of being copied to the location shown in the Copy Location column. Note that transaction log copy activities are not shown on the In Progress tab.

Interaction

By default, the transaction log backup files are listed in order of creation date, with the most recent first. You can change the sort order by clicking the header for the column by which you want to sort the list. Click the header again to reverse the order. You can also resize the columns as required.

To show a log of all copy attempts for a particular file, double-click the item. The Copy attempt log is displayed.

Log copy schedule

By default, SQL Backup attempts to copy each transaction log backup file as soon as it is added to the log copy queue. If the first copy attempt fails, subsequent copy attempts are made according to the following schedule:

Copy attempt | Copy attempt is made...
1 | immediately
2 | 2 minutes after the 1st copy attempt failed
3 | 4 minutes after the 2nd copy attempt failed
4 | 6 minutes after the 3rd copy attempt failed
5 | 8 minutes after the 4th copy attempt failed
6 | 10 minutes after the 5th copy attempt failed
All subsequent copies | 10 minutes after the preceding copy attempt failed

SQL Backup will continue trying to copy the oldest transaction log backup file every 10 minutes, for up to 24 hours.

You can change the copy schedule by creating and editing registry settings in the following registry folders:

  • For 32-bit machines: HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>
  • For 64-bit machines: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>
Registry key | Type | Description
COPYTO:CopyIntervalInMinutes | DWORD | The initial interval between copy attempts (2 minutes by default).
COPYTO:MaxCopyIntervalCount | DWORD | The number of copy attempts for which the interval to the next copy attempt is increased (5 by default).
COPYTO:ExpiryIntervalInMinutes | DWORD | The maximum time to continue with copy attempts (1440 minutes by default).

The interval between copy attempts after the number of attempts specified by COPYTO:MaxCopyIntervalCount is derived by multiplying COPYTO:CopyIntervalInMinutes with COPYTO:MaxCopyIntervalCount. For example, using the default values, this is 2 x 5 = 10 minutes.

A further registry key, COPYTO:ThreadCount (DWORD) controls the number of transaction log files that can be copied simultaneously. If you want to change the default value of 5, create and edit the registry key. The maximum value for COPYTO:ThreadCount is 10.

See also

About the graphical user interface

Compression levels

The In Progress tab

The Jobs tab

The Registered SQL Servers pane

The Time Line


The Jobs tab

The Jobs tab displays details of SQL Backup jobs that have been scheduled, and jobs that have been scheduled using SQL Server.

Select the SQL Server instance or database for which you want to see the jobs in the Registered SQL Servers pane.

To update the details, on the View menu, click [image] Refresh Connection.

Display

In the first column, [image] indicates that the job was created using SQL Backup (version 5 or later). If no icon is shown, the job was created by an earlier version of SQL Backup and the server components have not yet been upgraded to SQL Backup 5 or later, or the job was created by another program (such as SQL Server native backup).

In the second column, [image] indicates that the backup will be encrypted.

Type shows the type of activity. An icon is displayed so that you can scan the list easily:

Full backup

Differential backup

Transaction log backup

File or filegroup backup

Full, differential, transaction log, file, or filegroup restore

Note that if you have a dark background colour, the colours of the icons do not reverse. For example:

Full backup

Restore

Dates and times are displayed in the time zone local to the selected SQL Server.

If more than one schedule has been created for a job, the Schedules column shows the number of schedules and you can click [image] to see the individual schedules. For example:

If a job has never been run, Outcome shows [image] Unknown.

Interaction

By default, the jobs are listed in order of the date and time of the last run, with the most recent first. You can change the sort order by clicking the header for the column by which you want to sort the list. Click the header again to reverse the order. You can also resize the columns as required.

When you click a job in the Jobs tab, the time line view changes to show the activities that correspond to the job and the activities are highlighted. Similarly, when you click a future activity in the time line, its corresponding job is selected in the Jobs tab.

To edit a SQL Backup job, right-click the job and click Edit, or double-click the job. The Edit Backup Job wizard is displayed. This is similar to Schedule Backup Jobs; the wizard contains the settings for the selected job. You can edit only one job at a time. The edit feature is not available for jobs that do not display the [image] icon. Note that some types of SQL Backup jobs (for example, filegroup backup jobs and restore jobs) cannot be edited using the graphical user interface.

To delete a job, right-click the job and click Delete.

To run a job, right-click the job and click Start.

To stop a job that is running, right-click the job and click Stop. If you click Stop for a job that is not currently running, an error will be displayed.

SQL Server Agent

If the SQL Server Agent for the selected SQL Server instance is not running, a message is displayed at the top of the list, and the list is grayed:

You can start the SQL Server Agent by clicking the Start SQL Server Agent link.

See also

The Registered SQL Servers pane

The Time Line

The Activity History

Scheduling backup jobs


Backing up and restoring

In this chapter

  • Creating backups
  • Scheduling backup jobs
  • Configuring log shipping
  • Restoring backups
  • Object level recovery


Creating backups

SQL Backup provides a wizard to guide you through the process of creating a backup. To start the Back Up wizard, click [image] Back Up.

The Back Up wizard comprises the following steps:

Step 1: | Select the SQL Server, and optionally choose a previously saved template.
Step 2: | Select the type of backup, and the database that you want to back up.
Step 3: | Specify the backup file locations (including any network copies), and set options for existing backup files.
Step 4: | Specify compression, encryption, and optimization options, and actions required on completion.
Step 5: | Review the backup summary, and start the backup process.

See also

Configuring log shipping

Creating backups: file settings

Creating backups: processing and encryption settings

Creating backups: review summary

Creating backups: select the backup type and databases

Creating backups: specify SQL Server

Scheduling backup jobs


Scheduling backup jobs

SQL Backup provides the Schedule Backup Jobs wizard to guide you through the process of setting up scheduled SQL Server Agent backup jobs that use SQL Backup to perform the backup.

You can use the Schedule Backup Jobs wizard to generate a SQL script for backing up multiple databases. You can then view this script and use it as a basis for your own multiple database backup scripts.

The Schedule Backup Jobs wizard automatically sets up a job step using the RAISERROR command so that the SQL Server Agent reports an error if the SQL Backup command fails. For information about SQL Server Agent jobs, steps, and the RAISERROR command, refer to your SQL Server documentation.

To start the Schedule Backup wizard, click [image] Schedule Backup Jobs. The Schedule Backup Jobs wizard comprises the following steps:

Step 1: | Select the SQL Server, and optionally choose a previously-saved template.
Step 2: | Select the types of backup you want to perform and the databases that you want to back up.
Step 3: | Define the details of the job schedules.
Step 4: | Specify the backup file locations (including any network copies), and set options for existing backup files.
Step 5: | Specify compression, encryption, and optimization options, and actions required on completion.
Step 6: | Review the backup summary, and create the backup jobs.

You can edit a scheduled job by right-clicking the job in the Jobs tab, and then clicking Edit. The Edit Backup Job wizard is displayed. This is similar to the Schedule Backup Jobs wizard; the wizard contains the settings for the selected job. Note that you can edit only one job at a time.

See also

Scheduling backups: create backup schedules

Scheduling backups: file settings

Scheduling backups: processing and encryption settings

Scheduling backups: review summary

Scheduling backups: select the backup type and database

Scheduling backups: specify SQL Server

Configuring log shipping

Creating backups


Configuring log shipping

SQL Backup provides a wizard to guide you through the process of setting up log shipping, to ship transaction logs from a primary server to a secondary server. For example, you may want to do this if you are maintaining a 'standby' server as a backup to your primary server. For more information about this, see Using SQL Backup log shipping to maintain a standby server.

You use SQL Backup to perform regular backups of the primary server, and restore them on the secondary SQL Server.

In the log shipping process:

  1. Transaction log backups are performed on the source database [image] on the primary SQL Server (for example the production database).
  2. The backup files are copied to a network share [image] that can be accessed by both SQL Servers.

    If the copying process fails for a backup file (due to an extended network outage, for example), SQL Backup automatically attempts to copy the file to the network share at regular intervals. Any future transaction log backups that are created are queued behind the file that could not be copied. Once the copying process is working again (because the network outage has been fixed, for example), SQL Backup copies each transaction log backup to the network share, in the correct order.

  3. The backup files are restored to the destination database [image] on the standby SQL Server.

The database must use the full or bulk-logged recovery model.

The wizard sets up SQL Server Agent backup jobs that use SQL Backup to perform the backup and restore operations.

When you use SQL Backup to ship your transaction logs, the transaction log backups are not immediately restored on the secondary server; you specify a time delay (recommended) or you can specify a separate schedule for the restore operation.

By default, the first time the log shipping job is run, SQL Backup creates a full backup of the source database and restores it on the destination SQL Server prior to creating the log shipping tasks. This is to ensure that the destination database is in the correct state for restoring the transaction log backups. However, you can configure SQL Backup to skip this backup and restore; this is useful if you have already set up a destination database that is ready to receive transaction logs.

Note that you can use the Log Shipping wizard with remote SQL Servers only if SQL Backup Pro is installed on both of the SQL Servers.

You cannot ship transaction logs from a later version of SQL Server to an earlier version (for example, from SQL Server 2008 to SQL Server 2005), because of the differences in their structures. You can ship transaction logs from earlier versions of SQL Server to later versions, but in this case the destination server must use the non-operational recovery completion state (RESTORE WITH NORECOVERY). This is a SQL Server restriction.

Additionally, you cannot ship transaction logs created on a SQL Server instance that has version 6 SQL Backup server components installed, to an instance that has an earlier version of the server components installed. This is because of differences in the SQL Backup (.sqb) file format introduced in SQL Backup 6. In this scenario, you should update the destination SQL Server instance with the latest SQL Backup server components before configuring log shipping.

To start the Log Shipping wizard, click [image] Log Shipping. The Log Shipping wizard comprises the following steps:

Step 1: | Select the database that you want to back up, and the database on which you want to restore the transaction log backups.
Step 2: | Specify the backup file location and settings for the transaction log backup, and set options for existing backup files.
Step 3: | Choose the settings for restoring transaction log backups.
Step 4: | Specify the network share to which the transaction log backups will be copied, and network resilience settings.
Step 5: | Specify the schedule for backing up the transaction logs, and for restoring the backups.
Step 6: | Review your log shipping settings.

See also

Scheduling backup jobs

Log shipping: backup settings

Log shipping: network share

Log shipping: restore settings

Log shipping: review summary

Log shipping: specify source and destination database

Creating backups


Restoring backups

SQL Backup provides a wizard to guide you through the process of Restoring Backups. To start the Restore wizard, click [image] Restore, or in the Activity History, right click the backup and click Restore.

The Restore wizard comprises the following steps:

Step 1: | Select the database backups to restore. If necessary, enter the passwords for encrypted backups.
Step 2: | Specify the database to which you want to restore the backups.
Step 3: | Configure your settings for restoring the backups.
Step 4: | Review the restore summary, and start the restore process.

Restoring multiple backups

You can restore multiple full, differential, and transaction log backups that belong to the same backup set using the graphical user interface. For example, you can restore a full and differential backup for the same database at the same time. Details of how to use the graphical user interface to do this are given above.

You can restore multiple transaction log backups using the SQL Backup command line or extended stored procedure. To do this, use wildcard characters to enter a 'file search pattern' when you specify the DISK argument for the RESTORE command. The backup files that match the wildcard characters must belong to the same backup set.

You can restore multiple encrypted transaction log backups using the SQL Backup toolkit only if they all have the same password.

To restore transaction log backups, your database must be in an unrecovered state in order that the transaction logs can be restored to it, that is, it must have been restored using the NORECOVERY or STANDBY option. SQL Backup ensures that the backups are restored in the correct sequence. For example:

SQLBackupC -SQL "RESTORE LOG pubs FROM DISK = 'C:\Backups\Pubs\Logs\*.*' "

To specify a number of folders, use the DISK command repeatedly:

SQLBackupC -SQL "RESTORE LOG pubs FROM DISK = 'C:\Backups\Pubs\Logs\*.*', DISK = 'E:\OtherBackups\Pubs\Logs\*.*' "

Restoring the master database

To restore the master database, you must use the SQL Backup command line to ensure that the SQL Server is started in single user mode.

  1. Stop the SQL Server service.
  2. Open a command prompt window, and navigate to the SQL Server instance's Binn folder.
  3. Start SQL Server in single user mode using sqlservr.exe c m for an unnamed instance, or sqlservr.exe c m s {instancename} for a named instance.
  4. Use the SQL Backup command line interface to restore the master database.

    For example, at the command prompt, type:

    SQLBackupC -SQL "RESTORE DATABASE master FROM DISK = 'C:\Backups\master 20070101.sqb'"

    For a named instance, use the -I parameter to specify the instance name, for example:

    SQLBackupC -SQL "RESTORE DATABASE master FROM DISK = 'G:\Backups\master 20070101.sqb'" -I Instance2

    SQL Backup restores the master database, and SQL Server automatically stops the SQL Server service when the restore operation is complete.

  5. You can now start the SQL Server in normal mode.

Restoring individual database objects

If you want to restore individual objects from a SQL Backup .sqb file, use SQL Object Level Recovery Pro. This application, included with SQL Backup Pro, can recover individual objects to a database, with potentially large savings in time and disk space. For more information, see About object level recovery.

See also

Restoring backups: destination database

Restoring backups: restore options

Restoring backups: review summary

Restoring backups: select backups

Scheduling backup jobs

The RESTORE command

Toolkit syntax examples

Using the command line

Using the extended stored procedure

Toolkit parameters

Recovering objects


Object level recovery

In this chapter

  • About object level recovery
  • Recovering objects
  • Limitations


About object level recovery

Sometimes you may need to restore individual database objects rather than a complete backup. For example, a user may have accidentally dropped a lookup table, or deleted important data from a production database.

To restore the table from a backup, you would normally have to restore the entire database to an appropriate test server, and then produce a table creation script and data insert statements to recreate the table in the production database. For large databases, the full restore may take a long time, and could require a lot of free disk space on the test server.

SQL Object Level Recovery Pro (included with SQL Backup Pro) enables you to extract individual database objects from a backup file, and recover them to a database of your choice.

By recovering only the objects you specify, SQL Object Level Recovery Pro can save you a considerable amount of time when compared to restoring a full backup. You can also save space, as you do not need to restore a complete database.

  • For an overview of how to use SQL Object Level Recovery Pro, see Recovering objects.
  • SQL Object Level Recovery Pro has some limitations on the type of objects it can recover, and the type of backup files it can recover objects from. See Limitations for more information.


Recovering objects

This topic describes the basic steps to recover objects from a backup file using SQL Object Level Recovery Pro, including tips to help you get the most from the application.

Starting SQL Object Level Recovery Pro

You can start SQL Object Level Recovery Pro:

  • from SQL Backup Pro

    Right-click a server or database in the Registered SQL Servers pane, and select Object Level Recovery.

    This option is not available if the server you have selected does not have a valid SQL Backup Pro license.

  • from Windows Explorer

    The application is installed to C:\Program Files\Red Gate\SQL Backup 6\SQBObjectLevelRecovery\SQLObjectLevelRecoveryPro.exe by default.

    You can copy the contents of the SQBObjectLevelRecovery folder to any server, and run SQL Object Level Recovery Pro independently of SQL Backup. This is useful, for example, if you are recovering objects from a large backup, and want to avoid streaming large amounts of data across the network. The server you are recovering objects to must have a valid SQL Backup Pro license.

  • from the Windows Start menu

You can recover objects in four steps:

  1. Select a destination database
  2. Select a backup source
  3. Select objects to recover
  4. Recover the objects

1. Select a destination database

  • if you have already chosen a database in SQL Backup Pro, this is pre-selected as the destination database; you can continue to step 2 to select a backup source
  • the server you select must be licensed for use with SQL Backup Pro
  • the destination database must be open, and in a suitable state for receiving recovered objects

    For example, a database that is currently being restored cannot be used as a destination database for object level recovery.

  • you must have appropriate security permissions to create objects in the destination database

2. Select a backup source

  • you can add full backup files or differential backup files (SQL Backup .sqb files only)

    If you add a differential backup file, you will also need to add the related full backup file.

  • you can add files for backups that are split across several files

    You must add every file from the backup set.

  • if there are problems validating the file or backup (a [image] symbol is shown in the OK column), select the file or backup to see a detailed error message

3. Select objects to recover

  • SQL Object Level Recovery Pro does not automatically check for dependencies between objects, but objects are recovered in an order that reduces the possibility of errors caused by missing dependents.

    Depending on the contents of the destination database, you may have to select several objects to ensure that the necessary object dependencies are maintained.

  • the Data tab (available for table objects only) shows the first 100 rows of data in a table

    Data for certain data types (for example, varchar and nvarchar) may not be shown if it is too large to display.

  • View Recovery Script displays a complete object and data recovery script for the objects you have selected
  • SQL Object Level Recovery Pro does not support all possible arguments of the CREATE TABLE statement

    For more information, see Limitations.

4. Recover the objects

  • when you click Recover, the Recovering Objects dialog shows the status of each object you selected for recovery

    Select objects in the Progress list to show a detailed error message for objects that could not be recovered. These objects are marked with a

  • SQL Object Level Recovery Pro will not alter or overwrite existing objects in the destination database

    See Limitations for more information.

  • object level recovery activities are not shown on the SQL Backup time line, or on the SQL Backup Activity History tab


Limitations

SQL Object Level Recovery Pro has some limitations. For example, dependencies between objects are not handled automatically, and some features of SQL Server tables are not supported. The following sections provide more detailed information about these limitations:

  • Existing objects
  • Object dependencies
  • Supported backup types
  • Supported object types
  • Supported data types
  • Supported CREATE TABLE arguments

Refer to your SQL Server documentation for detailed information about specific object types, data types, and table arguments.

For more complex recovery scenarios, you should consider using Red Gate SQL Compare and SQL Data Compare. These enable you to compare the contents (object schema, and data) of SQL Backup .sqb files with a live database, and then synchronize the database with the backup file contents while maintaining object dependencies.

Existing objects

Objects that already exist in the destination database will not be modified or overwritten by SQL Object Level Recovery Pro. Attempting to recover such objects results in an error.

It is usually safer to recover an object to a test or staging database first, and then transfer the object to its final destination database manually. If you want to recover an object directly from a backup file to its final destination database, you will have to drop the object first. Make sure you have a recent valid backup of the object before you drop it.

Object dependencies

Objects you attempt to recover may have dependencies on other objects in the destination database. For example, a view may refer to several tables; successful recovery of the view depends on these tables being present in the destination database.

SQL Object Level Recovery Pro does not attempt to resolve dependencies automatically. To avoid dependency errors, you may need to recover multiple dependent objects together.

If you have selected objects of more than one type, they are recovered in the following order:

  1. SCHEMA
  2. TYPE (user defined type)
  3. XML SCHEMA COLLECTION
  4. FUNCTION
  5. TABLE
  6. VIEW
  7. PROCEDURE (stored procedure)

Recovering objects in this order reduces the possibility of failures caused by dependencies on missing objects.

Supported backup types

You can use SQL Object Level Recovery Pro with full backups and differential backups. To recover objects from a differential backup, you will also need to provide the associated full backup.

SQL Object Level Recovery Pro does not support:

  • filegroup backups
  • transaction log backups
  • backups from databases that use Transparent Data Encryption (TDE)
  • native SQL Server backups (.bak files)

Supported object types

Object types marked [image] can be recovered from SQL Backup .sqb files. Other object types are not supported.

Object type | Supported
ASSEMBLY |
ASYMMETRIC KEY |
CERTIFICATE |
CONTRACT |
DEFAULT |
EVENT NOTIFICATION |
FULLTEXT CATALOG |
FULLTEXT STOPLIST |
FUNCTION |
INDEX |
MESSAGE TYPE |
PARTITION FUNCTION |
PARTITION SCHEME |
PROCEDURE (stored procedure) |
QUEUE |
REMOTE SERVICE BINDING |
ROLE |
ROUTE |
RULE |
SCHEMA |
SERVICE |
SYMMETRIC KEY |
SYNONYM |
TABLE |
TRIGGER |
TYPE (user defined type) |
USER |
VIEW |
XML SCHEMA COLLECTION |

Supported data types

Table data with types marked [image] can be recovered from SQL Backup .sqb files.

Group | Data type | Supported
Exact numerics | bit |
 | tinyint |
 | smallint |
 | int |
 | bigint |
 | numeric |
 | decimal |
 | smallmoney |
 | money |
Approximate numerics | float |
 | real |
Date and time | datetime |
 | smalldatetime |
 | date |
 | time |
 | datetimeoffset |
 | datetime2 |
Character strings | char |
 | varchar / varchar(max) |
 | text |
Unicode character strings | nchar |
 | nvarchar / nvarchar(max) |
 | ntext |
Binary strings | binary |
 | varbinary / nvarbinary(max) |
 | image |
Other data types | sql_variant |
 | timestamp |
 | uniqueidentifier |
 | xml |
CLR data types | hierarchyid |
Spatial data types | geometry |
 | geography |

Supported CREATE TABLE arguments

CREATE TABLE arguments marked [image] are supported. All other CREATE TABLE arguments are ignored. For example, if the table to be recovered includes a FOREIGN KEY ... REFERENCES argument, this will not be created in the recovered table.

Argument | Supported
ALLOW_PAGE_LOCKS |
ALLOW_ROW_LOCKS |
CLUSTERED |
COLLATE |
computed_column_expression |
CONSTRAINT |
CONTENT |
DATA_COMPRESSION |
DEFAULT |
DOCUMENT |
FILESTREAM_ON |
FOREIGN KEY ... REFERENCES |
IDENTITY |
IGNORE_DUP_KEY |
NONCLUSTERED |
NOT FOR REPLICATION |
NULL |
ON filegroup |
ON partition scheme |
ON DELETE |
ON UPDATE |
PAD_INDEX |
PERSISTED |
PRIMARY KEY |
RANGE |
ROWGUIDCOL |
SPARSE |
STATISTICS_NORECOMPUTE |
TEXTIMAGE_ON |
UNIQUE |
WITH FILLFACTOR |
XML COLUMN_SET FOR ALL_SPARSE_COLUMNS |


Tools and utilities

In this chapter

  • Compression analyzer
  • Reporting
  • SQL Backup File Converter


Compression analyzer

You can use the SQL Backup Compression Analyzer to compare the effectiveness of different compression levels for full database backups on a selected database.

From within the SQL Backup graphical user interface, you can launch the Compression Analyzer:

  • from Step 4 of the Back Up wizard
  • from Step 5 of the Schedule Backup Jobs wizard
  • from the Tools > Utilities menu

SQL Backup automatically selects the SQL Server that you have already chosen in the Registered SQL Servers pane or in the wizard.

If you are not using the graphical user interface, you can launch the Compression Analyzer by running the file CompressionAnalyzer.exe; this file is located in your SQLBackup folder. You can run the program on any server on which you have installed the SQL Backup server components. In the Connect to SQL Server dialog box, type or select the name of the SQL Server. Then select the authentication method, and for SQL Server authentication enter the User name and Password. For Windows authentication, the credentials of the current user are used.

The Compression Analyzer is displayed. Select the database for which you want to compare compression levels, and click Start Test.

If you want stop the analysis, for example because the database you have chosen is large and the analysis is taking too long, click Stop. However, note that results are based only on the data that has been sampled so far; for a fully accurate result you must allow the analysis to finish.

The results of the analysis are displayed in a tab.

If you stopped the analysis before its completion, the Data processed bar indicates the proportion of data was analyzed.

For each compression level, the percentage of compression is shown under Compression Ratio. In the above example, Level 4 produced the best compression. For more information about compression levels and compression ratios, see Compression levels.

You can then select another database to analyze, if required. The results for the second database are shown in a separate tab, so that you can compare the results. To delete a tab, right-click the tab header and click Delete.

To analyze a database from a different SQL Server, click Change and specify the details of the SQL Server.

See also

Getting started

Creating backups

Compression levels

Scheduling backup jobs


Reporting

SQL Backup provides the Reporting feature for you to generate reports on completed backups that have been performed by SQL Backup version 5 or later. You can create the following reports on a selected SQL Server instance or instances, within specified dates:

  • All backup history lists all backups that have completed.

    This includes successful backups, backups that have warnings associated with them, and failed backups.

    For reports on multiple SQL Servers, this includes only backups performed by SQL Backup version 5 or later.

  • Backups with warnings lists all completed backups that have warnings associated with them.
  • Failed backups lists all completed backups that have failed.

    This includes only backups performed by SQL Backup version 5 or later.

On the Tools menu, click Reporting.

In Report scope, choose:

  • Single Server to generate a report on activities that have occurred on one SQL Server
  • Multiple Servers to generate a combined report on a number of SQL Servers

    To report on multiple SQL Servers, SQL Backup creates a reports database on a SQL Server that you specify.

Note that if the selected SQL Server does not have the server components installed, a warning is displayed and you must install the server components to proceed.

Single server

For Single Server, select the type of report that you want to generate, and select the SQL Server instance for which you want to create the report. Then, specify the dates (inclusive) between which you want the activities to be reported.

Click Generate Report. The report is displayed in the Report Viewer. You can print or save the report as required.

Multiple servers

For Multiple Servers, SQL Backup must create a reports database to collate the data from the SQL Server instances that you choose to report on.

Under Reports database settings, select the SQL Server for the reports database. If you have not yet created a reports database, click Create New Reports Database, type a name for the database, and click Create Database. If you have already created a reports database, you can select it from Reports database.

Under Report configuration, select the type of report you want to generate.

When you first create a reports database, the selected SQL Server is automatically added to the list of Servers to report on. You cannot remove this SQL Server from the list, but you can clear the check box if you do not want to include it in the report. Any 'linked' SQL Servers are also listed; you can deselect or remove these if required. For more information about linked servers, refer to your SQL Server documentation.

To add another SQL Server, click Add. Then, in the Add Linked Server dialog box, type the name of the SQL Server in the Server box, and click Add.

By default, SQL Backup updates the data collated in the reports database before generating the report. If you do not want the data to be updated, for example if you know it is already up-to-date, clear the Update activity data before generating report check box.

Then, specify the dates (inclusive) between which you want the activities to be reported.

Click Generate Report. The report is displayed in the Report Viewer. You can print or save the report as required.

See also

Compression analyzer

SQL Backup File Converter

The Activity History


SQL Backup File Converter

Use the SQL Backup File Converter (available as a command line application, or as a GUI application) to convert SQL Backup files (.sqb) to Microsoft Tape Format (MTF) files (.bak). You can use the native SQL Server RESTORE command to restore MTF files.

You can also use the SQL Backup File Converter to convert SQL Backup v6 files to SQL Backup v5 files; both file types have extension .sqb.

You can distribute the SQL Backup File Converter as required; you do not need to install SQL Backup in order to use it.

Using the SQL Backup file conversion utility (command line)

The executable for the SQL Backup conversion command-line utility is SQBConverter.exe. It is located in the folder in which you installed SQL Backup, for example:

C:\Program Files\Red Gate\SQL Backup 6

To convert a split backup to MTF format, use SQBConverter.exe to convert each of the backup files in turn. You can then restore the MTF format files as a group of split backup files.

Syntax

SQBConverter inputfile outputfile [password] [/sqb]

Arguments

inputfile

The name of the SQL Backup backup file that you want to convert.

outputfile

The name for the converted file.

password

The password for the SQL Backup backup file that you are converting, if the file is encrypted.

Switches

/sqb

Convert from a SQL Backup 6 compatible file to a version 5 compatible file. Note that both version 6 and version 5 SQL Backup files have a .sqb extension.

Using the SQL Backup file conversion utility (graphical user interface)

SQBConverterGUI.exe is a graphical user interface for the SQL Backup File Converter. It is located in the folder in which you installed SQL Backup, for example:

C:\Program Files\Red Gate\SQL Backup 6

You can start the user interface from the main SQL Backup menu (Tools > Utilities > SQL Backup File Converter).

To convert SQL Backup files to SQL Backup version 5 (.sqb) or Microsoft Tape Format (.bak):

  1. From the Convert SQL Backup files to list, select the file format that you want to convert to:
  • Choose Microsoft Tape Format (.bak) to convert version 6 or version 5 SQL Backup files (.sqb) to native Microsoft Tape Format files (.bak).
  • Choose SQL Backup 5 (.sqb) to convert version 6 SQL Backup files (.sqb) to version 5 SQL Backup files (.sqb).
  1. Select the required Conversion Options to control how the converted files are named and saved.
  2. Click Add Files and browse for the files that you want to convert.

    Valid SQL Backup files are shown with a Conversion status of [image] Pending. If there is a problem with a file, it is shown with a Conversion status of [image] Error. Click the file to view more information about the problem.

    Encrypted files are marked with a padlock icon [image]. If you have added encrypted files, you must also enter the password (in the Password box beneath the file list). The same password will be used to decrypt every encrypted file.

  3. Click Convert.

    Each file in the list is converted and saved according to the conversion options you have selected.

    Successfully converted files are shown with a Conversion status of [image] Successful.

    Files that could not be converted are shown with a Conversion status of [image] Error. Click the file to view more information about the problem.


Settings and options

In this chapter

  • File management options
  • Email notification options
  • Activity cache location


File management options

You can define a number of options for file management that are used throughout SQL Backup. For example, you can specify the default folder for backup files.

On the Tools menu, click Server Options. The File Management tab is selected by default.

In the SQL Server box, select the name of the SQL Server instance for which you want to set the options.

Note that if the selected SQL Server does not have the server components installed, a warning is displayed and you must install the server components to proceed.

Backup file locations

You can set up a default folder and file name format for your backups, so that you do not have to enter the same information repeatedly when you create backups or backup jobs using the graphical user interface.

The settings are also used when you use the BACKUP command with the SQL Backup toolkit.

In the Backup folder box, type the path for the folder in which you want your backups to be created by default, or click [image] to browse to the location. You can use tags in the path if required. For example, if you want the backup files for each database to be created in a separate folder, you can specify the <DATABASE> tag:

C:\MyBackups\<DATABASE>

For details of the tags you can use, see File location tags.

If you do not specify a location, SQL Backup uses the default SQL Server backup folder, which is usually C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP.

In the File name format box, define the default file name for backup files using the tags. The default is:

<TYPE>_<INSTANCE>_<DATABASE>_<DATETIME yyyymmdd_hhnnss>

For example, for a full database backup of the SalesData database running on the default SQL Server instance, the form of the backup file name would be:

FULL_(local)_SalesData_20090329_005042.sqb

where the numbers show the date and time of the backup. To reset the file name format to its default value, click Reset.

Your settings will be used the next time you use a backup wizard or the BACKUP command. The settings are saved on your computer.

SQL Backup log files

When SQL Backup backs up or restores a database, the details of the operation are sent to a log file. You can use the Options dialog box to specify the default folder for SQL Backup log files, and set up SQL Backup to delete the log files at regular intervals. You can also set up SQL Backup to delete the SQL Server backup history at intervals.

These settings are used when you use the graphical user interface or the BACKUP command to create a backup.

In the Log file folder box, type the path for the folder in which you want your log files to be created by default, or click [image] to browse to the location. You can use tags to define the path. For optimal performance, you are recommended to use a dedicated folder for the SQL Backup log files. By default, the primary log file is created in the folder:

C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log

If you want SQL Backup to delete old log files automatically, select the Delete log files in this folder check box, then type or select the age (in days or hours) of the files that you want to delete.

If you are using the SQL Backup toolkit, you can automatically create a copy of the log file in a different location using the BACKUP command LOGTO argument; to delete these files in addition to the primary log files, select the Delete log files in LOGTO folders check box. For more information, see The BACKUP command.

SQL Server backup history

If you want SQL Backup to delete the SQL Server backup history in the msdb database, select the Delete old backup history check box, then type or select the age (in days or hours) of the files that you want to delete.

SQL Backup uses the stored procedure msdb..sp_delete_backuphistory to delete the history. Note that this deletes all backup and restore operation history, including backup and restore activities that were not performed using SQL Backup.

See also

Creating backups

File location tags

Email notification options

The BACKUP command


Email notification options

SQL Backup can send an email to specified addresses whenever there are errors or warnings, or only if an error occurs, or for any outcome (success or failure).

To make this option available, you must enter the email settings to be used. You can then specify the recipients of the email when you set up the backup or restore operation:

  • in the graphical user interface, you specify the email addresses in the backup, scheduled backup, restore, or log shipping wizard
  • in the SQL Backup toolkit, you use the BACKUP and RESTORE command options MAILTO, MAILTO_ONERROR, and MAILTO_ONERRORONLY

Note that you cannot use email notification if SQL Backup Lite is installed on the SQL Server.

On the Tools menu, click Options. In the SQL Server box, select the name of the SQL Server instance for which you want to set up email notification, and then click Email Settings.

In the SMTP host box, type the name of the SMTP server that SQL Backup is to use when sending emails, and in the Port box, type or select the number of the port on the SMTP server (defaults to 25).

If your server requires authentication, type the user name in User name, then type your password in the Password box.

In the Send from box, type the email address from whom the email will be sent. If you leave this blank, SQL Backup will use the default email address for the sender SQLBackup@ComputerName.

To send a test email to ensure your settings are correct, click Send Test Email, and enter an email address for the recipient. When you click OK, the test email is sent.

See also

Restoring backups

Creating backups

File management options

Scheduling backup jobs

The BACKUP command


Activity cache location

The activity cache stores backup and restore history for your registered SQL Servers. The default cache location is:

C:\Documents and Settings\<user name>\Local Settings\Application Data\Red Gate\SQL Backup\Server Data

On Windows Vista or Windows 2008, the default cache location is: C:\Users\<user name>\AppData\Local\Red Gate\SQL Backup\Server Data

As the cache increases in size, this can cause problems with roaming profiles and disk quotas.

You may be able to resolve these problems by changing the cache location (for example, to a network share that does not form part of your roaming profile, or to a disk on which you have a larger storage quota).

To change the location of the activity cache: on the Tools menu, click Activity Cache Location.

When you click OK, SQL Backup remembers the new location for the activity cache, but will not start using this location until you restart the SQL Backup GUI.

To avoid a delay when restarting, you should manually copy the contents of the Server Data folder to the new location.

Reducing the amount of data in the activity cache

If you cannot find a suitable location for the activity cache, you can reduce the amount of data it contains. To do this:

  • when you add the SQL Server instance to SQL Backup, filter the native backup and restore history using the option Native backup and restore history to import, so that you import only recent history
  • use the option Delete old backup history in File management options to delete backup and restore history from the msdb database and the local cache


The SQL Backup Toolkit

In this chapter

  • Using the command line
  • Using the extended stored procedure
  • Toolkit parameters
  • Toolkit syntax
  • CONVERT command


Using the command line

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.

To enter a command using the command line interface, you use a subset of the TransactSQL BACKUP and RESTORE statements. For example, to back up the pubs database, you use the following command:

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

For details of the parameters you can use with the command line, see Toolkit parameters.

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.

See also

The BACKUP command

The CONVERT command

The RESTORE command

The RESTORE SQBHEADERONLY command

Toolkit parameters

Installation overview

Using the extended stored procedure


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.

See also

The BACKUP command

The RESTORE command

The RESTORE SQBHEADERONLY command

Toolkit parameters

Toolkit syntax examples

Using the command line

Installation overview


Toolkit parameters

To run SQL Backup from the command line, you use a command of the format:

>SQLBackupC parameters

To run SQL Backup using the extended stored procedure, you use a command of the format:

master..sqlbackup 'parameters'

The parameters you can use are described in this topic.

Connection parameters

-U SQL_Server_user_name

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 password to use to log in if you are using SQL Server authentication (command line only). For example:

-P MyPassword

-I

The name of the SQL Server instance to which you want to log on. 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 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 topics in the SQL Syntax section of the Help:

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

Some examples are provided in Toolkit syntax examples.

-USE template_name

Indicates that you want to load the configuration values from the specified template. You save the template using the graphical user interface 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 backs up the pubs database on the SQL Server's default instance using Windows authentication:

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

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

>SQLBackupC -SQL "BACKUP DATABASE pubs TO DISK ='C:\Backups\pubs.sqb' WITH PASSWORD = 'MyPassword'" -I ServerInstance2 - U sa -P MyPassword

See also

The CONVERT command

The RESTORE command

The RESTORE SQBHEADERONLY command

Toolkit syntax examples

Using the command line

The BACKUP command

Creating backups


Toolkit syntax

In this chapter

  • The BACKUP command
  • The RESTORE command
  • The RESTORE SQBHEADERONLY command
  • The CONVERT command
  • Toolkit syntax examples


The BACKUP command

Use the BACKUP command with the SQL Backup -SQL parameter to back up one or more databases, transaction logs, or filegroups.

  • Syntax provides the syntax for the command; additional arguments and extended syntax are highlighted.
  • Extended arguments describes the arguments that are provided by SQL Backup; for information about the standard arguments, refer to your SQL Server documentation.

Some example snippets are included in this topic. For more detailed examples, see Toolkit syntax examples. Note that the extended stored procedure expects only one parameter, which must be delimited by single quotes. Therefore, wherever a single quote is used for the parameters below, for the extended stored procedure you must use two single quotes so that SQL Server does not interpret it as a string delimiter.

Syntax

Note that the following arguments are available only with SQL Server 2005 and SQL Server 2008 instances:

CHECKSUM / NO_CHECKSUM
CONTINUE_AFTER_ERROR / STOP_ON_ERROR
COPY_ONLY

The READ_WRITE_FILEGROUPS keyword (used to create partial backups) is also available only with SQL Server 2005 and SQL Server 2008 instances.

Backing up a database to a single file or split files

BACKUP DATABASE { database_name }
< file_or_filegroup > [ ,...n ]
TO { DISK } = { 'physical_backup_device_name' | '<AUTO>' } [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] COMPRESSION = { 0 | 1 | 2 | 3 | 4 } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] COPY_ONLY ]
[ [ , ] COPYTO = { 'target_folder_name' } [,...n] ]
[ [ , ] DESCRIPTION = { 'text' } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] DISKRETRYCOUNT = { n } ]
[ [ , ] DISKRETRYINTERVAL = { n } ]
[ [ , ] ERASEFILES | ERASEFILES_ATSTART = { days | hours{h} | keep{b} } ]
[ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
[ [ , ] FILECOUNT = { n } ]
[ [ , ] FILEOPTIONS = { 1 | 2 | 3 | 4 | 5 | 6 | 7 } ]
[ [ , ] INIT ]
[ [ , ] KEYSIZE = { 128 | 256 } ]
[ [ , ] LOGTO = { 'target_folder_name' | 'file_name' } ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MAXDATABLOCK = { 65536 | 131072 | ... | 2097152 } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] MIRRORFILE = { 'physical_backup_device_name' } ] [ ,...n ]
[ [ , ] NAME = { 'backup_set_name' } ]
[ [ , ] NOCOMPRESSWRITE | NOWRITE ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] SINGLERESULTSET ]
[ [ , ] THREADCOUNT = { n } ]
[ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
[ [ , ] USESIMPLECOPY ]
[ [ , ] VERIFY ]
]

Backing up multiple databases to single files or split files

BACKUP DATABASES [EXCLUDE] { '[ list_of_databases ]' }
TO { DISK } = { 'physical_backup_device_name' | '<AUTO>' } [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] COMPRESSION = { 0 | 1 | 2 | 3 | 4 } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] COPY_ONLY ]
[ [ , ] COPYTO = { 'target_folder_name' } [ ,...n ] ]
[ [ , ] DESCRIPTION = { 'text' } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] DISKRETRYCOUNT = { n } ]
[ [ , ] DISKRETRYINTERVAL = { n } ]
[ [ , ] ERASEFILES | ERASEFILES_ATSTART = { days | hours{h} | keep{b} } ]
[ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
[ [ , ] FILECOUNT = { n } ]
[ [ , ] FILEOPTIONS = { 1 | 2 | 3 | 4 | 5 | 6 | 7 } ]
[ [ , ] INIT ]
[ [ , ] KEYSIZE = { 128 | 256 } ]
[ [ , ] LOGTO = { 'target_folder_name' | 'file_name' } ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MAXDATABLOCK = { 65536 | 131072 | ... | 2097152 } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] NAME = { 'backup_set_name' } ]
[ [ , ] NOCOMPRESSWRITE | NOWRITE ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] SINGLERESULTSET ]
[ [ , ] THREADCOUNT = { n } ]
[ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
[ [ , ] USESIMPLECOPY ]
[ [ , ] VERIFY ]
]

Creating a partial filegroup backup

BACKUP DATABASE { database_name }
READ_WRITE_FILEGROUPS [, <read_only_filegroup> [ ,...n ] ]
TO { DISK } = { 'physical_backup_device_name' | '<AUTO>' } [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] COMPRESSION = { 0 | 1 | 2 | 3 | 4 } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] COPY_ONLY ]
[ [ , ] COPYTO = { 'target_folder_name' } [,...n] ]
[ [ , ] DESCRIPTION = { 'text' } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] DISKRETRYCOUNT = { n } ]
[ [ , ] DISKRETRYINTERVAL = { n } ]
[ [ , ] ERASEFILES | ERASEFILES_ATSTART = { days | hours{h} | keep{b} } ]
[ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
[ [ , ] FILECOUNT = { n } ]
[ [ , ] FILEOPTIONS = { 1 | 2 | 3 | 4 | 5 | 6 | 7 } ]
[ [ , ] INIT ]
[ [ , ] KEYSIZE = { 128 | 256 } ]
[ [ , ] LOGTO = { 'target_folder_name' | 'file_name' } ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MAXDATABLOCK = { 65536 | 131072 | ... | 2097152 } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] MIRRORFILE = { 'physical_backup_device_name' } ] [ ,...n ]
[ [ , ] NAME = { 'backup_set_name' } ]
[ [ , ] NOCOMPRESSWRITE | NOWRITE ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] SINGLERESULTSET ]
[ [ , ] THREADCOUNT = { n } ]
[ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
[ [ , ] USESIMPLECOPY ]
[ [ , ] VERIFY ]
]

Backing up a transaction log to a single file or split files

BACKUP LOG { database_name }
TO { DISK } = { 'physical_backup_device_name' | '<AUTO>' } [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] COMPRESSION = { 0 | 1 | 2 | 3 | 4 } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] COPY_ONLY ]
[ [ , ] COPYTO = { 'target_folder_name' } [ ,...n ] ]
[ [ , ] DESCRIPTION = { 'text' } ]
[ [ , ] DISCONNECT_EXISTING ]
[ [ , ] DISKRETRYCOUNT = { n } ]
[ [ , ] DISKRETRYINTERVAL = { n } ]
[ [ , ] ERASEFILES | ERASEFILES_ATSTART = { days | hours{h} | keep{b} } ]
[ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
[ [ , ] FILECOUNT = { n } ]
[ [ , ] FILEOPTIONS = { 1 | 2 | 3 | 4 | 5 | 6 | 7 } ]
[ [ , ] INIT ]
[ [ , ] KEYSIZE = { 128 | 256 } ]
[ [ , ] LOGTO = { 'target_folder_name' | 'file_name' } ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MAXDATABLOCK = { 65536 | 131072 | ... | 2097152 } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] MIRRORFILE = {'physical_backup_device_name' } ] [ ,...n ]
[ [ , ] NAME = { 'backup_set_name' } ]
[ [ , ] NOCOMPRESSWRITE | NOWRITE ]
[ [ , ] NO_TRUNCATE ]
[ [ , ] { NORECOVERY | STANDBY = 'undo_file_name' } ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] SINGLERESULTSET ]
[ [ , ] THREADCOUNT = { n } ]
[ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
[ [ , ] USESIMPLECOPY ]
[ [ , ] VERIFY ]
]

Backing up multiple transaction logs to single files or split files

BACKUP LOGS [EXCLUDE] { '[ list_of_databases ]' }
TO { DISK } = { 'physical_backup_device_name' | '<AUTO>' } [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] COMPRESSION = { 0 | 1 | 2 | 3 | 4 } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] COPY_ONLY ]
[ [ , ] COPYTO = { 'target_folder_name' } [ ,...n ] ]
[ [ , ] DESCRIPTION = { 'text' } ]
[ [ , ] DISCONNECT_EXISTING ]
[ [ , ] DISKRETRYCOUNT = { n } ]
[ [ , ] DISKRETRYINTERVAL = { n } ]
[ [ , ] ERASEFILES | ERASEFILES_ATSTART = { days | hours{h} | keep{b} } ]
[ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
[ [ , ] FILECOUNT = { n } ]
[ [ , ] FILEOPTIONS = { 1 | 2 | 3 | 4 | 5 | 6 | 7 } ]
[ [ , ] INIT ]
[ [ , ] KEYSIZE = { 128 | 256 } ]
[ [ , ] LOGTO = { 'target_folder_name' | 'file_name' } ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MAXDATABLOCK = { 65536 | 131072 | ... | 2097152 } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] NAME = { 'backup_set_name' } ]
[ [ , ] NOCOMPRESSWRITE | NOWRITE ]
[ [ , ] NO_TRUNCATE ]
[ [ , ] { NORECOVERY | STANDBY = 'undo_file_name' } ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] SINGLERESULTSET ]
[ [ , ] THREADCOUNT = { n } ]
[ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
[ [ , ] USESIMPLECOPY ]
[ [ , ] VERIFY ]
]

TO DISK argument

You can specify multiple DISK values, up to a maximum of 32. For example:

BACKUP DATABASE TO DISK = 'C:\Backups\File1.sqb', DISK = 'C:\Backups\File2.sqb'

TO DISK = '<AUTO>'

If you specify <AUTO>, SQL Backup generates the backup file path and file name using the File management options specified in the graphical user interface (GUI). If no backup location options have been set up, SQL Backup uses the SQL Server instance's default backup folder, and the default format for file names. For details, see File management options.

TO DISK = 'path\<AUTO>'

If you specify a path and <AUTO>, SQL Backup uses the specified path, and generates the file name using the Backup Location options. If no backup location options have been set up, SQL Backup uses the default format for file names.

You can use tags in the path. For example, if you are backing up multiple databases and you want the backup files for each database to be created in a separate folder, you can specify the <DATABASE> tag. The following example creates the backups in a different folder for each database name:

BACKUP DATABASES TO DISK = 'C:\Backups\<DATABASE>\<AUTO>'

You can specify more than one tag in the path. In the following example, backups are grouped by database name, date, and type of backup:

BACKUP DATABASES TO DISK = 'C:\Backups\<DATABASE>\<DATETIME yyyymmdd>\<TYPE>\<AUTO>'

For details of the tags you can use, see File location tags.

TO DISK = 'file_name'

If you specify a file name with the TO DISK argument, SQL Backup uses the Backup Location options to generate the backup file path, and uses the specified name for the file. If no backup settings have been set up, SQL Backup uses the SQL Server instance's default backup folder.

TO DISK = '<AUTO>.001'

If you specify <AUTO> with a file extension, SQL Backup uses the Backup Location options to generate the backup file path and file name. If no backup settings have been set up, SQL Backup uses the SQL Server instance's default backup folder, and the default format for file names. This is useful when you generate split backup files.

Extended arguments

COMPRESSION

Specifies the compression level. The default value is 1. If you do not want to compress the backups, specify 0. For more information, see Compression levels.

Note that if SQL Backup Lite is installed on the SQL Server, you can specify only compression level 0 (no compression) or compression level 1.

COPYTO

Specifies that a copy of the backup files is to be created in the specified folder when the backup process completes. For example:

COPYTO = '\\BACKUPSERVER001\Folder1'

You can use tags in the path. To create a copy of the backup in more than one folder, use multiple COPYTO arguments. For example:

COPYTO = '\\BACKUPSERVER001\Folder1', COPYTO = '\\BACKUPSERVER001\Folder2'

You must ensure that you have sufficient rights to the specified folders.

If you are using SQL Backup Pro and want to prevent retries during copy operations, specify USESIMPLECOPY as well.

DATABASES

Specifies multiple full or differential database backups.

'[ list_of_databases ] '

Is a comma-separated list of the names of the databases that are to be backed up. For example:

BACKUP DATABASES [ Database1, Database2, Database3 ]

The list must be enclosed in square brackets [ ], and the list must not contain any other square brackets. A single wildcard character can be used. For example:

BACKUP DATABASES [*]

This backs up all databases that are currently online and operational, including read-only databases; databases that are unrecovered or unavailable (for example, offline) are not backed up.

EXCLUDE

Specifies that all online databases except those listed are to be backed up. For example:

BACKUP DATABASES EXCLUDE [ Database1, Database2, Database3 ]

This backs up all databases that are currently online and operational, except for Database1, Database2, and Database3. You cannot use wildcard characters in exclusion lists.

TO { DISK } =
{ 'physical_backup_device_name' | '<AUTO>' } [,...n]

Creates the backups on the specified disk. You are recommended to include the <AUTO> parameter to ensure that each database is backed up to a unique file name. For details, see the TO DISK argument. For example:

TO DISK = 'C:\Backups\<AUTO>'

Alternatively, you could set up your own unique path using tags (for example, you could use the <DATETIME> tag).

DISCONNECT_EXISTING

Kills any existing connections to the database before starting a transaction log backup. DISCONNECT_EXISTING is only valid when you are backing up the tail of the transaction log (with NORECOVERY or STANDBY).

DISKRETRYCOUNT

In combination with DISKRETRYINTERVAL, this argument controls network resilience behaviour.

DISKRETRYCOUNT specifies the maximum number of times to retry a failed data-transfer operation (writing or copying a backup file). If you omit this keyword, the default value of 10 is used; specify a value of 0 to prevent any retries following a failed data-transfer operation. If you specify a value for DISKRETRYCOUNT, you should also specify a value for DISKRETRYINTERVAL.

If you have also specified the COPYTO keyword, you can prevent retries for the copying operation only, by specifying USESIMPLECOPY.

DISKRETRYCOUNT is valid with SQL Backup Pro only.

DISKRETRYINTERVAL

In combination with DISKRETRYCOUNT, this argument controls network resilience behaviour.

DISKRETRYINTERVAL specifies the time interval between retries, in seconds, following a failed data-transfer operation (writing or copying a backup file). If you omit this keyword, the default value of 30 seconds is used. If you specify a value for DISKRETRYINTERVAL, you should also specify a value for DISKRETRYCOUNT.

If you have also specified the COPYTO keyword, you can prevent retries for the copying operation only, by specifying USESIMPLECOPY.

DISKRETRYINTERVAL is valid with SQL Backup Pro only.

ERASEFILES

Manages deletion of SQL Backup files from earlier backups in the primary backup folder (specified using DISK). The backup files are deleted only if the backup process completes successfully. To delete backup files prior to the start of the backup process, use ERASEFILES_ATSTART.

You can choose to delete SQL Backup files based on:

  • Age: files older than the specified number of days or hours are deleted. Type a number for days, or type h after the number for hours. For example, ERASEFILES = 24 deletes files that are more than 24 days old; ERASEFILES = 24h deletes files that are more than 24 hours old.
  • Number of files to keep: only the latest 'x' files will be kept. To specify the number of files to be kept, type b after the number. For example, ERASEFILES = 5b ensures the latest 5 files are kept; older files are deleted.

Files are deleted only if the name of the SQL Server, instance (if applicable), and database recorded in the file header match the details of the database that is being backed up. The backup type (for example, full, or differential) must also match.

To prevent deletion of files that have their archive attribute set, use the FILEOPTIONS argument.

You can use ERASEFILES_REMOTE to manage deletion of files in the secondary backup folder (specified using COPYTO).

Note that if SQL Backup cannot list the contents of the folder that contains the files to be deleted, it cannot delete the files. Ensure that the user account from which you are running SQL Backup has permissions to list the folder contents.

ERASEFILES_ATSTART

Manages deletion of SQL Backup files from earlier backups in the primary backup folder (specified using DISK). The backup files are deleted before the backup process starts. To delete backup files only if the backup process completes successfully, use ERASEFILES.

You can choose to delete SQL Backup files based on:

  • Age: files older than the specified number of days or hours are deleted. Type a number for days, or type h after the number for hours. For example, ERASEFILES = 24 deletes files that are more than 24 days old; ERASEFILES = 24h deletes files that are more than 24 hours old.
  • Number of files to keep: only the latest 'x' files will be kept. To specify the number of files to be kept, type b after the number. For example, ERASEFILES = 5b ensures the latest 5 files are kept; older files are deleted.

Files are deleted only if the name of the SQL Server, instance (if applicable), and database recorded in the file header match the details of the database that is being backed up. The backup type (for example, full, or differential) must also match.

To prevent deletion of files that have their archive attribute set, use the FILEOPTIONS argument.

You can use ERASEFILES_REMOTE to manage deletion of files in the secondary backup folder (specified using COPYTO).

Note that if SQL Backup cannot list the contents of the folder that contains the files to be deleted, it cannot delete the files. Ensure that the user account from which you are running SQL Backup has permissions to list the folder contents.

ERASEFILES_REMOTE

Manages deletion of remote SQL Backup files from earlier backups in the primary backup folder (specified using DISK) and secondary backup folder (specified using COPYTO). The backup files are deleted only if the backup process completes successfully.

You can choose to delete SQL Backup files based on:

  • Age: files older than the specified number of days or hours are deleted. Type a number for days, or type h after the number for hours. For example, ERASEFILES = 24 deletes files that are more than 24 days old; ERASEFILES = 24h deletes files that are more than 24 hours old.
  • Number of files to keep: only the latest 'x' files will be kept. To specify the number of files to be kept, type b after the number. For example, ERASEFILES = 5b ensures the latest 5 files are kept; older files are deleted.

Files are deleted only if the name of the SQL Server, instance (if applicable), and database recorded in the file header match the details of the database that is being backed up. The backup type (for example, full, or differential) must also match.

To allow overwriting of existing backup files, use the FILEOPTIONS argument.

You can use ERASEFILES to manage deletion of files in the primary backup folder (specified using DISK).

Note that if SQL Backup cannot list the contents of the folder that contains the files to be deleted, it cannot delete the files. Ensure that the user account from which you are running SQL Backup has permissions to list the folder contents.

FILECOUNT

Specifies the number of backup files to be generated if you are splitting the backup across a number of files, where n is an integer between 2 and 32 inclusive. The name of the file specified for TO DISK is used as the base name for the generated files. For example:

BACKUP DATABASE Pubs TO DISK = 'C:\Backups\Pubs.sqb' WITH FILECOUNT = 4

This example generates four backup files:

C:\Backups\Pubs_01.sqb
C:\Backups\Pubs_02.sqb
C:\Backups\Pubs_03.sqb
C:\Backups\Pubs_04.sqb

Can be used together with the <AUTO> keyword (see the TO DISK argument). For example:

BACKUP DATABASE Pubs TO DISK = '<AUTO>' WITH FILECOUNT = 4

In this example, SQL Backup generates the additional files using the <AUTO> naming convention, appending _02, _03, _04 and so on to the root name.

Note that if you are using the FILECOUNT keyword to create multiple files:

  • you cannot use TO DISK multiple times
  • you cannot use the THREADCOUNT keyword to use multiple threads

FILEOPTIONS

Specifies whether old backup files are to be deleted or overwritten in the primary backup folder and any COPYTO folders. Specify the sum of the values that correspond to the options you require:

1 | Delete old backup files in the secondary backup folders (specified using COPYTO) if they are older than the number of days or hours specified in ERASEFILES, ERASEFILES_ATSTART, or ERASEFILES_REMOTE.
2 | Delete old backup files in the primary backup folder (specified using DISK) if they are older than the number of days or hours specified in ERASEFILES, ERASEFILES_ATSTART, or ERASEFILES_REMOTE unless they have the ARCHIVE flag set.
4 | Overwrite existing files in the COPYTO folder.

Valid values are 1 to 7.

If you specify option 1 or 2 you must also set the age of the files to delete using ERASEFILES, ERASEFILES_ATSTART, or ERASEFILES_REMOTE. For example, to delete old backup files in the COPYTO folder that are older than 5 days:

BACKUP DATABASE... WITH COPYTO = ... , ERASEFILES = 5, FILEOPTIONS = 1

To overwrite any existing files in the COPYTO folder and also delete old backup files in the COPYTO folder that are older than 5 days (values 1 + 4):

BACKUP DATABASE... WITH COPYTO = ... , ERASEFILES = 5, FILEOPTIONS = 5

INIT

Specifies that files with the same name in the primary backup folder should be overwritten.

Because SQL Backup supports a maximum of one backup set in a media set, specifying INIT also overwrites any media-set data (media header) associated with the existing backup files. This is equivalent to using the FORMAT argument in SQL Server.

KEYSIZE

Specifies the size of the encryption key to use; you must also use the PASSWORD keyword to specify a password for the encrypted backups. If SQL Backup Pro is installed on the SQL Server, you can specify a 128-bit key or a 256-bit key; if SQL Backup Lite is installed on the SQL Server, you cannot encrypt the backup.

If you do not specify the key size, SQL Backup Pro uses a 256-bit key.

Note that you can restore 128-bit key and 256-bit key encrypted backups using SQL Backup Pro or SQL Backup Lite.

LOGS

Specifies multiple transaction log backups.

'[ list_of_databases ] '

Is a comma-separated list of the names of the databases for which the transaction logs are to be backed up. The databases must use the FULL or BULK-LOGGED recovery model. For example:

BACKUP LOGS [ Database1, Database2, Database3 ]

The list must be enclosed in square brackets [ ], and the list must not contain any other square brackets. A single wildcard character can be used. For example:

BACKUP LOGS [*]

This backs up transaction logs for all databases that are currently online, operational, and using the FULL or BULK-LOGGED recovery models.

EXCLUDE

Specifies that transaction logs of all online databases except those listed are to be backed up. For example:

BACKUP LOGS EXCLUDE [ Database1, Database2, Database3 ]

This backs up the transaction logs of all databases that are currently online and operational, except for Database1, Database2, and Database3.

TO { DISK } =
{ 'physical_backup_device_name' | '<AUTO>' } [,...n]

Creates the backups on the specified disk. Must include the <AUTO> parameter to ensure that each transaction log is backed up to a unique file name. For details, see TO DISK argument. For example:

TO DISK = 'C:\Backups\<AUTO>'

LOGTO

Specifies that a copy of the log file is to be saved.

By default, the primary log file is created in the folder C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log; you can change this location in your log file options. To create a copy with the same name as the primary log file, specify the folder. For example:

LOGTO = 'C:\Logs'

To create a copy with a different name from the primary log file, specify the folder and file name. For example:

LOGTO = 'C:\Logs\SQBSecondaryLog.txt'

To copy the log file to more than one location, use multiple LOGTO commands.

MAILTO

Specifies that a copy of the log file is to be sent to one or more users by email. SQL Backup uses the settings specified in your email options to send the email. To specify multiple recipients, separate the email addresses with a semi-colon (;). For example:

MAILTO = 'dba01@myco.com;dba02@myco.com'

If you have not defined email settings, the email will not be sent and a warning will be reported.

Note that if SQL Backup Lite is installed on the SQL Server, you cannot use email notification.

MAILTO_ONERROR

Specifies that a copy of the log file is to be sent to one or more users by email if SQL Backup encounters an error during the backup process or the backup completes successfully but with warnings. SQL Backup uses the settings specified in your email options to send the email. To specify multiple recipients, separate the email addresses with a semi-colon (;). For example:

MAILTO_ONERROR = 'dba01@myco.com;dba02@myco.com'

If you have not defined email settings, the email will not be sent and a warning will be reported.

Note that if SQL Backup Lite is installed on the SQL Server, you cannot use email notification.

MAILTO_ONERRORONLY

Specifies that a copy of the log file is to be sent to one or more users by email if SQL Backup encounters an error during the backup process. SQL Backup uses the settings specified in your email options to send the email. To specify multiple recipients, separate the email addresses with a semi-colon (;). For example:

MAILTO_ONERRORONLY = 'dba01@myco.com;dba02@myco.com'

If you have not defined email settings, the email will not be sent and a warning will be reported.

Note that if SQL Backup Lite is installed on the SQL Server, you cannot use email notification.

MAXDATABLOCK

Specifies the maximum size of data blocks to be used when SQL Backup stores backup data. Valid values are integers in multiples of 65536, up to a maximum value of 2097152. For example:

MAXDATABLOCK = 655360

If not specified, SQL Backup uses the value defined in the following DWORD registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\MAXDATABLOCK (32-bit)

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\MAXDATABLOCK (64-bit)

MAXTRANSFERSIZE

Specifies the maximum size of each block of memory to be used when SQL Backup stores backup data. You may want to specify this argument if a SQL Server reports that it has insufficient memory to service requests from SQL Backup.

Valid values are integers in multiples of 65536, up to a maximum value of 1048576.

For example:

MAXTRANSFERSIZE = 262144

If not specified, defaults to 1048576. However, if you have created the following DWORD registry key, SQL Backup uses the defined value as the default value:

HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\MAXTRANSFERSIZE (32-bit)

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\MAXTRANSFERSIZE (64-bit)

MIRRORFILE

Indicates that you want to create a duplicate backup file (in addition to the backup file named in the TO DISK argument). Specify the value as described for the TO DISK argument.

You cannot use MIRRORFILE if you are backing up multiple databases, if you specify multiple DISK options, or if you use the FILECOUNT option.

To create more than one duplicate backup file, use multiple MIRRORFILE arguments (up to a maximum of 32 files).

Before the backup process starts, the locations specified in the MIRRORFILE arguments are verified; if a location does not exist, or the file cannot be created at that location, then the backup will not start.

During the backup process, if any of the files cannot be written a warning is raised. However, the backup process continues as long as at least one specified backup file can be written. If none of the files can be written, an error is raised and the backup process is stopped.

NOCOMPRESSWRITE

Determines the maximum backup process throughput of your SQL Server. When you specify this argument, SQL Backup simulates a backup process without compression; no backup files are created.

You can compare the results obtained using NOCOMPRESSWRITE and NOWRITE to deduce the effects of compression on the backup throughput. For details, see Optimizing backup speed.

NOWRITE

Determines the maximum backup process throughput of your SQL Server using compression. When you specify this argument, SQL Backup simulates a backup process using the specified compression level; no backup files are created.

You can compare the results obtained using NOCOMPRESSWRITE and NOWRITE to deduce the effects of compression on the backup throughput. For details, see Optimizing backup speed.

PASSWORD

Specifies the password to be used with encrypted backup files. You must supply the same password when you restore the backup.

You can specify the size of the encryption key using the KEYSIZE keyword.

Note that in standard Transact-SQL syntax, the PASSWORD argument attaches a password to the backup file, but does not encrypt the file contents.

Note that if you subsequently convert the SQL Backup file to a Microsoft Tape Format (MTF) file, you must supply the password; the MTF file will not be protected by the password.

SINGLERESULTSET

Specifies that the results returned by the BACKUP command should be limited to just one result set. This may be useful if you want to manipulate results using a Transact-SQL script. Such scripts can only manipulate results when a single result set is returned. The BACKUP command will return two result sets by default in most cases, unless you specify the SINGLERESULTSET keyword.

STANDBY

Backs up the tail of the transaction log and leaves the database in a read-only and STANDBY state. The STANDBY clause writes standby data (performing rollback, but with the option of further restores).

'undo_file_name'

Is a standby file, whose location is stored in the log of the database. This file holds the rolled back changes, which must be reversed if RESTORE LOG operations are to be subsequently applied.

When used with the BACKUP LOGS command (backing up multiple transaction logs) 'undo_file_name' must include the <DATABASE> tag.

When used with the BACKUP LOG command (backing up a single transaction log) 'undo_file_name' can include tags, but these are not required.

THREADCOUNT

Specifies the number of threads to be used to create the backup, where n is an integer between 2 and 32 inclusive. For example:

BACKUP DATABASE Pubs TO DISK = 'C:\Backups\Pubs.sqb' WITH THREADCOUNT = 4

This example creates a single backup files using four threads.

Note that if you are using the THREADCOUNT keyword to use multiple threads:

  • you cannot use TO DISK multiple times
  • you cannot use the FILECOUNT keyword to create multiple files

THREADPRIORITY

Sets the SQL Backup thread priority when the backup or restore process is run. Valid values are 0 to 6, and correspond to the following priorities:

0 | Idle
1 | Very Low
2 | Low
3 | Normal
4 | High
5 | Very High
6 | Time Critical

USESIMPLECOPY

Prevents retries occurring during copy operations (specified using the COPYTO keyword). With USESIMPLECOPY specified, copying behaviour is the same as in SQL Backup version 5.

VERIFY

Defines whether the backup files are verified when the backup process has completed. If specified, runs a procedure to check the backup file when the backup has completed, to ensure that the file is not corrupted and can be read by SQL Backup for restoring. The results are displayed as text output when the backup completes. You can also check the results later by viewing the Activity History in the graphical user interface.

Deprecated extended arguments

The following extended arguments have been deprecated:

  • THREADS (replaced by FILECOUNT)
  • ERASEFILEOPTIONS (deprecated in version 4)
  • FILEOPTIONS (value 1 only)

See also

The RESTORE command

Using the command line

Toolkit syntax examples

Toolkit parameters

The Activity History

Using the extended stored procedure

Compression levels

Creating backups

File management options

File location tags

Email notification options

Optimizing backup speed


The RESTORE command

Use the RESTORE command with the SQL Backup -SQL parameter to restore a backup that you created using SQL Backup.

  • Syntax provides the syntax for the command; additional arguments and extended syntax are highlighted.
  • Extended arguments describes the arguments that are provided by SQL Backup; for information about the standard arguments, refer to your SQL Server documentation.

Some example snippets are included in this topic. For more detailed examples, see Toolkit syntax examples. Note that the extended stored procedure expects only one parameter, which must be delimited by single quotes. Therefore, wherever a single quote is used for the parameters below, for the extended stored procedure you must use two single quotes so that SQL Server does not interpret it as a string delimiter.

For information about how you restore multiple backups, see Restoring multiple backups.

Syntax

Note that the following arguments are only available with SQL Server 2005 and SQL Server 2008 instances:

CHECKSUM / NO_CHECKSUM
CONTINUE_AFTER_ERROR / STOP_ON_ERROR

Restore an entire database

RESTORE DATABASE { database_name }
< file_or_filegroup_or_pages > [ ,...n ]
[ FROM { DISK } = { 'physical_backup_device_name' } ] [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] DISCONNECT_EXISTING ]
[ [ , ] ERASEFILES = { days | hours{h} | keep{b} } ]
[ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
[ [ , ] FILEOPTIONS = { 1 | 2 | 3 } ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] LOGTO = { 'target_folder_name' | 'file name' } ] [ ,...n ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] [ ,...n ]
[ [ , ] MOVETO ' { 'target_folder_name' } ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY = 'undo_file_name' } ]
[ [ , ] ORPHAN_CHECK ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] REPLACE ]
[ [ , ] RESTRICTED_USER ]
[ [ , ] SINGLERESULTSET ]
[ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
]

Restore part of a database

RESTORE DATABASE { database_name }
< file_or_filegroup > [ ,...n ]
[ FROM { DISK } = { 'physical_backup_device_name' } ] [ ,...n ]
[ WITH
PARTIAL
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] DISCONNECT_EXISTING ]
[ [ , ] ERASEFILES = { days | hours{h} | keep{b} } ]
[ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
[ [ , ] FILEOPTIONS = { 1 | 2 | 3 } ]
[ [ , ] LOGTO = { 'target_folder_name' | 'file name' } ] [ ,...n ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] [ ,...n ]
[ [ , ] MOVETO ' { 'target_folder_name' } ]
[ [ , ] NORECOVERY ]
[ [ , ] ORPHAN_CHECK ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] REPLACE ]
[ [ , ] RESTRICTED_USER ]
[ [ , ] SINGLERESULTSET ]
[ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
]

Restore a transaction log

RESTORE LOG { database_name }
< file_or_filegroup_or_pages > [ ,...n ]
[ FROM { DISK } = { 'physical_backup_device_name' | 'file_search_pattern' } ] [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] DISCONNECT_EXISTING ]
[ [ , ] ERASEFILES = { days | hours{h} | keep{b} } ]
[ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
[ [ , ] FILEOPTIONS = { 1 | 2 | 3 } ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] LOGTO = { 'target_folder_name | file_name' } ] [ ,...n ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] MOVETO = { 'target_folder_name' } ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY = 'undo_file_name' } ]
[ [ , ] ORPHAN_CHECK ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] REPLACE ]
[ [ , ] RESTRICTED_USER ]
[ [ , ] SINGLERESULTSET ]
[ [ , ] { STOPAT = { 'date_time' | @date_time_var } ]
| STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime' ]
| STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime']
} ]
[ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
]

Restore file list

RESTORE FILELISTONLY
[ FROM { DISK } = { 'physical_backup_device_name' } ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] SINGLERESULTSET ]
]

Restore header

RESTORE HEADERONLY
[ FROM { DISK } = { 'physical_backup_device_name' } ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] SINGLERESULTSET ]
]

We recommend you use the SQL Backup command RESTORE SQBHEADERONLY to retrieve the header information for SQL Backup backup files, because it is much quicker than using the native command RESTORE HEADERONLY. For details, see The RESTORE SQBHEADERONLY command.

Verify backup set

RESTORE VERIFYONLY
[ FROM { DISK } = { 'physical_backup_device_name' } ] [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] SINGLERESULTSET ]
]

FROM DISK argument

FROM DISK

You can specify up to 32 DISK values. You can also specify wildcard characters in the physical backup device name for full and differential backups. All files that match the wildcard characters must belong to the same backup set. For example, instead of:

FROM DISK = 'C:\Backups\pubs_01.sqb', DISK = 'C:\Backups\pubs_02.sqb', DISK = 'C:\Backups\pubs_03.sqb',
DISK = 'C:\Backups\pubs_04.sqb'], DISK = 'C:\Backups\pubs_05.sqb'

you can enter:

FROM DISK = 'C:\Backups\pubs_*.sqb'

For more information about restoring multiple backups, see Restoring multiple backups.

Extended arguments

DISCONNECT_EXISTING

Kills any existing connections to the database before starting the restore. Restoring to an existing database will fail if there are any connections to the database.

ERASEFILES

Deletes all SQL Backup files for the database that are older than the specified number of days (or hours), or that exceed the number of files you want to keep, and are located in the MOVETO folder. You must also set the FILEOPTIONS argument. The files are deleted only if the name of the SQL Server, instance (if applicable), and database recorded in the file header match the details of the database that is being restored.

  • To specify the age of files to be deleted in hours, type h after the number. For example, ERASEFILES = 24 deletes files that are more than 24 days old; ERASEFILES = 24h deletes files that are more than 24 hours old.
  • To specify the number of files to keep, type b after the number. For example, ERASEFILES = 5b ensures the latest 5 files are kept; older files are deleted.

Note that if SQL Backup cannot list the contents of the folder that contains the files to be deleted, it cannot delete the files. Ensure that the user account from which you are running SQL Backup has permissions to list the folder contents.

ERASEFILES_REMOTE

Deletes all SQL Backup files for the database that are older than the specified number of days (or hours), or that exceed the number of files you want to keep, and are located on a network share. You use this argument if you want to specify different deletion intervals for local files and files located on network shares. If not specified, SQL Backup uses the ERASEFILES value.

The backup files are deleted only if the restore process completes successfully.

  • To specify the age of files to be deleted in hours, type h after the number. For example, ERASEFILES_REMOTE = 24 deletes files that are more than 24 days old; ERASEFILES_REMOTE = 24h deletes files that are more than 24 hours old.
  • To specify the number of files to keep, type b after the number. For example, ERASEFILES = 5b ensures the latest 5 files are kept; older files are deleted.

You can use FILEOPTIONS to specify that files will also be deleted in the MOVETO folder.

Note that if SQL Backup cannot list the contents of the folder that contains the files to be deleted, it cannot delete the files. Ensure that the user account from which you are running SQL Backup has permissions to list the folder contents.

FILEOPTIONS

Specifies whether old backup files are to be deleted in the primary backup folder and the MOVETO folder. Specify the sum of the values that correspond to the options you require:

1 | Delete old backup files in the MOVETO folder if they are older than the number of days or hours specified in ERASEFILES.
2 | Do not delete old backup files in the MOVETO folder that are older than the number of days or hours specified in ERASEFILES if they have the ARCHIVE flag set.

Valid values are 1, 2, and 3.

You must also set the age of the files to delete using ERASEFILES. For example, to delete old backup files in the MOVETO folder that are older than 5 days:

BACKUP DATABASE ... WITH MOVETO = ... , ERASEFILES = 5, FILEOPTIONS = 1

To delete any existing files in the MOVETO folder that are older than 5 days and do not have the ARCHIVE flag set, (values 1 + 2):

BACKUP DATABASE ... WITH MOVETO = ... , ERASEFILES = 5, FILEOPTIONS = 3

LOGTO

Specifies that a copy of the log file is to be saved.

By default, the primary log file is created in the folder C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log; you can change this location in your log file options. To create a copy with the same name as the primary log file, specify the folder. For example:

LOGTO = 'C:\Logs'

To create a copy with a different name from the primary log file, specify the folder and file name. For example:

LOGTO = 'C:\Logs\SQBSecondaryLog.txt'

To copy the log file to more than one location, use multiple LOGTO commands.

MAILTO

Specifies that a copy of the log file is to be sent to one or more users by email. SQL Backup uses the settings specified in your email options to send the email. To specify multiple recipients, separate the email addresses with a semi-colon (;). For example:

MAILTO = 'dba01@myco.com;dba02@myco.com'

If you have not defined email settings, the email will not be sent and a warning will be reported.

Note that if SQL Backup Lite is installed on the SQL Server, you cannot use email notification.

MAILTO_ONERROR

Specifies that a copy of the log file is to be sent to one or more users by email if SQL Backup encounters an error during the restore process or the restore process completes successfully but with warnings. SQL Backup uses the settings specified in your email options to send the email. To specify multiple recipients, separate the email addresses with a semi-colon (;). For example:

MAILTO_ONERROR = 'dba01@myco.com;dba02@myco.com'

If you have not defined email settings, the email will not be sent and a warning will be reported.

Note that if SQL Backup Lite is installed on the SQL Server, you cannot use email notification.

MAILTO_ONERRORONLY

Specifies that a copy of the log file is to be sent to one or more users by email if SQL Backup encounters an error during the restore process. SQL Backup uses the settings specified in your email options to send the email. To specify multiple recipients, separate the email addresses with a semi-colon (;). For example:

MAILTO_ONERRORONLY = 'dba01@myco.com;dba02@myco.com'

If you have not defined email settings, the email will not be sent and a warning will be reported.

Note that if SQL Backup Lite is installed on the SQL Server, you cannot use email notification.

MAXTRANSFERSIZE

Specifies the maximum size of each block of memory to be used when SQL Backup restores backup data. You may want to specify this argument if a SQL Server reports that it has insufficient memory to service requests from SQL Backup.

Valid values are integers in multiples of 65536, up to a maximum value of 1048576.

For example:

MAXTRANSFERSIZE = 262144

If not specified, defaults to 1048576. However, if you have created the following DWORD registry key, SQL Backup uses the defined value as the default value:

HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\MAXTRANSFERSIZE (32-bit)

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\MAXTRANSFERSIZE (64-bit)

MOVETO

Specifies that the backup files should be moved to another folder when the restore process completes. For example:

MOVETO = 'C:\Backups\Archive\Processed'

You must ensure that you have permission to delete files from the original folder, and to write the MOVETO folder.

ORPHAN_CHECK

Specifies that once the restore has completed, the database should be checked for orphaned users. Database user names are considered to be orphaned if they do not have a corresponding login defined on the SQL Server instance. Orphaned users are often created when you restore a database backup to a different SQL Server instance.

If orphaned users are detected, warning 472 is generated and each orphaned user is listed in the SQL Backup log file along with the associated SID.

PASSWORD

Specifies the password to be used with encrypted backup files.

You cannot use the encrypted form of the password. This is to prevent unauthorized users from restoring backups if they have access to the encrypted password, but not the unencrypted password.

SQL Backup version 3 allowed the use of encrypted passwords; these will no longer work. You must specify the password in unencrypted form:

RESTORE DATABASE database FROM DISK='filename' WITH PASSWORD = 'Password'

SINGLERESULTSET

Specifies that the results returned by the RESTORE command should be limited to just one result set. This may be useful if you want to manipulate results using a Transact-SQL script. Such scripts can only manipulate results when a single result set is returned. The BACKUP command will return two result sets by default in most cases, unless you specify the SINGLERESULTSET keyword.

STANDBY

Specifies a standby file that allows the recovery effects to be undone. The STANDBY option is allowed for offline restore (including partial restore). The option is disallowed for online restore.

Refer to your SQL Server documentation for more information about the STANDBY argument.

'undo_file_name'

Is a standby file used to keep a "copy-on-write" pre-image for pages modified during the undo pass of a RESTORE WITH STANDBY.

When used with the RESTORE DATABASE or RESTORE LOG command, 'undo_file_name' can include tags, but these are not required.

THREADPRIORITY

Sets the SQL Backup thread priority when the backup or restore process is run. Valid values are 0 to 6, and correspond to the following priorities:

0 | Idle
1 | Very Low
2 | Low
3 | Normal
4 | High
5 | Very High
6 | Time Critical

See also

File management options

The BACKUP command

The RESTORE SQBHEADERONLY command

Toolkit parameters

Toolkit syntax examples

Using the command line

Using the extended stored procedure


The RESTORE SQBHEADERONLY command

Use the RESTORE SQBHEADERONLY command with the SQL Backup -SQL parameter to retrieve the header information associated with a SQL Backup backup file.

Note that you can use wildcard characters for the location of the backups (physical_backup_device_name) to display the headers of multiple files.

For backup files that are compressed, using RESTORE SQBHEADERONLY to view header information is much quicker than using the equivalent SQL Server command, which necessarily requires that the entire backup file is uncompressed.

Syntax

Additional arguments and extended syntax are highlighted.

Restore a backup header

RESTORE SQBHEADERONLY
[ FROM { DISK } = { 'physical_backup_device_name' } ]
[ WITH
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] SINGLERESULTSET ]
]

Arguments

PASSWORD

Specifies the password to be used with encrypted backup files. If you are restoring multiple headers at the same time, the encrypted files must all have the same password.

SINGLERESULTSET

Specifies that the results returned by the RESTORE SQBHEADERONLY command should be limited to just one result set. This may be useful if you want to manipulate results using a Transact-SQL script. Such scripts can only manipulate results when a single result set is returned. The RESTORE SQBHEADERONLY command will return two result sets by default unless you specify the SINGLERESULTSET keyword.

Examples

A. Restoring a header file

This example retrieves the header information for the pubs.sqb database backup file.

SQLBackupC -SQL "RESTORE SQBHEADERONLY FROM DISK = 'C:\Backups\pubs.sqb' "

B. Restoring multiple header files

This example retrieves the header information for all database backup files in the Backups folder.

SQLBackupC -SQL "RESTORE SQBHEADERONLY FROM DISK = 'C:\Backups\*.sqb' "

See also

Using the command line

Toolkit syntax examples

Toolkit parameters

The RESTORE command


The CONVERT command

Use the CONVERT command with the SQL Backup -SQL parameter to convert SQL Backup files (.sqb) to Microsoft Tape Format (MTF) files (.bak). You can use the native SQL Server RESTORE command to restore MTF files.

Alternatively, you can use the SQL Backup File Converter (either from the command line, or using the GUI application). This application can also convert SQL Backup v6 files to SQL Backup v5 files; both file types have extension .sqb. See SQL Backup File Converter for more information.

Note that if you convert a backup file that has been stored across a number of files (using FILECOUNT as detailed in The BACKUP command), an individual .bak file is created for each file. For example, if backup file BackUpA.sqb was split into three files, the following files are created when you convert the backup:

BackUpA_01.bak

BackUpA_02.bak

BackUpA_03.bak

You can then restore the MTF (.bak) format backups as a group of backup files.

The CONVERT command

Additional arguments and extended syntax are highlighted.

Syntax

CONVERT { 'physical_backup_device_name' } ] TO { 'physical_file_name' }
[ WITH
[ [ , ] MAXDATABLOCK = { 65536 | 131072 | ... | 2097152 } ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] SINGLERESULTSET ]
]

Arguments

MAXDATABLOCK

Specifies the maximum size of data blocks to be used when SQL Backup stores backup data. Valid values are integers in multiples of 65536, up to a maximum value of 2097152. For example:

MAXDATABLOCK = 655360

PASSWORD

Specifies the password to be used with encrypted backup files.

SINGLERESULTSET

Specifies that the results returned by the CONVERT command should be limited to just one result set. This may be useful if you want to manipulate results using a Transact-SQL script. Such scripts can only manipulate results when a single result set is returned. The CONVERT command will return two result sets by default unless you specify the SINGLERESULTSET keyword.

Example

SQLBackupC -SQL "CONVERT 'C:\Backups\pubs.sqb' TO 'C:\Backups\pubs.bak' "

See also

Using the extended stored procedure

The BACKUP command

Toolkit parameters

Using the command line

The RESTORE SQBHEADERONLY command

The RESTORE command


Toolkit syntax examples

This topic provides examples of Transact-SQL statements for the SQL Backup command line and extended stored procedure -SQL parameter.

For example, if you are using the command line, use:

>SQLBackupC -SQL "Transact-SQL_statement"...

If you are using the extended stored procedure, use:

master..sqlbackup '-SQL "Transact-SQL_statement" ...'

Note that the extended stored procedure expects only one parameter, which must be delimited by single quotes. Therefore, wherever a single quote is used for the arguments in the examples below, for the extended stored procedure you must use two single quotes so that SQL Server does not interpret it as a string delimiter.

For more information, see Using the command line and Using the extended stored procedure.

Examples are provided for the following commands:

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

For full details of the standard commands you can use, and the extensions to the standard Transact-SQL syntax, see the topics in the SQL Syntax section of the Help.

The BACKUP command

A. Back up a database to a single file

This example creates a full backup of the pubs database in a single file.

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

B. Specify the compression level for a backup

This example creates a full backup of the pubs database using compression level 4.

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

C. Split the backup file

This example creates a full backup of the pubs database and splits the backup across two files.

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

D. Create a mirrored backup

This example simultaneously creates a full backup of the pubs database and two duplicate backup files.

-SQL "BACKUP DATABASE pubs TO DISK = 'C:\Backups\pubs_01.sqb' WITH MIRRORFILE = 'E:\Backups\pubs_01_alt.sqb' "

E. Send email notification of the backup process

This example creates a full backup of the pubs database and sends an email of the completion log to two users upon completion of the backup process.

-SQL "BACKUP DATABASE pubs TO DISK = 'C:\Backups\pubs_01.sqb' WITH MAILTO = 'dba01@myco.com;dba02@myco.com' "

F. Create copies of the backup file

This example creates a full backup of the pubs database and then copies the backup file to two remote folders on completion of the backup process.

-SQL "BACKUP DATABASE pubs TO DISK = 'C:\Backups\pubs_01.sqb' WITH COPYTO = '\\BACKUPSERVER001\share', COPYTO = '\\BACKUPSERVER002\share' "

G. Encrypt the backup file

This example creates a full backup of the pubs database and encrypts the backup file with password MyPassword.

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

H. Back up multiple databases with exclusions

This example creates a full backup of the all databases except master, msdb, and model (all user databases).

-SQL "BACKUP DATABASES EXCLUDE [master, msdb, model] TO DISK = 'C:\Backups\<AUTO>' "

I. Back up multiple databases to split files

This example creates transaction log backups for databases northwind and pubs each into two files.

-SQL "BACKUP LOGS [northwind, pubs] TO DISK = 'C:\Backups\<AUTO>' WITH FILECOUNT = 2 "

J. Back up multiple databases using threads

This example creates transaction log backups for databases northwind and pubs using two threads.

-SQL "BACKUP LOGS [northwind, pubs] TO DISK = 'C:\Backups\<AUTO>' WITH THREADCOUNT = 2 "

K. Back up transaction logs for multiple databases

This example creates transaction log backups for databases northwind and pubs each into a single backup file.

-SQL "BACKUP LOGS [northwind, pubs] TO DISK = '<AUTO>' "

L. Create a partial filegroup backup

This example backs up the database FileGroupTest using the standard Transact-SQL argument READ_WRITE_FILEGROUPS. For detailed information about this argument, refer to your SQL Server documentation about the BACKUP command.

-SQL "BACKUP DATABASE [FileGroupTest] READ_WRITE_FILEGROUPS TO DISK = 'C:\Backups\<AUTO>' "

M. Create a partial differential filegroup backup

This example creates a differential backup of the database FileGroupTest using the standard Transact-SQL argument READ_WRITE_FILEGROUPS. For detailed information about this argument, refer to your SQL Server documentation about the BACKUP command.

-SQL "BACKUP DATABASE [FileGroupTest] READ_WRITE_FILEGROUPS TO DISK = 'C:\Backups\<AUTO>' WITH DIFFERENTIAL"

RESTORE command

A. Restore a database from a single file

This example restores a full backup of the pubs database from a single file.

-SQL "RESTORE DATABASE pubs FROM DISK = 'C:\Backups\pubs_01.sqb' "

B. Restore a database from a multiple (split) backup files

This example restores a full backup of the pubs database from multiple files.

-SQL "RESTORE DATABASE pubs FROM DISK = 'C:\Backups\pubs_01.sqb', DISK = 'C:\Backups\pubs_02.sqb' "

C. Restore a database to a new name and move the backup files

This example restores a full backup of the pubs database and restores it to a new database called pubs02. It also renames the database data and log files.

-SQL "RESTORE DATABASE pubs02 FROM DISK = 'C:\Backups\pubs_01.sqb' WITH MOVE 'pubs' TO 'E:\Data\pubs02.mdf', MOVE 'pubs_log' TO 'E:\Data\pubs02.ldf' "

D. Restore a database from an encrypted backup file

This example restores an encrypted backup of the pubs database, specifying the password MyPassword.

-SQL "RESTORE DATABASE pubs FROM DISK = 'C:\Backups\pubs_01.sqb' WITH PASSWORD = 'MyPassword' "

E. Restore a database in NORECOVERY mode

This example restores a full backup of the pubs database, specifying that the database is to be left in an unrecovered state so that differential and transaction log backups can be restored to it.

-SQL "RESTORE DATABASE pubs FROM DISK = 'C:\Backups\pubs_01.sqb' WITH NORECOVERY "

F. Restore a database in READ-ONLY mode

This example restores a full backup of the pubs database, specifying that the database should be left in an unrecovered, read-only state so that its data can be viewed and differential and transaction log backups can be restored to it.

-SQL "RESTORE DATABASE pubs FROM DISK = 'C:\Backups\pubs_01.sqb' WITH STANDBY = 'C:\Standby\pubs_log.DAT' "

RESTORE SQBHEADERONLY command

A. Restore a header file

This example retrieves the header information for the pubs.sqb database backup file.

-SQL "RESTORE SQBHEADERONLY FROM DISK = 'C:\Backups\pubs.sqb' "

B. Restore multiple header files

This example retrieves the header information for all database backup files in the Backups folder.

-SQL "RESTORE SQBHEADERONLY FROM DISK = 'C:\Backups\*.sqb' "

CONVERT command

A. Convert a SQL Backup file to a Microsoft Tape Format file

-SQL "CONVERT 'C:\Backups\pubs.sqb' TO 'C:\Backups\pubs.bak' "

See also

The BACKUP command

Using the command line

Toolkit parameters

The RESTORE command

The RESTORE SQBHEADERONLY command

Using the extended stored procedure


Error code reference

This topic provides information about the warning codes and error codes returned by SQL Backup.

For information about SQL Server errors, refer to your SQL Server documentation.

Warning codes

The following warning codes may be returned by SQL Backup.

Warning code | Description
110 | Failed to save primary log file: <value>
115 | LOGTO error: Failed to save log file: <value>
120 | LOGTO error: Folder does not exist: <value>
121 | LOGTO error: Default folder does not exist: <value>. Log file will be saved in folder: <value>.
130 | MOVETO error: Failed to move file: <value>
131 | MOVETO error: Failed to execute MOVETO command.
132 | MOVETO error: Folder does not exist: <value>
140 | COPYTO error: Destination file already exists. Will not overwrite: <value>
141 | COPYTO error: Unable to copy <source> to <target>
142 | COPYTO error: Unable to copy <source> to <target>. Error message: <value>
143 | COPYTO error: Failed to copy file: <value>
144 | COPYTO error: Folder does not exist: <value>
145 | COPYTO error: Failed to create folder: <value>
146 | COPYTO error: Source file does not exist: <value>
147 | COPYTO made multiple attempts to copy the file: <value>
150 | MAILTO error: SMTP host name not defined.
151 | MAILTO error: Failed to send mail.
160 | ERASEFILES error: Failed to delete file: <value>
161 | ERASEFILES error: Failed to delete file: <value>. Error code: <value>
162 | Failed to delete log file: <value>. Error code: <value>
164 | Failed to delete backup entries in msdb tables: <value>
166 | Failed to delete old entries in local history tables: <value>
167 | Failed to get database size from server.
168 | Failed to identify filestream files.
170 | Validation error when processing the list of transaction log backups.
175 | Another process might be locking this file: <value> (attempt <value>) (<value>)
195 | Failed to update backup history table (BACKUPMEDIAFAMILY).
200 | Error creating backup file: <value>
202 | Multiple attempts were made to create the backup file.
210 | Error writing to backup file: <value>
213 | Error setting file pointer position: <value>
215 | Error flushing buffer contents to file: <value>
218 | Insufficient disk space <value>: required <value>; available <value>
220 | No log files found to be restored.
300 | Backup failed. Retry attempt: <value>
320 | Validation failed.
330 | Your license does not allow email notification to be performed. The MAILTO, MAILTO_ONERROR, or MAILTO_ONERRORONLY keyword was specified, but has been ignored due to licensing restrictions.
400 | Failed to initialize local data store: <value>
402 | Failed to open local data store: <value>
405 | Failed to acquire handle for local data store lock (<value>): <value>
410 | Failed to start transaction on local data store: <value>
435 | Get exclusive local data store access failed - timeout.
436 | Get exclusive local data store access failed - abandoned.
438 | Get exclusive local data store access failed - invalid lock handle.
440 | Invalid DB handle.
442 | Get exclusive local data store access failed - invalid lock handle.
445 | Local data store error: <value>
450 | Local data store SQL error: <value>
460 | Encryption key size is specified, but password is empty. The KEYSIZE keyword has been specified, but the PASSWORD keyword has not been specified, or it is specified with a zero-length password.
462 | Short passwords may not be secure. The KEYSIZE keyword has been specified, but the supplied PASSWORD is four characters or fewer.
470 | Transaction log has already been restored.
471 | Failed to run sp_change_users_login report while checking for orphaned users.

Error codes

The following error codes may be returned by SQL Backup.

Error code

Description

500

General SQL Backup error.

505

No SQL Backup file name entered.

Check that the length of the path name does not exceed the number of characters for your operating system.

If you are backing up directly to a network share (not recommended), ensure the network share already exists, and that the startup user for the SQL Backup Agent Service has full permissions for the network share (see Using SQL Backup to back up to a network share).

To check the SQL Backup Agent Service permissions, run the following command:

execute master..sqbutility 999, 'RWE', '\\testsrv\backup'

If the agent has read (R), write (W), and execute (E) permissions, the command returns 1; 0 indicates failure.

510

Backup file exists. Will not overwrite. File name: <value>

Try specifying a file name format that does not result in identical file names. See The BACKUP command and File location tags.

If you want to overwrite the backup files, use the INIT argument.

512

Backup file name (<value>) contains an invalid character (<value>)

515

Multi database backup file names must contain the <AUTO>, <DATABASE> or <DATETIME> tags.

If you are backing up multiple databases, the backup file names must be unique. You use the <AUTO>, <DATABASE>, or <DATETIME> tags to do this. See The BACKUP command and Toolkit syntax examples.

516

Multi database operations cannot use MIRRORFILE option.

If you are backing up multiple databases, you cannot use the MIRRORFILE argument. See The BACKUP command.

517

Cannot use MAXDATABLOCK parameter with local files.

518

File name exceeds limit of 259 characters: <value>

519

Multi-database undo file names must contain the <DATABASE> tag.

If you are backing up logs for multiple databases and have specified the STANDBY keyword, the undo file names must be unique. You must use the <DATABASE> tag to do this. You can use other tags too, but the <DATABASE> tag must be present.
See The BACKUP command and Toolkit syntax examples.

520

Output file exists. Will not overwrite. File name: <value>

When you use the CONVERT command, you must specify a unique file name for the destination file.

525

Folder does not exist. Folder name: <value>

526

Cannot find saved setting.

Check that the SQL Backup Agent Service startup user has read access to the following registry key and its values: HKEY_LOCAL_MACHINE\Software\Red Gate\SQL Backup\BackupSettings (32-bit) or
HKEY_LOCAL_MACHINE\Software\Wow6432Node\Red Gate\SQL Backup\BackupSettings (64-bit)

530

Non-existent backup file.

Check the path of the backup file.

If you are restoring from a backup file on a network share, ensure the startup user for the SQL Backup Agent Service has permissions for the network share.

540

No file name entered for the Microsoft Tape Format (MTF) file.

When you use the CONVERT command, you must specify the path for the SQL Backup files (.sqb) and the path for the MTF file. See The CONVERT command and Toolkit syntax examples.

550

Cannot have the same file names for the SQL Backup file and the converted file.

560

File does not exist: <value>

Check the path of the backup file in the RESTORE SQBHEADERONLY command.

If the backup file is on a network share, ensure the startup user for the SQL Backup Agent Service has permissions for the network share.

562

Object is a folder, not a file: <value>

570

File is not a SQL Backup file.

Check the file you are restoring from is not a log file or native backup file.

580

Failed to open file. Message: <value>

The file may be in use or there may be a permissions problem; ensure the startup user for the SQL Backup Agent Service application has been granted the necessary permissions. See Security model.

585

Cannot use multiple file names with the SINGLEFILE option.

586

Failed to connect to SQL Server instance: <value>

587

Backup and restore operations are not allowed on database tempdb.

588

You can only perform a full backup of the master database.
Use BACKUP DATABASE to back up the entire master database.

589

Cannot back up the log of the master database.
Use BACKUP DATABASE to back up the entire master database.

590

Insufficient space to perform process. Space required: <value>

591

Cannot disconnect existing users from the model database.

592

The REPLACE option must be used when using the DISCONNECT_EXISTING option on an active database.

595

Failed to create MTF file: <value>

Ensure the startup user for the SQL Backup Agent Service application has been granted the necessary permissions. See Security model.

600

Multiple log file validation error for restore.

605

Error creating backup file(s). No files could be created.

Ensure the startup user for the SQL Backup Agent Service application has been granted the necessary permissions. See Security model.

610

Error creating file handle for file: <value>

Ensure the startup user for the SQL Backup Agent Service application has been granted the necessary permissions. See Security model.

612

Error reading file: unexpected end-of-file reached.

613

Compressed data block not found in file.

620

Error writing to backup file(s).

621

Error writing to backup file(s) due to insufficient disk space.

622

Error flushing contents of backup file(s).

623

Error setting file pointer position for backup file(s).

624

Error writing header (LSN data) to backup file (<value>): <value>.

625

Error writing header (backup size) to backup file (<value>): <value>.

627

Failed to set file pointer to beginning of file (<value>) to write backup size: <value>.

628

Failed to set file pointer to beginning of file (<value>) to read header data: <value>.

629

Failed to set file pointer to beginning of file (<value>) to write LSN data: <value>.

630

Cannot run a multi device backup with the MIRRORFILE option.

If you are backing up to multiple devices, you cannot use the MIRRORFILE argument. See The BACKUP command.

632

Failed to set file pointer to current position of file.

640

Cannot specify more than 32 devices.

You can specify up to 32 devices in a backup or restore operation. See The BACKUP command and The RESTORE command.

650

File already flushed to disk; cannot service another flush request.

651

File already flushed to disk; cannot process additional backup data.

653

File already flushed to disk, but buffer not empty.

660

VDI library not registered.

This may be because the VDI library has become unregistered when other SQL Server components have been uninstalled. To register this library manually, first locate the sqlvdi.dll file (for 64-bit versions of SQL Server, you need to locate the 32-bit version of the file). You can then use the regsvr32 command, for example:

regsvr32 sqlvdi.dll

If you are using more than one version of SQL Server, locate the latest copy of the sqlvdi.dll file; later versions of this file are compatible with earlier versions of SQL Server.

662

The version of the VDI library used does not match the version of SQL Server. Please install the correct version of sqlvdi.dll on this server.

For example, this may occur if you have reinstalled SQL Server 2000, and you are attempting to back up a SQL Server 2005 database.

665

Beta trial period has expired.

667

Trial period has expired.

Contact licensing@red-gate.com and tell us you are evaluating SQL Backup.

668

64-bit servers require Professional license.

Please contact sales@red-gate.com for more information.

680

The media set for database '<value>' has <value> family members but only <value> are provided. All members must be provided.

The backup set that you are trying to restore is split across multiple files; the number of files you have specified does not match the number of files in the backup set.

700

No process type entered.

710

Wrong password entered.

The RESTORE command contains an incorrect password, or if you are restoring from an encrypted backup file, the PASSWORD argument is missing.

715

Backup file is not encrypted.

You do not need to specify the PASSWORD argument in the RESTORE command.

716

None of the backup files are encrypted.

You do not need to specify the PASSWORD argument in the RESTORE LOG command.

720

No database name entered.

730

Command line gateway not defined.

740

Failed to get LSN data from server.

742

Failed to get LSN data from server (0 rows returned).

750

No LSN data returned from server.

760

LSN data from server is blank.

765

Failed to read file header (<value>): <value>. Read <value> bytes.

775

Failed to open file to write LSN data. Another process might be locking this file: <value>

SQL Backup writes a header block to each backup file upon completion of the backup. In some cases, antivirus applications may lock the backup file immediately after creation, denying SQL Backup access.

780

General conversion error.

790

General thread error.

800

File requires SQL Backup version <value> or higher to restore. Current version is <value>.

810

Error retrieving SQL Server login name: <value>

Check that the SQL Backup Agent Service startup user has read access to the following registry key and its values: HKEY_LOCAL_MACHINE\Software\Red Gate\SQL Backup\BackupSettings (32-bit) or
HKEY_LOCAL_MACHINE\Software\Wow6432Node\Red Gate\SQL Backup\BackupSettings (64-bit)

820

Validation of all backup files failed.

830

Cannot disconnect existing users when the log is not backed up with the NORECOVERY or STANDBY options.

850

SQL Backup syntax error.

870

No command passed to SQL Backup.

The command is empty.

880

BACKUP DATABASE permission denied in database: <value>

Check that you have backup rights to the relevant database.

890

RESTORE DATABASE permission denied for database: <value>

Check that you have the appropriate restore rights.

900

Failed to read backup file header (0 rows).

910

Failed to read file list details (0 rows).

915

Failed to update backup history table (BACKUPSET).

916

Failed to update restore history table (RESTOREHISTORY).

917

Failed to update backup history table (BACKUPSET). Media set ID is blank.

918

Failed to retrieve restore history id.

920

Failed to update backup history table (BACKUPMEDIAFAMILY).

930

File read error for compressed data. Backup file is incomplete or corrupted (FileBufferRemainingBytes: <value>)

932

File read error for compressed data size. Backup file is incomplete or corrupted (CompressedDataSize: <value>)

934

File read error for device index. Backup file is incomplete or corrupted (FileBufferRemainingBytes: <value>)

940

File read error for <value> bytes (balance <value> bytes). Backup file is incomplete or corrupted.

942

File read error for <value> padding bytes (balance <value> bytes). Backup file is incomplete.

950

Data decompression error: <value>

960

Failed to service command. Error code: <value>

970

Backup file does not exist. File name: <value> (for restores).

980

Your license does not allow restoring files encrypted with 256-bit keys.

985

Error creating critical section: <value>.

990

Invalid number of virtual devices: <value>

995

Error creating event for virtual device: <value>

1000

Failed to create virtual device. Error code: <value>

If the secondary error code is -2139684857, ensure that the SQL Backup Agent Service startup user has read access to the SQL Server service. You can do this using the sc sdset and sc sdshow commands, for example:

sc \\ServerName sdshow "sqlbackupagent"

For information about how to interpret the results, refer to your Windows® documentation.

1010

Failed to get configuration from server. Check that the SQL Server instance is running and that you have the SQL Server System Administrator server role. Error code: <value>. [Also check that the database is not currently in use.]

For certain timeout conditions, a more detailed description may be presented with this error code:

Failed to get the configuration from the server because the timeout interval has elapsed. Check that the SQL Server instance is running, that you have the SQL Server System Administrator server role, and that no other processes are blocking the backup or restore process. If the problem recurs, try increasing the value of the VDITimeout registry setting in HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\[InstanceName].

or,

Failed to get the configuration from the server because the timeout interval has elapsed. SQL Backup required <bytes> bytes of free SQL Server memory, which was not available. [You can reduce the memory requirements by reducing the number of backup files/threads used in the backup.

To configure the VDITimeout registry key, open the registry editor, locate the SQL Backup entries for the relevant SQL Server instance, and add a DWORD entry named "VDITimeout". Enter a duration that you feel is adequate for SQL Server to respond. The duration must be entered in seconds (in hexadecimal).

For more information, see this knowledge base article.

1020

Failed to open virtual device. Error code: <value>

1030

Failed to create VirtualDeviceSet component. Error code: <value>

1040

Error during backup / restore process.

1050

Failed to close VDI.

1060

Error cancelling command: <value>

1100

SQL Server error.

Refer to your SQL Server documentation.

2010

Command exceeds permitted length.

5000

General inter-process communication (IPC) error.

5100

Error creating SQB service event: <value>

5102

Error creating SQB service event (already exists): <value>

5110

Error opening SQB service event: <value>

5120

Error setting SQB service event: <value>

5130

Error creating client event: <value>

5140

Error creating mutex: <value>

5142

Error creating mutex (already exists): <value>

5145

Failed to start SQL Backup Agent service.

5146

Inadequate rights to start SQL Backup Agent service. Please start the SQL Backup Agent service manually.

5148

SQL Backup cluster resource is stopped.

The SQL Backup Agent Service is stopped on the active node in the cluster. This may be because a failover is in progress (in which case the service should restart automatically within a few minutes), or because the service has failed, or has been deliberately stopped.

You can re-enable the service with the Windows Cluster Administrator tool (Windows Server 2003) or Failover Cluster Management tool (Windows Server 2008).

5150

Error opening mutex: <value>

5160

Error acquiring mutex: <value>

5170

Error releasing mutex: <value>

5200

Error creating memory mapped file: <value>

5202

Error creating memory mapped file (already exists): <value>

5210

Error opening memory mapped file: <value>

5220

Error mapping to memory mapped file: <value>

5230

Error creating named pipe: <value>

5240

SQB service did not acknowledge receipt of data: <value>

5250

Failed to connect to Service Control Manager: <value>

5260

Failed to connect to SQL Backup service: <value>

5270

Error: SQL Backup Agent service is no longer running.

5280

Error creating client event.

5290

Error creating local data store mutex (already exists): <value>

5292

Error creating local data store mutex: <value>

*VDI errors 1000, 1010, 1020, 1030, 1040

If SQL Backup encounters VDI errors 1000, 1010, 1020, 1030, or 1040, SQL Backup attempts the backup again, reducing the MAXTRANSFERSIZE parameter in case the error was caused by an insufficient amount of contiguous memory on the SQL Server. SQL Backup attempts the backup five times in total, using the MAXTRANSFERSIZE values 1048576, 524288, 262144, 131072, 65536 respectively.


Red Gate product acknowledgements

SQL Backup 6.2

Acknowledgements

Trademarks and registered trademarks

Red Gate is a registered trademark of Red Gate Software Ltd registered in the U.S. Patent and Trademark Office.

SQL Compare is a registered trademark of Red Gate Software Ltd registered in the U.S. Patent and Trademark Office.

ANTS Profiler, Exception Hunter, SQL Backup, SQL Data Compare, SQL Comparison SDK, SQL Dependency Tracker, SQL Doc, SQL Log Rescue, SQL Multi Script, SQL Packager, SQL Prompt, SQL Refactor, SQL Response, and SQL Toolbelt are trademarks of Red Gate Software Ltd.

Microsoft, Windows, Windows 98, Windows NT, Windows 2000, Windows 2003, Windows XP, Windows Vista, Visual Studio, and other Microsoft products referenced herein are either registered trademarks or trademarks of Microsoft Corporation.

InstallShield is a registered trademark and service mark of InstallShield Software Corporation.

Copyright information

All Red Gate applications are © Red Gate Software Ltd 1999 - 2009

SQL Backup, SQL Compare, SQL Data Compare, SQL Packager, and SQL Prompt contain software that is Copyright © 1995 - 2005 Jean-loup Gailly and Mark Adler.

SQL Doc includes software developed by Aspose (http://www.Aspose.com).

SQL Backup contains software that is Copyright © 2003 - 2008 Terence Parr. Refer to the ACKNOWLEDGEMENTS.txt file in your SQL Backup installation directory for the full license text.


Non-TOC topics

© Red Gate Software Ltd. All Rights Reserved.


Compression levels

SQL Backup offers four compression levels, described below. Generally, the smaller the resulting backup file, the slower the backup process.

Smaller backups save you valuable disk space. For example, if you achieve an average compression rate of 80%, you can store the backup for a 42.5 gigabyte (GB) database on a 8.5 GB DVD-R dual layer disc. Smaller files can also be transferred more quickly over the network, which is particularly useful, for example, when you want to store backups off-site.

To set the compression level:

  • using the graphical user interface, select the required option in the Backup, Schedule Backup Jobs, or Log Shipping wizard, as appropriate.
  • using the SQL Backup toolkit, specify the BACKUP command COMPRESSION keyword (defaults to level 1)

Note that if SQL Backup Lite is installed on the SQL Server, you can choose only compression level 0 (no compression) or compression level 1.

The compression level used to create a backup does not noticeably affect the time necessary to restore the backup.

The compression you can achieve depends upon the type of data stored in the database; if the database contains a lot of highly-compressible data, such as text and uncompressed images, you can achieve higher compression. For full backups, you can use the Compression Analyzer to perform a test on the databases to check which compression level will produce the best result for your requirements.

Compression level 4

Compression level 4 uses the LZMA compression algorithm. This compression level generates the smallest backup files in most cases, but it uses the most CPU cycles and takes the longest to complete.

Compression level 3

Compression level 3 uses the zlib compression algorithm.

On average, the backup process is 25% to 30% faster than when compression level 4 is used, and 27% to 35% fewer CPU cycles are used. Backup files are usually 5% to 7% larger.

Compression level 2

This compression level uses the zlib compression algorithm, and is a variation of compression level 3.

On average, the backup process is 15% to 25% faster than when compression level 3 is used, and 12% to 14% fewer CPU cycles are used. Backup files are usually 4% to 6% larger.

Compression level 1

This is the default compression level. It is the fastest compression, but results in larger backup files.

On average, the backup process is 10% to 20% faster than when compression level 2 is used, and 20% to 33% fewer CPU cycles are used. Backup files are usually 5% to 9% larger than those produced by compression level 2.

However, if a database contains frequently repeated values, compression level 1 can produce backup files that are smaller than if you used compression level 2 or 3. For example, this may occur for a database that contains the results of Microsoft SQL Profiler trace sessions.

Compression level 0

If you do not want to compress your backups, specify compression level 0 from the command line or extended stored procedure; in the graphical user interface, clear the Compress backup check box in the wizard. For example, you may want to do this if you require only encryption and you do not want to compress your backups.

Compression percentage

SQL Backup calculates the percentage compression of a backup by comparing the size of the SQL Backup backup with the total database size.

For example, if a database comprises a 10 GB data file and a 3 GB transaction log file and SQL Backup generates a full backup of the database to create a backup file that is 3 GB, the compression for this backup is calculated as 77%, [1-(3/13)]x100.

The compression percentage is displayed in the Activity History

See also

Scheduling backup jobs

The Activity History

The BACKUP command

Compression analyzer

Creating backups

Configuring log shipping

Getting started


Creating backups: processing and encryption settings

Creating backups > Specify SQL Server > Select backup type and database > File settings > Processing and encryption settings > Review summary

This topic describes the wizard page for specifying the compression, encryption, and optimization options for your backups. It also describes the actions you can choose to run on completion of the backup. (Use the previous step of the wizard to specify a network location for a copy of the backup.)

Backup processing

To compress the backup, select the Compress backup check box and select the compression level by moving the slider. Note that if SQL Backup Lite is installed on the SQL Server and you choose to compress your backup, compression level 1 is selected and you cannot change it. For more information about compression levels, see Compression levels.

For full database backups, you can click Compression Analyzer to perform a test on the databases to check which compression level will produce the best result for your requirements. For more information about the Compression Analyzer, see Compression analyzer.

To encrypt the backup, select the Encrypt backup check box, then type a password for the backup in Password, and again in Confirm password.

If SQL Backup Pro is installed on the SQL Server, you can choose 128-bit or 256-bit encryption; if SQL Backup Lite is installed on the SQL Server, you cannot encrypt the backup. However, note that you can restore 128-bit and 256-bit encrypted backups using SQL Backup Pro or SQL Backup Lite.

You must remember your password; if you do not, you will not be able to access the encrypted backup.

Optimization

If you are backing up to a Single file or Single backup file, mirrored to second location, SQL Backup can use multiple threads to create the backups. This can speed up the backup process. Select the Use multiple threads check box, and type or select the number of threads up to a maximum of 32. For more information about using multiple threads, see Optimizing backup speed.

Maximum transfer size specifies the maximum size of each block of memory to be used when SQL Backup stores backup data. The default value is 1024 KB; you may want to change this value if a SQL Server reports that it has insufficient memory to service requests from SQL Backup.

Maximum data block specifies the maximum size of data blocks to be used when SQL Backup stores backup data. The default value is 4096 KB.

Note that if you want to specify the network transfer packet size, change the connection properties for the SQL Server.

The Network resilience settings control retry behaviour following a read/write error whenever SQL Backup:

  • writes a backup file to disk
  • copies a backup file to another disk location

Read/write errors are most likely to occur when SQL Backup is processing files through a network connection.

In most circumstances, you can leave On failure, retry after and Retry up to (n) times at their default values (30 seconds, and 10 times).

To disable retries completely, specify Retry up to 0 times.

On completion

If you want to receive an email with a copy of the completion log, select the Send email notification check box, and enter the recipient's email address. This option is available only if you have entered your email settings. For more information about setting up email notification, see Email notification options.

To send the log to multiple email addresses, type each address separated with a semi-colon (;). For example:

dba01@myco.com;dba02@myco.com

By default, email notifications are sent only when there are errors during the backup process (Error only). If you want to receive an email when an error or warning occurs, select Error or warning; to always receive an email on completion of the backup process (on success or failure), select Any outcome.

If SQL Backup Lite is installed on the SQL Server, you cannot use email notification.

Select the Verify backup files check box if you want SQL Backup to run a verification of the backup files when they have been created. When the backup process completes, the results of the verification are displayed in the Activity History. They are also saved in the SQL Backup completion log file.

Creating backups > Specify SQL Server > Select backup type and database > File settings > Processing and encryption settings > Review summary

See also

Getting started

Restoring backups

Reporting

Configuring log shipping

Creating backups

Scheduling backup jobs


Creating backups: file settings

Creating backups > Specify SQL Server > Select backup type and database > File settings > Processing and Encryption settings > Review summary

This topic describes the wizard page for specifying the locations and file names for your backups, and for managing existing backup files.

Choose:

  • Single backup file to create each backup as a single file.
  • Single backup file, mirrored to second location to create the backup as a single file and simultaneously make a duplicate of the backup during the backup process.

    This option is only available when you have selected a single database to back up. During the backup process, a warning is raised if any of the files cannot be written. However, the backup process continues as long as at least one specified backup file can be written. If none of the files can be written, an error is raised and the backup process is stopped.

  • Split backup into multiple files to store each backup across a number of files.

    Splitting the backup can speed up the backup process if backing up to a single file does not fully use the Input/Output capacity of your disks. For a single database backup, you can split (or 'stripe') your backup files across a number of disks. For multiple database backups, you can specify the number of files to split each database backup into (on the same disk). All backup files must be available when you restore the backup.

Backup location

For a Single backup file, if you have selected only one database to back up, a default folder and file name is displayed.

To change the file name, clear the Name file automatically check box, and type the new file name. To change the folder, type the new path or click [image] and specify the path using the folder browser. You can use tags in the folder and file name, if required.

For information about how to specify file names on remote servers, see Specifying file paths. Note that if you specify a network share as the backup file location (not recommended), the SQL Backup Agent Service 'log on' user must have Full permissions to access the location; instead, you are recommended to back up locally and use the Copy backup to network option. For more information, see the article Using SQL Backup to back up to a network share.

If you have selected multiple databases, the backup for each database is created in a separate file. The file names will be generated automatically using the <AUTO> tag. SQL Backup uses the default file name format specified in your Server Options; if you have not set up a default file name format, SQL Backup uses the SQL Server instance's default format for file names. A default folder for these files is displayed; to change the folder, type the new path or click [image] and specify the folder using the folder browser. If you want the backup file to be created in a different folder for each database, in the folder browser click Create subfolder for each database.

For a Single backup file, mirrored to second location, specify the backup file as described above, and additionally specify the folder and file name for the mirrored backups.

For Split backup into multiple files, if you have selected a single database, you must specify at least two destination files. Type or select each folder and file name as described above, and click Add to add it to the list.

If you have selected multiple databases, you can specify only one folder for the split backup files; the files will be named automatically.

In Files per backup, type or select the number of files into which you want each backup to be split (maximum 32).

Network copy location

If you want a copy of the backup files to be created on a network share when the backup has completed, select the Copy backup to network check box, and then type the network share details in the box or click [image] to use the folder browser. For information about how to specify file names on remote servers, see Specifying file paths.

If you typed the network share details, click Test to check that the 'log on' user for the SQL Backup Agent on the source SQL Server has permission to create files on the network share. If it does not have the appropriate permissions, an error message is displayed next to the button; if you choose to keep the network share details, a warning [image] is displayed in the Activity History when the backup has completed. If you browsed to the network share, this check is performed automatically.

For more information about using network shares with SQL Backup, see the article Using SQL Backup to back up to a network share. For details about setting up permissions to use a network share, see Security model.

You may experience problems when you back up to network shares if your SQL Server is unable to write large data blocks (over 2 MB) to the network share. You can use the MAXDATABLOCK keyword with the BACKUP command to limit the data block size.

Managing backup files

SQL Backup provides a number of settings to automate the management of your existing backup files. You can specify these settings separately for the initial backup location, and the network copy location.

Note that if you specify a network path in the backup location folder (not recommended), any network copy location options you specify will be applied to files at this path. Similarly, if you specify a local path in the network copy location folder, any backup location folder options will be applied to the copied files in the (local) network copy location.

Select the Overwrite existing backup files of the same name check box if you want to overwrite any files that exist with the share/path name you specified for the initial backup file or network copy. Note that if a file of the same name exists already and you have not chosen to overwrite it, the backup or network copy will fail.

Select the Delete existing backup files in this folder for the selected databases check box if you want SQL Backup to delete backups of the same type for the selected databases from the initial backup or network copy destination folders. You can restrict deletion of existing backup files by age (All files older than) or number (All files except the latest).

By default, files are deleted when the backup process or network copy has completed. If the backup fails, the files are not deleted. To delete the files before the backup is created, select the Delete files prior to start of backup check box. For example, you may want to do this to create space for the new backup files. However, note that if the backup fails, the old files will have been deleted; you are therefore recommended to select this check box only if you have a copy of the existing backups. This option is available for the initial backup location only; existing network copies of files are always deleted after the copying process has completed.

Note that if SQL Backup cannot list the contents of the folder that contains the files to be deleted, it cannot delete the files. Ensure that the user account from which you are running SQL Backup has permissions to list the folder contents.

Creating backups > Specify SQL Server > Select backup type and database > File settings > Processing and Encryption settings > Review summary

See also

Scheduling backup jobs

Creating backups

Configuring log shipping

Reporting

Restoring backups

Getting started


Creating backups: select the backup type and databases

Creating backups > Specify SQL Server > Select backup type and database > File settings > Processing and Encryption settings > Review summary

This topic describes the wizard page for selecting the type of backup you want to perform and the databases that you want to back up.

Backup type

Select the type of backup you require:

  • Full creates a complete copy of the selected database.
  • Differential creates a partial copy of the selected database.

    Only the changes since the last full backup was made are copied. You must create a full backup of the database before you can perform a differential backup.

  • Transaction log copies all the log records that have been written to the live transaction log since the first full or differential backup and the last transaction log backup.

    You can back up the live transaction log only if the database uses the FULL or BULK LOGGED recovery model, and you have previously created a full backup. To truncate the live transaction log, select the Remove inactive entries from transaction log check box.

  • Filegroups or files creates a backup of selected files and filegroups.

    For example, you may want to back up a file or filegroup when the database size makes a full database backup impractical. You can create a filegroup or files backup for only one database at a time. The database must use the FULL or BULK LOGGED recovery model. You will be prompted to select the files and filegroups when you click Next.

Databases to back up

For full, differential, or transaction log backups:

  • To select the databases that you want to back up, click Include these, and then select the check box next to each database that you want to back up.

    Selecting System or User selects all databases within that group. Included databases are marked with

  • To back up all databases except for individual specified databases, click Exclude these and select the check box next to each database that you do not want to back up.

    Excluded databases are marked with

For filegroups or files, you can select only one database; click the database in the list.

Note that only those databases that can be backed up with the backup type you selected are available. For example, if you selected Differential, only databases that have already had a full backup are available.

By default, all databases are displayed in the list, and those that are not available for selection given the selected backup type are displayed in gray. If you are using Exclude these, databases that are not available are automatically excluded. To see the reason why a database is not available, move your mouse pointer over the database name. In the example below, the Filegroups or files backup type has been selected.

You can filter the list so that only available databases are displayed in the list by selecting the Filter list to show only databases eligible for chosen backup type check box.

Click Next when you have selected the databases.

If you have chosen to back up files or filegroups, the wizard displays a page for you to select the files. The files and filegroups for the database you selected are displayed in a hierarchy. To select a file, select the appropriate check box. If you select the check box for a folder, all files and filegroups within that folder are automatically selected. When you have made your selection, click Next.

Creating backups > Specify SQL Server > Select backup type and database > File settings > Processing and Encryption settings > Review summary

See also

Getting started

Restoring backups

Reporting

Configuring log shipping

Creating backups

Scheduling backup jobs


Creating backups: specify SQL Server

Creating backups > Specify SQL Server > Select backup type and database > File settings > Processing and Encryption settings > Review summary

This topic describes the wizard page for selecting the SQL Server when you are creating a backup.

In the Server to back up list, click the name of the SQL Server instance for the databases that you want to back up. You can select a remote SQL Server instance only if SQL Backup Pro is installed on the SQL Server instance. SQL Servers that are not currently available (for example, because they are disconnected) are not displayed in the list.

Note that if the selected SQL Server does not have the server components installed, a warning is displayed and you must install the server components to proceed.

You can save the settings that you enter in the wizard as a template so that you can use them again. The option to save the settings as a template is displayed when you review the backup summary on the Review summary page of the Back Up wizard. If you have saved any templates, these are displayed in the right-hand pane.

To use a template, select the Use an existing template check box and click the name of the template in the list; when you click Next, the wizard will display those settings for you to edit if required. To delete a template, click the name of the template in the list and click Delete.

Creating backups > Specify SQL Server > Select backup type and database > File settings > Processing and Encryption settings > Review summary

See also

Scheduling backup jobs

Creating backups

Configuring log shipping

Reporting

Restoring backups

Getting started


Creating backups: review summary

Creating backups > Specify SQL Server > Select backup type and database > File settings > Processing and Encryption settings > Review summary

This topic describes the wizard page for reviewing your backup settings.

The Summary tab displays a simple report of the options you have set for the backup, so that you can check the details.

To see the script that will be used, click the Script tab.

You can then select the format in which to view the script:

  • Extended stored procedure shows the script you can use to run the backup when you are connected to the SQL Server using an application such as Microsoft SQL Server Management Studio, or connectivity tools such as ADO, OLEDB, ODBC.

    For information about how you can use the SQL Backup extended stored procedure to back up databases, see Using the extended stored procedure.

  • Command line shows the script you can use to run the backup from the command line.

You can save the settings to a template. You can then use the template when you next create a backup or when you schedule backup jobs. For example, you may want to use the Back Up wizard to perform a trial run of the backup; when you are happy with the settings, you can open the template in the Schedule Backup Jobs wizard to create a scheduled job.

You can also use the template from the command line or extended stored procedure using the USE command, if required. For more information about the USE parameter, see Toolkit parameters.

To save your settings to a template, select the Save as template check box and type a name for the template. The template will be saved when you click Finish. Note that if a template of the same name exists already, it will be overwritten.

When you have checked the settings, click Finish.

SQL Backup displays a message dialog box that shows the progress of the backup. Click Hide to minimize this dialog box and continue working. You can display the box again by clicking the arrow in the SQL Backup status bar.

The progress of the backup is also displayed in the In Progress tab.

When the backup process completes, if the message box is minimized, a pop-up message is displayed to inform you that the task is complete.

Information about the backup is then displayed in the Activity History. This information is also sent to a log file; you can view the contents of the log file by right-clicking the activity, and selecting Show Log. Log files are located in "C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log" by default; you can change this location in your log file options (see File management options).

If the backup process fails, you can optionally return to the final step of the wizard by clicking Retry in the message dialog. You can then change the settings in any of the wizard steps before starting the backup process again.

Creating backups > Specify SQL Server > Select backup type and database > File settings > Processing and Encryption settings > Review summary

See also

Scheduling backup jobs

Creating backups

Configuring log shipping

Reporting

Restoring backups

Getting started


Specifying file paths

SQL Backup often requires you to specify a file path or name, as, for example. in the Back Up wizard:

You can type the file path and name in the boxes. To specify a network path, type the full path, including the server name, for example \\ServerName\MyFolder\MyFile

Note that the file path is relative to the selected SQL Server. For example, if you have chosen to back up a database on a remote SQL Server instance called ServerA and you specify a local path such as C:\Backups, the backup files will be created on the C: drive on ServerA, not on the local server.

Alternatively, you can click [image] to browse to the folder or file you require. For example:

SQL Backup displays the local file system for the selected SQL Server.

To see the file system on other servers:

  1. Click the Server list to see if the server you want to access is listed (the local server is always available).
  2. Select the server from the list. If the server you require is not displayed in the list, click Add Server and type the name or IP address of the server to add.

    To remove a server from the list, select it and and click Remove Server.

Browsing SQL Servers with SQL Server authentication

If you are using SQL Server authentication on a SQL Server, for security reasons browsing to folders or files on the SQL Server is disabled by default. This is to prevent information about the file system structure being revealed to SQL Authenticated users who do not have permissions to browse the file system on the SQL Server.

You can override this restriction. However, you should do this only after careful consideration.

To enable file browsing by SQL Authenticated users, you must edit the registry of the SQL Server whose file system you want to browse. In the registry folder:

32-bit machines: HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>

64-bit machines: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>

create the registry key AllowSQLBrowsing as type DWORD, and set the value to 1. You must have administrator privileges to do this. This setting will not take effect until you refresh the connection to the instance.

See also

File management options


File location tags

SQL Backup enables you to set up file locations that are automatically generated when the backup is created, by using tags. For example, you can use tags to include the date and time of creation of a backup in its file name:

C:\Backups\<DATETIME yyyymmdd_hhnnss>_Backup.sqb

Tags are particularly useful when you are backing up multiple databases in one operation. For example, you could specify that the backup files for each database are to be created in a separate folder using the <DATABASE> tag in the path:

C:\Backups\<DATABASE>\Backup.sqb

You can use the following tags in path and file names:

<TYPE>

The backup type. SQL Backup will use the following values:

  • FULL for full backups
  • DIFF for differential backups
  • LOG for transaction log backups
  • FILE for filegroup or file backups

<SERVER>

The SQL Server name.

<INSTANCE>

The SQL Server instance name.

<DATABASE>

The database name.

<DATETIME x>

The date and time value of the backup process, where x is a format string. The values that you can use in the format string are listed below, under Date and time format. Note that you can use only one DATETIME tag in the default name.

<AUTO>

If you specify <AUTO>, SQL Backup uses the backup file location options (see File management options) to generate the backup file path and file name. If no backup location options have been set up, SQL Backup uses the SQL Server instance's default backup folder, and the default format for file names.

If you specify a path and <AUTO> (for example C:\MyBackups\<AUTO>), SQL Backup uses the specified path, and generates the file name using the Backup Location options. If no backup location options have been set up, SQL Backup uses the default format for file names.

If you specify <AUTO> with a file extension (for example <AUTO>.sqb, SQL Backup uses the Backup Location options to generate the backup file path and file name. If no backup settings have been set up, SQL Backup uses the SQL Server instance's default backup folder, and the default format for file names. This is useful when you generate split backup files.

Note that if you specify a format that may result in identical file names, for example you do not specify a <DATETIME> tag, the backups will fail if you have not chosen to overwrite the backup files. For more information about overwriting backup files, see Creating backups: file settings and Scheduling backups: file settings.

Date and time format

You can use the following formats for string x in the <DATETIME x> tag.

d | Displays the day as a number without a leading zero (1-31).
dd | Displays the day as a number with a leading zero (01-31).
ddd | Displays the day as an abbreviation (Sun-Sat) using the strings given by the ShortDayNames global variable.
dddd | Displays the day as a full name (Sunday-Saturday) using the strings given by the LongDayNames global variable.
ddddd | Displays the date using the format given by the ShortDateFormat global variable.
dddddd | Displays the date using the format given by the LongDateFormat global variable.
m | Displays the month as a number without a leading zero (1-12). If the m specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.
mm | Displays the month as a number with a leading zero (01-12). If the mm specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.
mmm | Displays the month as an abbreviation (Jan-Dec) using the strings given by the ShortMonthNames global variable.
mmmm | Displays the month as a full name (January-December) using the strings given by the LongMonthNames global variable.
YY | Displays the year as a two-digit number (00-99).
YYYY | Displays the year as a four-digit number (0000-9999).
h | Displays the hour without a leading zero (0-23).
hh | Displays the hour with a leading zero (00-23).
n | Displays the minute without a leading zero (0-59).
nn | Displays the minute with a leading zero (00-59).
s | Displays the second without a leading zero (0-59).
ss | Displays the second with a leading zero (00-59).

See also

The BACKUP command

Scheduling backup jobs

File management options

Configuring log shipping

Creating backups


Log shipping: backup settings

Log shipping > Specify databases > Backup settings > Restore settings > Network share > Schedule > Review summary

This topic describes the Log Shipping wizard page for specifying the locations and file names for your transaction log backups, for managing existing backup files, and for specifying backup processing settings such as compression.

Backup location

A default folder for the transaction log backups is displayed; the file names will be generated automatically. To change the folder, type the new path or click [image] and specify the folder using the folder browser. You can use tags in the path and file name, if required.

For information about how to specify folders on remote servers, see Specifying file paths. Note that if you specify a network share as the backup file location, the SQL Backup Agent service user must have Full permissions to access the location. For more information, see the article Using SQL Backup to back up to a network share.

File management

Select the Delete existing backup files in this folder for the selected databases check box if you want SQL Backup to delete backups of the selected database that exist in the destination backup folder. You can restrict deletion of existing backup files by age (All files older than) or number (All files except the latest).

By default, the files are deleted when the backup process has completed. If the backup fails, the files are not deleted. To delete the files before the backup is created, select the Delete prior to start of backup check box. For example, you may want to do this to create space for the new backup files. However, note that if the backup fails, the old files will have been deleted; you are therefore recommended to select this check box only if you have a copy of the existing backups.

Note that if SQL Backup cannot list the contents of the folder that contains the files to be deleted, it cannot delete the files. Ensure that the user account from which you are running SQL Backup has permissions to list the folder contents.

Backup processing

To compress the backups, select the Compress backup check box and select the compression level by moving the slider. For more information about compression levels, see Compression levels.

To encrypt the backup, select the Encrypt backup check box, then type a password for the backup in Password, and again in Confirm password. You can choose 128-bit or 256-bit encryption.

Optimization

SQL Backup can use multiple threads to create the backups. This can speed up the backup process. Select the Use multiple threads check box, and type or select the number of threads up to a maximum of 32. For more information about using multiple threads, see Optimizing backup speed.

On completion

If you want to receive an email with a copy of the completion log, select Send email notification, and enter the recipient's email address. This option is available only if you have entered your email settings. For more information about setting up email notification, see Email notification options.

To send the log to multiple email addresses, type each address separated with a semi-colon (;). For example:

dba01@myco.com;dba02@myco.com

By default, email notifications are sent only when there are errors during the backup process (Error only). If you want to receive an email when an error or warning occurs, select Error or warning; to always receive an email on completion of the backup process (on success or failure), select Any outcome.

Log shipping > Specify databases > Backup settings > Restore settings > Network share > Schedule > Review summary

See also

Creating backups

Configuring log shipping

Restoring backups

Reporting

Scheduling backup jobs

Getting started


Log shipping: network share

Log shipping > Specify databases > Backup settings > Restore settings > Network share > Schedule > Review summary

This topic describes the Log Shipping wizard page for specifying the network share to which the transaction log backups will be copied.

When SQL Backup has backed up the source database, the backup file is copied to a folder that can be accessed by the destination SQL Server. The SQL Backup Agent service on both the source and destination SQL Servers must have access to this folder. For more information, see the article Using SQL Backup to back up to a network share. For details about setting up permissions to use a network share, see Security model.

Type the name of the network share to which you want to copy the backup files, or click [image] and specify the network share using the folder browser. For information about how to specify file names on remote servers, see Specifying file paths.

If you typed the network share name, click Test to check that the user for the SQL Backup Service user on the source SQL Server has permissions to create files on the network share, and that the SQL Backup Service user on the specified destination SQL Server has permissions to access the network share and delete files. If the user for either SQL Backup Service does not have the appropriate permissions, an error message is displayed. If you browsed to the network share, this check is performed automatically.

Optimization

The Network resilience settings control retry behaviour following a read/write error whenever SQL Backup:

  • writes a backup file to disk
  • copies a backup file to another location

Read/write errors are most likely to occur when SQL Backup is processing files through a network connection.

The Network resilience settings apply to all read/write operations involving the transaction log backups. In most circumstances, you can leave On failure, retry after and Retry up to (n) times at their default values (30 seconds, and 10 times).

To disable retries completely, specify Retry up to 0 times.

Log shipping > Specify databases > Backup settings > Restore settings > Network share > Schedule > Review summary

See also

Getting started

Scheduling backup jobs

Reporting

Restoring backups

Configuring log shipping

Creating backups


Log shipping: restore settings

Log shipping > Specify databases > Backup settings > Restore settings > Network share > Schedule > Review summary

This topic describes the wizard page for specifying your settings for restoring the transaction log backups.

Destination database restore options

Select the state in which you want the destination database to be left on completion of the restore operation.

  • Non-operational

    The database is restored but not recovered. Incomplete transactions are not rolled back. The database is not usable, but transaction log backups can be restored to it. This is equivalent to using the SQL Server RESTORE WITH NORECOVERY option.

  • Read only

    The database is restored and recovered. Incomplete transactions are rolled back, but recorded in the Undo file. The data in the database can be read, but not modified. Transaction log backups can be restored to it. This is equivalent to using the SQL Server RESTORE WITH STANDBY option. To change the location of the Undo file, in the Undo file box, type the full path on the standby server for the file, or click [image] and specify the file using the file browser. For information about how to specify file names on remote servers, see Specifying file paths.

On completion

When they have been restored, SQL Backup moves the transaction log backup files from the network share (which you specify in the next step), so that they are not processed in the next run. Specify the path for the folder to which the processed transaction log backup files are to be moved.

SQL Backup displays a default path for the files. To change the path, type the full path for the folder, or click [image] and specify the folder using the folder browser.

For information about how to specify file names on remote servers, see Specifying file paths.

Select the Delete existing backup files in this folder for the selected databases check box if you want SQL Backup to delete existing backup files for the selected database in the specified folder. You can restrict deletion of existing backup files by age or number. To restrict deletion of files by age, select All files older than and type or select the age (in days or hours) of the files that you want to delete. To restrict deletion of files by number, select All files except the latest and type or select the number of the latest backup files to keep.

If you want to receive an email with a copy of the completion log, select Send email notification, and enter the recipient's email address. This option is available only if you have entered your email settings. For more information about setting up email notification, see Email notification options.

To send the log to multiple email addresses, type each address separated with a semi-colon (;). For example:

dba01@myco.com;dba02@myco.com

By default, email notifications are sent only when there are errors during the backup process (Error only). If you want to receive an email when an error or warning occurs, select Error or warning; to always receive an email on completion of the backup process (on success or failure), select Any outcome.

Log shipping > Specify databases > Backup settings > Restore settings > Network share > Schedule > Review summary

See also

Creating backups

Configuring log shipping

Restoring backups

Reporting

Scheduling backup jobs

Getting started


Log shipping: set the schedule

Log shipping > Specify databases > Backup settings > Restore settings > Network share > Schedule > Review summary

This topic describes the wizard page for specifying the schedule for the transaction log backups.

The schedule defines the time and frequency of the backups. For example, you may want to make transaction log backups every 5 minutes.

A default schedule is displayed for the backups. To change the schedule, click Edit, and on the Schedule Editor, specify the schedule and click OK.

You are recommended to use the backup schedule as a basis for the restore schedule. Click Base on transaction log backup schedule and in Offset, type or select the number of minutes by which you want to delay the restore operation after the backup has started. The offset time should be long enough to allow the backup to complete.

Alternatively, you can set up a separate schedule for the restore operations. Click Specify a schedule. To change the default schedule, click Edit and on the Schedule Editor, specify the schedule and click OK.

Log shipping > Specify databases > Backup settings > Restore settings > Network share > Schedule > Review summary

See also

Creating backups

Configuring log shipping

Restoring backups

Reporting

Scheduling backup jobs

Getting started


Log shipping: specify source and destination database

Log shipping > Specify databases > Backup settings > Restore settings > Network share > Schedule > Review summary

This topic describes the Log Shipping wizard page for selecting the SQL Server instance and database that you want to back up (the source database), and the SQL Server instance and database to which you want to restore the transaction logs (the destination database).

Note that if the selected SQL Server does not have the server components installed, a warning is displayed and you must install the server components to proceed.

In Step 4 of the wizard, you will specify the network share to which backup files will be copied. The user that the SQL Backup Service is running as on the source SQL Server must have permissions to create files on the network share you are going to use.

Similarly, the user that the SQL Backup Service is running as on the specified destination SQL Server must have permissions to access the network share and to erase files from the network share, so that it can move the files.

Source database

In the SQL Server list, click the name of the SQL Server instance for the database for which you want to ship transaction logs. You can select a SQL Server instance only if SQL Backup Pro is installed on the SQL Server instance.

In the Database list, click the database for which you want to ship transaction logs. The database cannot be in simple recovery mode or standby mode, and must not be a system database. It must also be online.

Destination database

Select the SQL Server on which you want to restore the transaction log backups.

You can then choose to restore the transaction logs to an existing database on the destination SQL Server, or to a new database.

Restoring to an existing database

To restore the transaction logs to an existing database on the destination SQL Server, select Use an existing database to restore to, and then select the name of the database.

To automatically kill any existing connections to the destination database before starting log shipping, select Kill any existing connections to the database. Log shipping will fail if there are existing connections to the destination database; if you do not select this option, you will need to kill existing connections manually.

By default, SQL Backup will create a full backup of the source database and restore it to the destination SQL Server prior to creating the log shipping tasks. This effectively overwrites the destination database, and ensures that the destination database is in the correct state for restoring the transaction log backups.

If you have already prepared your destination database so that it is ready to receive transaction logs, you should clear the Overwrite: initialize the destination database with a full backup of the source database check box. You must ensure that the destination database is in NO RECOVERY or STANDBY mode, and is populated with the most recent backup of the source database. You must also restore any subsequent backups taken on the source database before log shipping can start.

Restoring to a new database

To restore the transaction logs to a new database, select Create a new database to restore to. Then type a name for the new database and specify the folders in which you want the database's data and log files to be stored.

Log shipping > Specify databases > Backup settings > Restore settings > Network share > Schedule > Review summary

See also

Getting started

Scheduling backup jobs

Reporting

Restoring backups

Configuring log shipping

Creating backups


Log shipping: review summary

Log shipping > Specify databases > Backup settings > Restore settings > Network share > Schedule > Review summary

This topic describes the Log Shipping wizard page for reviewing your backup and restore settings.

The Summary displays the settings you have specified. When you have checked the settings, click Finish to create the SQL Server Agent jobs to perform the log shipping.

The Log Shipping wizard automatically sets up job steps using the RAISERROR command so that the SQL Server Agent reports an error if the SQL Backup command fails. For information about SQL Server Agent jobs, steps, and the RAISERROR command, refer to your SQL Server documentation.

SQL Backup displays the progress of the log shipping setup. SQL Backup performs a full backup of the source database and restores it on the destination SQL Server.

If any tasks fail, an error message is displayed, and all subsequent operations are failed. You can optionally return to the final step of the wizard by clicking Retry in the message dialog. You can then change the settings in any of the wizard steps before running the tasks again.

Log shipping > Specify databases > Backup settings > Restore settings > Network share > Schedule > Review summary

See also

Getting started

Scheduling backup jobs

Reporting

Restoring backups

Configuring log shipping

Creating backups


Optimizing backup speed

The backup process can be separated into three distinct stages:

  • Stage 1: SQL Server backup engine reads the data and log files.

    This incurs disk Input/Output (I/O) reads.

  • Stage 2: SQL Backup compresses, and optionally encrypts, the data.

    This uses CPU cycles.

  • Stage 3: SQL Backup writes the resulting compressed data files to disk.

    This incurs disk I/O writes.

To optimize the speed of your backup, you optimize each of these stages in turn:

  1. Use the SQL Backup command line or extended stored procedure to run a backup using the NOCOMPRESSWRITE argument (see The BACKUP command).

    When you use NOCOMPRESSWRITE, SQL Backup simulates a backup process without compression, and no backup files are created. This simulates Stage 1.

    The throughput for a backup process is shown on the SQL Server report. When you use the NOCOMPESSWRITE argument, this shows the maximum possible backup throughput attainable on your system.

    If you are using a multi-processor system, test the effect of using multiple threads. You are recommended to start with one thread fewer than the number of processors. For example, if you are using four processors, start with three threads.

    The limiting factor for Stage 1 is usually the speed at which your disk can read the data from the disks.

  2. Use SQL Backup to run a backup using the NOWRITE argument (see The BACKUP command).

    When you use the NOWRITE argument, SQL Backup simulates a backup process using the specified compression level and no backup files are created. This simulates Stage 1 and Stage 2.

    You can change the compression level to see the effect that the different levels have on the backup speed.

    To improve backup throughput, use multiple threads until you achieve a throughput close to that you achieved with the NOCOMPRESSWRITE argument.

  3. Run the backup process to completion, using the optimum number of threads found at Stage 2.

    To optimize Stage 3, store the backup data on a different set of disks from the disks used to store the data and log files. Depending upon the type of disk controllers, you have, you may need to reduce the number of backup devices so that you do not overload the disk I/O writes. Your aim is to balance the number of backup devices with the number of disks you can back up, to achieve a throughput close to that achieved at Stage 2. The Current Disk Queue Length performance counter tells you when you have reached the maximum capacity of your disks. Generally, the number should not exceed twice the number of drives on your disk array. If you achieve this, your backup process has been optimized.

Example

  1. A backup was run using NOCOMPRESSWRITE. SQL Server reported a throughput of 103 MB/sec. The number of threads was increased to two. Throughput remained at 103 MB/sec. Therefore, the limiting factor for this setup is the disk read speed, at 103 MB/sec.
  2. The same backup was run using NOWRITE, with compression level 1.

    SQL Server reported a throughput of 60 MB/sec. The number of threads was increased to two, and throughput increased to 99 MB/sec. The number of threads was then increased to three, and throughput increased to 103 MB/sec. This is the maximum disk read speed found at Stage 1.

    The process was then repeated for compression level 2. With one thread, throughput was 80 MB/sec; with two and three threads, throughput was 103 MB/sec.

    Therefore, optimum backup speed is achieved using compression level 2 with two threads.

  3. The same backup was then run to completion (without the NOCOMRESSWRITE or NOWRITE arguments) using compression level 2 with two threads.

    Throughput was 77 MB/sec. The number of threads was increased to 3, and SQL Server reported a throughput drop to 71 MB/sec. Therefore, two threads is optimum choice for this particular backup on this system. The Current Disk Read Queue performance counter averaged 13 for a 4 disk array.

See also

Compression levels

The BACKUP command

Using the command line

Using the extended stored procedure


Options

You can define a number of options for each SQL Server. The options are used throughout SQL Backup for that SQL Server. For example, you can specify the default folder for backup files and define the settings to be used for email notification.

In SQL Server, select the SQL Server instance for which you want to define the settings, then enter the details in each tab as required:

  • in File management, specify options for backup file locations, log file locations, and SQL Server backup history
  • in Email settings, enter your details for email notification (not available for SQL Backup Lite)

Click Apply to set the options defined on both tabs for the selected SQL Server. You can then select another SQL Server for which to set options. When you have finished, click OK to set the options and close the dialog box.

See also

Getting started

Installation overview


Performance expectations

This topic compares the native SQL Server backup and restore performance against similar operations performed by SQL Backup.

The numbers shown here should be used only as a broad indicator of expected performance. The performance you can achieve using SQL Backup depends upon your own setup, such as hard disk configuration and throughput. For details of how you can optimize the speed of your backups, see Optimizing backup speed.

Results for a 22 GB database

This test was run on a four processor server with 6 GB of RAM. A single backup file was created using eight threads.

 | Native backup | SQL Backup (compression level 1)
Backup: |  |
Speed: | 49.660 MB/sec | 101.517 MB/sec
File Size: | 19.781 GB | 7.766 GB
Elapsed time: | 7 min 7 sec | 3 min 29 sec
Restore: |  |
Speed: | 17.447 MB/sec | 43.465 MB/sec
Elapsed time: | 20 min 15 sec | 8 min 9 sec

Results for a 306 GB database

This test was run on an eight processor server with 12 GB of RAM. A single backup file was created using sixteen threads.

 | Native backup | SQL Backup (compression level 1)
Backup: |  |
Speed: | 83.849 MB/sec | 164.274 MB/sec
File Size: | 306.561 GB | 115.746 GB
Elapsed time: | 65 min 26 sec | 33 min 23 sec
Restore: |  |
Speed: | 29.682 MB/sec | 51.105 MB/sec
Elapsed time: | 184 min 50 sec | 107 min 20 sec

See also

Optimizing backup speed


Restoring backups: destination database

Restoring backups > Select backups > Destination database > Restore options > Review summary

This topic describes the Restore wizard page for specifying the destination database.

Restoring to an existing database

To restore the backups to an existing database, select Overwrite existing database and select the database. Note that any files listed under Filegroups and files will be overwritten; other files that are not shown in the list but previously belonged to the database will be deleted.

If you are restoring a full database backup or a filegroup backup, you can select any existing database. If you are restoring a differential database backup or a transaction log backup, only those databases that are in an 'unrecovered' or 'standby' state are available for selection.

To automatically kill any existing connections to the destination database before starting the restore process, select Kill any existing connections to the database. The restore process will fail if there are existing connections to the destination database; if you do not select this option, you will need to kill existing connections manually.

If you have selected an existing database that is:

  • in an 'unrecovered' or 'standby' state
  • or is not in an 'unrecovered' or 'standby' state, but uses the Simple recovery model (full or filegroup backups only)

there is no requirement that you back up the tail of the log before you restore the database. This is equivalent to using the REPLACE option in a Transact-SQL RESTORE statement. Refer to your SQL Server documentation for full details.

If you select a database that is not in an 'unrecovered' or 'standby' state but uses the Full or Bulk-Logged recovery model (full or filegroup backups only), when you click Next SQL Backup displays a page asking you whether you want to back up the tail of the log to capture all the transaction log records that have not already been backed up:

  • To back up the tail of the transaction log, click Backup up the transaction log before proceeding with the restore and then click Back Up Now.

    Note that if there are existing connections to the destination database, and you have not selected the Kill any existing connections to the database option in the previous step of the wizard, the transaction log backup (and subsequent restore) will fail. Either kill connections manually before backing up the transaction log, or return to step 1 of the wizard and select the Kill any existing connections to the database option. With this option selected, existing connections will be killed before the transaction log is backed up.

    A wizard is displayed for you to enter the backup settings you require. See Creating backups for details. Note that the script to back up the tail of the transaction log is displayed only in this wizard. When you click Finish on the wizard, the transaction log is backed up. Click Close on the message box, and then click Next to proceed with the Restore wizard.

    Refer to your SQL Server documentation for more information about backing up the tail of the transaction log.

  • If you do not want to back up the tail of the transaction log, click Discard transactions in the tail of the transaction log and click Next to proceed with the Restore wizard.

Restoring to a new database

If you are restoring a full database backup, you can select Create new database to create a new database. Type a name for the database in the box. To change the location in which the data files and the log file are created, click [image] next to the file or folder and specify the location. Note that you must ensure that you have sufficient free space in the new location.

Restoring backups > Select backups > Destination database > Restore options > Review summary

See also

Getting started

Creating backups

Reporting

Restoring backups

Scheduling backup jobs


Restoring backups: review summary

Restoring backups > Select backups > Destination database > Restore options > Review summary

This topic describes the Restore wizard page for reviewing the settings to be used when you restore the backups.

The Summary tab displays a simple report of the options you have set for the restore operation, for you to check.

To see the script that will be used, click the Script tab.

You can then select the format in which to view the script:

  • Extended stored procedure shows the script you can use to run the restore operation when you are connected to the server using an application such as Microsoft SQL Server Management Studio, or connectivity tools such as ADO, OLEDB, ODBC.

    For information about how you can use the SQL Backup extended stored procedure to restore databases, see Using the extended stored procedure.

  • Command line shows the script you can use to run the restore operation from the command line.

    For information about how you can use the SQL Backup command line to restore databases, see Using the command line.

Note that if you chose to back up the tail of the transaction log, the script to do this is displayed on the summary page of the Backup Transaction Log Tail wizard.

Refer to your SQL Server documentation for more information about backing up the tail of the transaction log.

When you have checked the settings, click Finish to start the restore process.

SQL Backup displays a message dialog box that shows the progress of the restore operation. Click Hide to minimize this dialog box and continue working. You can display the box again by clicking the arrow in the SQL Backup status bar.

The progress is also displayed in the In Progress tab.

When the restore process completes, if the message box is minimized, a pop-up message is displayed to inform you that the task is complete.

Information about the backup is then displayed in the Activity History. This information is also sent to a log file; you can view the contents of the log file by right-clicking the activity, and selecting Show Log. Log files are located in "C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log" by default; you can change this location in your log file options (see File management options).

If the restore process fails, you can optionally return to the final step of the wizard by clicking Retry in the message dialog. You can then change the settings in any of the wizard steps before starting the restore process again.

Restoring backups > Select backups > Destination database > Restore options > Review summary

See also

Getting started

Creating backups

Reporting

Restoring backups

Scheduling backup jobs


Restoring backups: select backups

Restoring backups > Select backups > Destination database > Restore options > Review summary

This topic describes the Restore wizard page for selecting the database that you want to restore, and the backup files to use.

Choose:

  • Select from backup history to select from a list of backups that were created on the target SQL Server (the SQL Server to which you want to restore the backup).
  • Browse for backup files to restore to select individual backup files using the file selector.

    For example, if the target SQL Server is different from the SQL Server on which you created the backup, or if you are restoring from a copy of the backup files, choose this option.

Select from backup history

Select the SQL Server and Database that was backed up.

Note that if the selected SQL Server does not have the server components installed, a warning is displayed and you must install the server components to proceed.

The backups for the selected database are listed. Select the backup you want to restore. (Note that backups that were created using SQL Backup version 4 are not listed; you can use Browse for backup files to restore to select these files.)

If you select a files or filegroups backup, you can select individual files or filegroups to restore at a later stage in the wizard.

When you click Next, SQL Backup retrieves the files for the selected backup. If you selected a transaction log backup or a differential backup from a backup set, SQL Backup automatically retrieves files for all backups in the set. For example, if you selected a transaction log backup, SQL Backup retrieves all transaction log backups since the corresponding full backup, and the full backup itself. You may see [image] displayed while SQL Backup searches for the appropriate files.

The files are listed with the most recent first. You can reorder the list by clicking the column headers as required; this will not affect the order in which they will be restored.

is displayed next to any encrypted files. Type the password in Password, and click Decrypt. For all files that use the password you entered, [image] is displayed. You must enter the passwords for all selected files before you click Next.

If there are any problems with a file, [image] is displayed next to the file. For example, SQL Backup checks that all the files exist and are not corrupted. To see details of the error, move your mouse pointer over the icon.

By default, all the files that SQL Backup has identified as part of the backup set are selected to be restored. If required, you can clear the Restore check box for a file. For example, you may want to do this if you know that the full backup has already been restored to a database. However, note that the restore process may fail if you do not restore all required files.

Browse for backup files to restore

You may want to back up a database on one SQL Server, but restore the backup to a different SQL Server.

Similarly, you may want to restore a backup from a copy of the backup files. (If the backup files are on a network share, you are recommended to copy the files to a local folder before restoring; for more information, see the article Using SQL Backup to back up to a network share.)

In these circumstances, you must choose Browse for backup files to restore. (If you choose Select from backup history, when you select the SQL Server to which you want to restore the backup, you will not see the backup you require in the Available Restore Points list because it is not associated with the target SQL Server.)

Select the SQL Server to which you want to restore the backups, and then click Add Files. The file browser is displayed for you to select the backup files. If necessary, click Add Server and type the name of the (physical) server on which the backup files are located. You can then select the files from the tree view.

When you click OK on the file browser, the files you have selected are listed in the Restore wizard.

is displayed next to any encrypted files. Type the password in Password, and click Decrypt. For all files that use the password you entered, [image] is displayed. You must enter the passwords for all selected files before you click Next.

If there are any problems with a file, for example a file is missing, [image] is displayed. To see details of the error, move your mouse pointer over the icon.

If there are any problems with your selection, for example if you have selected backup files from different backup sets or databases, a message is displayed above the list of files.

SQL Backup displays only one message at a time, therefore when you have fixed the displayed problem, you may see a second message.

To remove a file from the list, select the file and click Remove; to clear the list, click Remove All.

Files or filegroup backups

If you have selected a files/filegroups backup, when you click Next a tree of the available files is displayed.

Select the check boxes for any files or filegroups that you want to restore and click Next.

Restoring backups > Select backups > Destination database > Restore options > Review summary

See also

Getting started

Creating backups

Reporting

Restoring backups

Scheduling backup jobs


Restoring backups: restore options

Restoring backups > Select backups > Destination database > Restore options > Review summary

This topic describes the Restore wizard page for configuring the settings to be used when you restore the backups.

Recovery completion state

Select the state in which you want the database to be left on completion of the restore operation.

  • Operational

    The database is restored from the backup, and recovery is completed. Incomplete transactions are rolled back. The database is fully usable. Additional transaction logs or differential backups cannot be restored. This is equivalent to using the SQL Server RESTORE WITH RECOVERY option.

  • Non-operational

    The database is restored but not recovered. Incomplete transactions are not rolled back. The database is not usable, but differential backups and transaction log backups can be restored to it. This is equivalent to using the SQL Server RESTORE WITH NORECOVERY option.

  • Read only

    The database is restored and recovered. Incomplete transactions are rolled back, but recorded in the Undo file. The data in the database can be read, but not modified. Differential backups and transaction log backups can be restored to it. This is equivalent to using the SQL Server RESTORE WITH STANDBY option. To change the location of the Undo file, in the Undo file box, type the full path on the standby server for the file, or click [image] and specify the file using the file browser. For information about how to specify file names on remote servers, see Specifying file paths.

Transaction log

If you are restoring transaction logs, you can specify the exact time to which you want to restore the transactions. Select the Restore transaction log to point in time check box, and select a transaction log from the list. The slider shows the time period covered by the selected transaction log; move the slider to select the exact time to which you want to restore the transactions.

If you have selected a transaction log backup for which SQL Backup cannot find the preceding backup, SQL Backup cannot determine the start time of the period covered by the transaction log backup. You must therefore type the time and date of the point in time to which you want to restore the backup.

On completion

These options are applied once the restore process has completed.

Check for orphaned users and list in log file

If you are restoring a database onto a different SQL Server instance, it is possible that some of the restored database user accounts will not have an associated server login on that instance. These database user accounts are known as orphaned users, and will be unable to provide access to the database unless you create their associated logins.

Select the Check for orphaned users and list in log file check box to run a check for orphaned users when the restore job has completed. If any orphaned users are found, a warning is included in the SQL Backup log file, along with a list of the orphaned users.

SQL Backup does not fix orphaned users automatically. If orphaned users are reported, you should consider creating the required logins on the SQL Server instance that you are restoring to.

For more information about database user accounts, logins, and fixing orphaned users, refer to your SQL Server documentation.

Send email notification

If you want to receive an email with a copy of the completion log, select the Send email notification check box, and enter the recipient's email address. This option is available only if you have entered your email settings. For more information about setting up email notification, see Email notification options.

To send the log to multiple email addresses, type each address separated with a semi-colon (;). For example:

dba01@myco.com;dba02@myco.com

By default, email notifications are sent only when there are errors during the backup process (Error only). If you want to receive an email when an error or warning occurs, select Error or warning; to always receive an email on completion of the backup process (on success or failure), select Any outcome.

If SQL Backup Lite is installed on the SQL Server, you cannot use email notification.

Restoring backups > Select backups > Destination database > Restore options > Review summary

See also

Getting started

Creating backups

Reporting

Restoring backups

Scheduling backup jobs


The Schedule Editor

SQL Backup provides wizards to guide you through the process of setting up SQL Server Agent jobs using SQL Backup to perform the backup or restore operations for scheduling regular backup jobs, and for log shipping.

In these wizards, you use Schedule Editor to set up the required job schedules.

To set up a schedule, choose the type of schedule you want to set up from Schedule type. Select:

  • Recurring to set a schedule that recurs at a specified frequency in days, weeks, or months
  • Single occurrence to set a single date and time for a one-off scheduled backup
  • Occurs whenever SQL Server Agent starts to start the backup job whenever the SQL Server Agent is started
  • Occurs whenever the CPU becomes idle to start the backup job whenever the CPU becomes idle

As you enter the schedule details, a summary of the schedule is displayed at the top of the dialog box.

Recurring

  1. Under Frequency, in Occurs select the frequency with which you want the backups to be performed.
    • For Daily backups, type or select the frequency (for example, enter 2 to back up every other day, or 3 to back up on every third day).
    • For Weekly backups, type or select the frequency, and then select the check boxes for the days on which you want the backups to be performed.

      For example, to back up every fourth week on a Sunday, enter 4 and select the Sun check box.

    • For Monthly backups, type or select the date and frequency of the monthly backups.

      For example, to back up on the 10th day of every other month, click Day, and enter 10 and 2 respectively.

  2. Specify the frequency at which the backup is to be processed on the days you specified above.
    • To back up the databases once only on the specified days, select Occurs once at and type or select the time.
    • To back up the databases more than once on the specified days, select Occurs every and specify the frequency, and the start and end times.
  3. Under Duration, type or select the Start date for the job schedule.
  4. Select End date and type or select the date on which you want the job schedule to end, or select No end date if you want the job schedule to run indefinitely.
  5. Click OK to close the Schedule Editor and return to the wizard.

See also

Scheduling backup jobs

Configuring log shipping

Restoring backups

Creating backups

Getting started


Scheduling backups: processing and encryption settings

Scheduling backups > Specify SQL Server > Select backup type and database > Create backup schedules > File settings > Processing and encryption settings > Review summary

This topic describes the wizard page for specifying the compression, encryption, and optimization options for your backups. It also describes the actions you can choose to run on completion of the backup. (Use the previous step of the wizard to specify a network location for a copy of the backup.)

Backup processing

To compress the backup, select the Compress backup check box and select the compression level by moving the slider. Note that if SQL Backup Lite is installed on the SQL Server and you choose to compress your backup, compression level 1 is selected and you cannot change it. For more information about compression levels, see Compression levels.

For full database backups, you can click Compression Analyzer to perform a test on the databases to check which compression level will produce the best result for your requirements. For more information about the Compression Analyzer, see Compression analyzer.

To encrypt the backup, select the Encrypt backup check box, then type a password for the backup in Password, and again in Confirm password.

If SQL Backup Pro is installed on the SQL Server, you can choose 128-bit or 256-bit encryption; if SQL Backup Lite is installed on the SQL Server, you cannot encrypt the backup. However, note that you can restore 128-bit and 256-bit encrypted backups using SQL Backup Pro or SQL Backup Lite.

You must remember your password; if you do not, you will not be able to access the encrypted backup.

Optimization

If you are backing up to a Single file or Single backup file, mirrored to second location, SQL Backup can use multiple threads to create the backups. This can speed up the backup process. Select the Use multiple threads check box, and type or select the number of threads up to a maximum of 32. For more information about using multiple threads, see Optimizing backup speed.

Maximum transfer size specifies the maximum size of each block of memory to be used when SQL Backup stores backup data. The default value is 1024 KB; you may want to change this value if a SQL Server reports that it has insufficient memory to service requests from SQL Backup.

Maximum data block specifies the maximum size of data blocks to be used when SQL Backup stores backup data. The default value is 4096 KB.

Note that if you want to specify the network transfer packet size, change the connection properties for the SQL Server.

The Network resilience settings control retry behaviour following a read/write error whenever SQL Backup:

  • writes a backup file to disk
  • copies a backup file to another disk location

Read/write errors are most likely to occur when SQL Backup is processing files through a network connection.

In most circumstances, you can leave On failure, retry after and Retry up to (n) times at their default values (30 seconds, and 10 times).

To disable retries completely, specify Retry up to 0 times.

On completion

If you want to receive an email with a copy of the completion log, select the Send email notification check box, and enter the recipient's email address. This option is available only if you have entered your email settings. For more information about setting up email notification, see Email notification options.

To send the log to multiple email addresses, type each address separated with a semi-colon (;). For example:

dba01@myco.com;dba02@myco.com

By default, email notifications are sent only when there are errors during the backup process (Error only). If you want to receive an email when an error or warning occurs, select Error or warning; to always receive an email on completion of the backup process (on success or failure), select Any outcome.

If SQL Backup Lite is installed on the SQL Server, you cannot use email notification.

Select the Verify backup files check box if you want SQL Backup to run a verification of the backup files when they have been created. When the backup process completes, the results of the verification are displayed in the Activity History. They are also saved in the SQL Backup completion log file.

Scheduling backups > Specify SQL Server > Select backup type and database > Create backup schedules > File settings > Processing and encryption settings > Review summary

See also

Getting started

Creating backups

Reporting

Restoring backups


Scheduling backups: file settings

Scheduling backups > Specify SQL Server > Select backup type and database > Create backup schedules > File settings > Processing and encryption settings > Review summary

This topic describes the wizard page for specifying the locations and file names for your backups, and for managing existing backup files.

Choose:

  • Single backup file to create each backup as a single file.
  • Single backup file, mirrored to second location to create the backup as a single file and simultaneously make a duplicate of the backup during the backup process.

    This option is only available when you have selected a single database to back up. During the backup process, a warning is raised if any of the files cannot be written. However, the backup process continues as long as at least one specified backup file can be written. If none of the files can be written, an error is raised and the backup process is stopped.

  • Split backup into multiple files to store each backup across a number of files.

    Splitting the backup can speed up the backup process if backing up to a single file does not fully use the Input/Output capacity of your disks. For a single database backup, you can split (or 'stripe') your backup files across a number of disks. For multiple database backups, you can specify the number of files to split each database backup into (on the same disk). All backup files must be available when you restore the backup.

Backup location

For a Single backup file, if you have selected only one database to back up, a default folder and file name is displayed.

To change the file name, clear the Name file automatically check box, and type the new file name. To change the folder, type the new path or click [image] and specify the path using the folder browser. You can use tags in the folder and file name, if required.

For information about how to specify file names on remote servers, see Specifying file paths. Note that if you specify a network share as the backup file location (not recommended), the SQL Backup Agent Service 'log on' user must have Full permissions to access the location; instead, you are recommended to back up locally and use the Copy backup to network option. For more information, see the article Using SQL Backup to back up to a network share.

If you have selected multiple databases, multiple backup types, or both, the backup for each database and backup type is created in a separate file. The file names will be generated automatically using the <AUTO> tag. SQL Backup uses the default file name format specified in your Server Options; if you have not set up a default file name format, SQL Backup uses the SQL Server instance's default format for file names. A default folder for these files is displayed; to change the folder, type the new path or click [image] and specify the folder using the folder browser. If you want the backup file to be created in a different folder for each database, in the folder browser click Create subfolder for each database.

For a Single backup file, mirrored to second location, specify the backup file as described above, and additionally specify the folder and file name for the mirrored backups.

For Split backup into multiple files, if you have selected a single database and a single backup type, you must specify at least two destination files. Type or select each folder and file name as described above, and click Add to add it to the list.

If you have selected multiple databases, multiple backup types, or both, you can specify only one folder for the split backup files; the files will be named automatically.

In Files per backup, type or select the number of files into which you want each backup to be split (maximum 32).

Network copy location

If you want a copy of the backup files to be created on a network share when the backup has completed, select the Copy backup to network check box, and then type the network share details in the box or click [image] to use the folder browser. For information about how to specify file names on remote servers, see Specifying file paths.

If you typed the network share details, click Test to check that the 'log on' user for the SQL Backup Agent on the source SQL Server has permission to create files on the network share. If it does not have the appropriate permissions, an error message is displayed next to the button; if you choose to keep the network share details, a warning [image] is displayed in the Activity History when the backup has completed. If you browsed to the network share, this check is performed automatically.

For more information about using network shares with SQL Backup, see the article Using SQL Backup to back up to a network share. For details about setting up permissions to use a network share, see Security model.

You may experience problems when you back up to network shares if your SQL Server is unable to write large data blocks (over 2 MB) to the network share. You can use the MAXDATABLOCK keyword with the BACKUP command to limit the data block size.

Managing backup files

SQL Backup provides a number of settings to automate the management of your existing backup files. You can specify these settings separately for the initial backup location, and the network copy location.

Note that if you specify a network path in the backup location folder (not recommended), any network copy location options you specify will be applied to files at this path. Similarly, if you specify a local path in the network copy location folder, any backup location folder options will be applied to the copied files in the (local) network copy location.

Select the Overwrite existing backup files of the same name check box if you want to overwrite any files that exist with the share/path name you specified for the initial backup file or network copy. Note that if a file of the same name exists already and you have not chosen to overwrite it, the backup or network copy will fail.

Select the Delete existing backup files in this folder for the selected databases check box if you want SQL Backup to delete backups of the same type for the selected databases from the initial backup or network copy destination folders. You can restrict deletion of existing backup files by age (All files older than) or number (All files except the latest).

By default, files are deleted when the backup process or network copy has completed. If the backup fails, the files are not deleted. To delete the files before the backup is created, select the Delete files prior to start of backup check box. For example, you may want to do this to create space for the new backup files. However, note that if the backup fails, the old files will have been deleted; you are therefore recommended to select this check box only if you have a copy of the existing backups. This option is available for the initial backup location only; existing network copies of files are always deleted after the copying process has completed.

Scheduling backups > Specify SQL Server > Select backup type and database > Create backup schedules > File settings > Processing and encryption settings > Review summary

See Also

Getting started

Reporting

Restoring backups

Scheduling backup jobs


Scheduling backups: create backup schedules

Scheduling backups > Specify SQL Server > Select backup type and database > Create backup schedules > File settings > Processing and encryption settings > Review summary

This topic describes the wizard page for specifying schedules for each of the backup types you have selected.

Each backup type must have one or more schedules. The schedules define the time and frequency of the backups. For example, a backup type could have two schedules; one to perform the backups daily, and another to perform them weekly.

To create a schedule, click the tab for the backup type for which you want to create the schedule, and click Create. The Schedule Editor is displayed for you to define the schedule.

To edit an existing schedule, select the schedule in the appropriate tab, and click Edit; to delete an existing schedule, select the schedule and click Delete.

Scheduling backups > Specify SQL Server > Select backup type and database > Create backup schedules > File settings > Processing and encryption settings > Review summary

See also

Getting started

Reporting

Restoring backups

Scheduling backup jobs


Scheduling backups: select the backup type and database

Scheduling backups > Specify SQL Server > Select backup type and database > Create backup schedules > File settings > Processing and encryption settings > Review summary

This topic describes the wizard page for selecting the type of backup you want to perform and the databases that you want to back up.

Backup type

Select the type of backup you require:

  • Full creates a complete copy of the selected database.
  • Differential creates a partial copy of the selected database.

    Only the changes since the last full backup was made are copied. You must create a full backup of the database before you can perform a differential backup.

  • Transaction log copies all the log records that have been written to the live transaction log since the first full or differential backup and the last transaction log backup.

    You can back up the live transaction log only if the database uses the FULL or BULK LOGGED recovery model, and you have previously created a full backup. To truncate the live transaction log, select the Remove inactive entries from transaction log check box.

  • Filegroups or files creates a backup of selected files and filegroups.

    For example, you may want to back up a file or filegroup when the database size makes a full database backup impractical. You can create a filegroup or files backup for only one database at a time. The database must use the FULL or BULK LOGGED recovery model. You will be prompted to select the files and filegroups when you click Next.

Databases to back up

For full, differential, or transaction log backups:

  • To select the databases that you want to back up, click Include these, and then select the check box next to each database that you want to back up.

    Selecting System or User selects all databases within that group. Included databases are marked with

  • To back up all databases except for individual specified databases, click Exclude these and select the check box next to each database that you do not want to back up.

    Excluded databases are marked with

For filegroups or files, you can select only one database; click the database in the list.

Note that only those databases that can be backed up with the backup type you selected are available. For example, if you selected Differential, only databases that have already had a full backup are available.

By default, all databases are displayed in the list, and those that are not available for selection given the selected backup type are displayed in gray. If you are using Exclude these, databases that are not available are automatically excluded. To see the reason why a database is not available, move your mouse pointer over the database name. In the example below, the Filegroups or files backup type has been selected.

You can filter the list so that only available databases are displayed in the list by selecting the Filter list to show only databases eligible for chosen backup type check box.

Click Next when you have selected the databases.

If you have chosen to back up files or filegroups, the wizard displays a page for you to select the files. The files and filegroups for the database you selected are displayed in a hierarchy. To select a file, select the appropriate check box. If you select the check box for a folder, all files and filegroups within that folder are automatically selected. When you have made your selection, click Next.

Scheduling backups > Specify SQL Server > Select backup type and database > Create backup schedules > File settings > Processing and encryption settings > Review summary

See also

Getting started

Scheduling backup jobs

Creating backups

Reporting

Restoring backups


Scheduling backups: specify SQL Server

Scheduling backups > Specify SQL Server > Select backup type and database > Create backup schedules > File settings > Processing and encryption settings > Review summary

This topic describes the wizard page for selecting the SQL Server when you are scheduling backup jobs.

In the Server to back up list, click the name of the SQL Server instance for the databases that you want to back up. You can select a remote SQL Server instance only if SQL Backup Pro is installed on the SQL Server instance. SQL Servers that are not currently available (for example, because they are disconnected) are not displayed in the list.

Note that if the selected SQL Server does not have the server components installed, a warning is displayed and you must install the server components to proceed.

If you have previously saved any templates using the Back Up wizard, these are displayed in the right-hand pane.

To use a template, select the Use an existing template check box and click the name of the template in the list; when you click Next, the wizard will display those settings for you to edit if required. To delete a template, click the name of the template in the list and click Delete.

Scheduling backups > Specify SQL Server > Select backup type and database > Create backup schedules > File settings > Processing and encryption settings > Review summary

See also

Restoring backups

Reporting

Creating backups

Scheduling backup jobs

Getting started


Scheduling backups: review summary

Scheduling backups > Specify SQL Server > Select backup type and database > Create backup Schedules > File Settings > Processing and encryption settings > Review summary

This topic describes the wizard page for reviewing your job schedules.

The name of each backup job that will be created is displayed. You can edit the backup job names, if required, by typing in the appropriate box.

The Summary tab displays a simple report of the options you have set for the backup jobs, so that you can check the details.

To see the scripts that will be used, click the Script tab.

You can then select the format in which to view the scripts:

  • Extended stored procedure shows the scripts you can use to run the backups when you are connected to the SQL Server using an application such as Microsoft SQL Server Management Studio, or connectivity tools such as ADO, OLEDB, ODBC.

    For information about how you can use the SQL Backup extended stored procedure to back up databases, see Using the extended stored procedure.

  • Command line shows the scripts you can use to run the backups from the command line.

When you have checked the settings, click Finish.

SQL Backup displays a message dialog box that shows the progress of the job creation. Click Hide to minimize this dialog box and continue working. You can display the box again by clicking the arrow in the SQL Backup status bar.

When the job creation process completes, if the message box is minimized, a pop-up message is displayed to inform you that the task is complete.

The created jobs are then displayed in the Jobs tab.

If the job creation process fails, you can optionally return to the final step of the wizard by clicking Retry in the message dialog. You can then change the settings in any of the wizard steps before starting the job creation process again.

Scheduling backups > Specify SQL Server > Select backup type and database > Create backup Schedules > File Settings > Processing and encryption settings > Review summary

See also

Restoring backups

Reporting

Creating backups

Scheduling backup jobs

Getting started