Microsoft SQL Server Backup Configuration

Use the following guidelines when creating your Microsoft SQL Server backup configuration in MyLiveVault for the LiveVault Online Backup and Recovery Service.

Configuring SQL Server backup

To back up a SQL Server:

  • Create a separate SQL Server database backup configuration.

  • Select the directories and volumes that contain the SQL data, transaction logs, and application files.

    In a default installation of SQL Server 2000 this is usually:

    C:\Program Files\Microsoft SQL Server\mssql\*

    In a default installation of earlier SQL Server versions this is usually:

    C:\mssql\*

    The directory paths shown indicates the paths that would be specified for backup. Your directory path may be different from that depicted above; make the appropriate adjustments.

  • Exclude the SQL Server backup directories and their subdirectories.

    LiveVault Online Backup and Recovery Service Agent version 3.3 and later automatically exclude the SQL Server backup directories and their subdirectories (as specified in the registry key below).  If you are running an earlier Agent version, you must manually create the exclusions. Also, other SQL backup directories may be used which are not specified in the registry key. If you are using other directories, you need to manually create the exclusions.

    In the tree view on the Files to Backup tab, you will see the icon for the automatically excluded directory and subdirectories. Parent directories of the automatically excluded directory are marked in MyLiveVault with one of the "partially excluded" icons, either (if a parent directory is selected), or (if a parent directory is not selected).

    The SQL Server backup directory is specified in the registry key:

    HKLM\Software\Microsoft\MSSQLServer\MSSQLServer value BackupDirectory

    For SQL Server 7.0 this is commonly:

    C:\SQL\Backup

    For SQL Server 2000 this is commonly:

    C:\Program Files\Microsoft SQL Server\mssql\backup

    For example, if the BackupDirectory value is C:\Program Files\Microsoft SQL Server\mssql\backup, create the exclusion:

    Exclude C:\Program Files\Microsoft SQL Server\mssql\backup\* (plus subdirectories)

    Important: If you manually create exclusions for the SQL Server backup directories, you must create exclusions for these directories on both the SQL database configuration and the Files and Directories configuration. This is because if you only exclude the directories from the database backup configuration, and the Files and Directories configuration is backing up the full computer (the default setting), then the Files and Directories will automatically back up those directories.

  • We generally recommend setting your schedule to continuous backup, 24x7, so your changes are always being backed up. However, you can set any schedule you desire.

Important: It is important to back up the Windows Registry/System State for your computer. If you do not back it up, you will not be able to perform a disaster recovery, and you may not be able to restore your databases or applications. The Registry/System State is backed up by default as part of your Files and Directories backup configuration.

SQL transaction log truncation

Unlike traditional backup products, the LiveVault Online Backup and Recovery Service backup technology does not rely upon the SQL standard logging to ensure complete and accurate backups or restores. As such, LiveVault Online Backup and Recovery Service does not truncate or remove the SQL logs.

You must truncate the log or the computer may run out of disk space. For example, you may set up SQL maintenance to truncate the transaction logs each day. Refer to your SQL documentation for information about truncating the transaction logs.

SQL Server backup utility

In addition to using LiveVault Online Backup and Recovery Service to back up your SQL data to an offsite location, you may choose to use the SQL Server backup utility to back up the databases to disk for multiple intra-day copies that are available locally. This allows you to perform restores locally using the SQL utility without having to move data over the Internet. This complements the ability to restore current and historical versions of your SQL data using LiveVault Online Backup and Recovery Service, as well as recovering your SQL Server in the event of a disaster.

We do not recommend using the LiveVault Online Backup and Recovery Service to back up the SQL backup files. Each time you perform a SQL backup to disk (using the SQL backup utility), the SQL backup files change 100%. If the SQL backup files are included for backup by LiveVault Online Backup and Recovery Service, then LiveVault Online Backup and Recovery Service will back up those full files each time you perform a SQL backup. This will consume a significant amount of bandwidth, which can affect backup performance for all your data. Depending upon the size of the SQL backup files, the amount of SQL and other data you are also backing up and the speed of your Internet connection, the additional load of backing up the SQL backup files could possibly prevent your other data from being backed up in an acceptable amount of time. Also, this additional backup load is unnecessary and redundant, as you are already backing up your SQL data with LiveVault Online Backup and Recovery Service.

However, if you choose to have LV Service back up the SQL backup files for offsite storage, then you need to contact Customer Service for assistance identifying the amount of bandwidth that will be necessary for successful backup. If we determine that your Internet connection can handle the increased data, you will create a separate backup configuration for the SQL backup directories and schedule it to run after your SQL backup utility completes. This method will have less impact on the backup of your SQL data and other data.

Related Information

Microsoft SQL Server Restore
Excluding Files from Backup
Automatic and Recommended Backup Exclusions
 
© 2002, 2004 LiveVault Corporation. All rights reserved.