Home Print  

Configure SQL Server Backup

Overview

supports backup of Microsoft SQL servers using SQL Server Plugin. SQL Server Plugin is a flexible and efficient option while backing up SQL servers.

's SQL Server plugin uses VDI API to backup database(s) and then uploads the locally dumped file to the backup server.

provides three types of backups for SQL Server database : Full, Differential and Transaction Log. Full backup type backs up the entire database. Differential backup type backs up only modified extents since the previous full backup. And Transaction log backup backs up the active portion and truncates the inactive portion of the transaction log. This section will give you information about how to configure MSSQL Server 2000/2005/2008 database(s) backup using .

You can also refer SQL Backup and Restore for more details on SQL Server backup and restore.

How to get here in the Web Console?
 In the top menu, select: "Backup -> Plugin Backups -> SQL Server".

SQL Server Connection Settings

"SQL Server Connection Properties" page is used to configure with SQL Server connection properties. will use the connection properties to connect to the SQL server while listing the database(s) or while taking SQL Server database(s) backup.

  • SQL Server Instance Name
    This choice-box will list all the SQL Server instances detected in the local machine. You should choose the instance of SQL Server should connect to. will connect to this instance and list the databases in this instance to enable you to configure backup schedules for the databases.

  • Continue with previous authentication for this SQL Server Instance
    This option is provided if a user name and password have already been configured for the SQL-Server instance. By default "Yes" option is selected and the "No" option could be used to change the previously saved "User Name" and "Password" values.

  • Authentication
    Choose the authentication type to connect to the SQL Server instance selected.

    Windows Authentication : Select this option if you have automatic access to the SQL Server through Microsoft Windows user names and passwords.

    SQL Server Authentication : Select this option to access the SQL Server with a user name and password that the SQL Server manages.

  • User Name
    If you have selected the SQL Server Authentication option, specify the user name here.

  • Password
    If you have selected the SQL Server Authentication option, specify the password here.

  • Next Button
    If all the authentication parameters are correctly set then clicking the Next button will take you to the SQL Server Backup Configuration page.

  • Cancel Button
    Closes the "SQL Server Connection Properties" page without accepting the selections. And the UI will display the "List of Backup Schedules already created" page.

SQL Server Backup Configuration

  • Step 1: Schedule Name

    Schedule Name: The first step in configuring the SQL Server backup is to give a name for the backup schedule. supports creating multiple backup schedules each with its own configuration. The backup schedule name will uniquely identify the backup. While restoring your backup data, you need to choose the data to be restored using its schedule name.

  • Step 2: Select Database(s)

    SQL Server Instance Name: This field is a non-editable name of the SQL Server Instance for which the backup needs to be configured. If you want to change the SQL Server instance you need to go back to the SQL Server Connection Properties UI and choose the instance name again.

    Select Database(s): Select the database name that needs to be backed up.

    SQL Server databases are classified as Full Recovery, Bulk-Logged Recovery and Simple Recovery Models. When a particular Recovery Model is selected, the databases in the SQL server falling under that Recovery Model will be listed for backup. You can select all of them or individually select the ones you want to backup.

    If a database is included in a backup schedule, then that particular database cannot be selected in another backup schedule; as the data might get inconsistent when backed up across two different backup schedules. Similarly, a database should not be configured with more than one backup software for the same reason.

    Select All Databases for this schedule

    If "Select All Databases for this Schedule" option is chosen, it will include all the databases present in the SQL server. If a new database is later added to the SQL Server, it will be automatically included in this backup schedule.

    Select All Full Recovery Model databases

    If "Select All Full Recovery Model databases" option is chosen, it will include all the databases with Full Recovery model from the SQL Server. If a new Full Recovery model database is later added to SQL Server, it will be automatically included in this schedule.

    The Full Recovery Model provides extensive backup and restore capabilities. Full, differential and Transaction Log backup of the databases are supported.

    To restore a Full Recovery Model database, a full backup has to be first restored. On top of the full backup, differential and/or Transaction Log backups could be restored to recover the database to the latest backup state.

    Select All Simple Recovery Model databases

    If "Select All Simple Recovery Model databases" option is chosen, it will include all the databases with Simple Recovery Model from the SQL Server. If a new Simple Recovery model database is later added to SQL Server instance, it will be automatically included in the backup schedule.

    The Simple Recovery Model provides the simplest form of backup and restore. Full or differential backup of the databases could be done. Transaction log backups are not available for databases belonging to this model.

    To restore a Simple Recovery Model database, a full backup has to be first restored. On top of the full backup, differential backups could be restored to recover the database to the latest backup state.

    Select All Bulk-Logged Recovery Model databases

    If "Select All Bulk-Logged Recovery Model databases" option is chosen, it will include all the databases with Bulk-Logged recovery model from the SQL Server. If a new Bulk-Logged recovery model database is later added to SQL Server, it will be automatically included in the backup schedule.

    Similar to a Full Recovery Model database, Bulk Logged Model databases provide extensive backup and restore capabilities. Full, differential and Transaction Log backup of the databases are supported. However, compared to the Full recovery model, which logs all transactions, the bulk-logged recovery model minimally logs bulk operations. Therefore, recovery is possible only to the end of a transaction log backup when the log backup contains bulk changes.

    Like Full Recovery Model database, to restore a Bulk Logged Model database, a full backup has to be first restored. On top of the full backup, differential and/or Transaction Log backups could be restored to recover the database to the latest backup state.

    Let me manually choose databases

    If "Let me manually choose databases" option is chosen, then any databases can be selected for backup irrespective of the recovery model they belong to.

    If a database does not support Differential/Transaction Log backups, then that Differential/Transaction Log backup will be skipped for that database and the error message will be reported in backup report.

    The database 'tempdb' need not be configured as part of any of the above mentioned schedules. However, if it was configured for a backup, it will be skipped internally when the backup is scheduled. 'tempdb' is used internally by SQL Server just for saving temporary data.

  • Step 3: Set Basic Configuration

    Backup Type

    Same Machine - You can backup this client machine to a drive attached to this client machine for the following two purposes:

    1. When large amount of data has to be backed up, it might take significant amount of time to transfer all the data to a remote backup server over WAN/Internet. You can take the backup to a local external drive connected to the client machine and then physically move the external drive to the backup server location to migrate the data into the backup server. In such cases, you can select the backup type to be 'Same Machine'. Once the data is migrated to the backup server using the Server Side Seed Backup Migration, you will have to perform Seed Backup Migration (under Settings -> Seed Backup Migration) in the client machine to make it to run its backups to the remote server directly thereafter.

    2. If you want to keep a local backup copy that you want to restore quickly when needed.

    Local Backup location: Enter the location/directory path where the backup is to be stored in the same machine. This location should be accessible to process and should have enough free disk space for the backup data. You can copy data from this location to the backup server as seed backup data. Or, you can leave the backups in the local drive if you want to maintain a local copy of the backup that you can quickly restore.

    NOTE: Deleted File Retention policies and Time Based version file Retention policy will not apply for a local backup. Only Version-Based Retention is supported for a local backup schedule. The Retention policies configured for this backup schedule will work only if the backup data is migrated from the local backup server/external drive into a remote backup server (seed backup).

    Remote Server - Select this option if you want the backup data to be transferred to a backup server directly when the backup schedule runs.

    Also keep a copy locally (optional) :

    Enabling this option will backup the data to the backup server and also keep a copy of the backup data in a local location. First the data will be backed up to the backup server and on completion of the backup, the copy of the backed up data will be saved in the specified local location. The local copy will provide for faster restores and redundancy. You can always do a restore from the backup server as well.

    Select Backup Server :

    This is the list of peers/backup servers in which the backup data can be stored. Once the backup server is selected, you cannot change the server name. In case the backup server name or IP address is to be changed in future, you can change it from the Seed Backup Migration page. The client will run the backup to the selected backup server.

    Test connectivity - Before you select the backup server for this backup schedule, you can check the connectivity to backup server. It also checks the different settings configured in the backup server for this client and confirms if this client can backup to the chosen backup server or not.

    Add Backup Server- If you don't see a particular backup server listed, you need to first add it by following this link before you can select it for this backup schedule.

    Temporary location for backup dumps
    Enter the location/directory path to which the MSSQL Server database dumps has to be stored temporarily before uploading to the backup server. Please ensure that you have enough disk space in the configured temporary location (where the backup dump has to be saved). Please note that, backup dumps stored in the temporary location will be deleted once backup dumps are transferred successfully to the remote backup server.
    Encryption

    Password Protect this Backup

    This option is to enable or disable encryption of the data that is being backed up. uses a well known encryption algorithm, Blow fish. If enabled, a password has to be specified which will be used for generating the encryption key. Encrypting data ensures that it cannot be read in the destination machine (backup server) where your backup is stored, and is especially relevant for sensitive information.

    Password Protection

    This option is to enable System Generated Encryption Key or use a Customized Encryption key for encrypting the backup data.

    System Generated: The selected files and folders will be encrypted using Blow fish algorithm with a 448 bit encryption key automatically generated by before been backed up. No password will be required while restoring the data. Make sure you use this option only when the backup server is setup such that the backup server requires the client machines to authenticate before accepting the backup from the client.

    Custom: This will enable the user to select the custom Encryption Technique, Encryption Key Size and the Encryption Key that needs to be used for encrypting the selected files and folders. Please don't lose the customized encryption key, as the backed up data cannot be restored without this key.

    Default Encryption Password: The selected files and folders will be encrypted with the Default Encryption Password as provided in the "Settings -> Advanced Options -> Default Encryption Password" page. Default Encryption Password is a convenient way to use the same password for all the backup schedules created in this client machine. It saves you from having to remember a different password for each backup set.

    Encryption Technique

    supports encryption using Blow fish or Triple DES or AES. The Blow fish is a commonly used open source encryption technique which provides encryption based on the size of the encryption key length (from 64 bits to 448 bits). Higher the number of bits stronger is the encryption. Triple DES is a block cipher formed from the Data Encryption Standard (DES) cipher by using it three times, AES is a Cipher Block Chaining from the Advanced Encryption Standard (AES), this technique is also supported by for encrypting the backup data. By default, AES encryption technique is chosen.

    Encryption Key Size

    supports encryption using variable length (64 to 448 bits for Blow fish and 192 bits for Triple DES and 128 bits for AES) keys. The key is generated using the password you provide. Basically the longer the key size you give the more difficult it is for someone to decrypt the encrypted files. For normal backup purposes the default 64 bit encryption should be good enough.

    Type Password

    Provide a password here to generate the encryption key. If the password is forgotten the data can never be recovered as the data cannot be decrypted without this password. So, please ensure that you keep your password in a safe place.

  • Step 4: Full Backup Scheduling (Decide When To Do Full Backup)

    Configuring this step is mandatory. will do a full backup dump of the database whenever the full backup is scheduled and back it up.

    This step has the following options from which any one can be chosen.

    Run Monthly At - Choose the time and day of the month the backup should run. will run the backup every month on the particular day and time chosen. Unless you’ve selected the “Run this Backup each time restarts” option in Advanced Settings, will run at the stipulated time/day only.

    Run Weekly - Choose the time and the day of the week the backup should run. will execute the backup every week on the particular day and time chosen. Note that you can select multiple days of the week if you wish to run the backup on multiple days of the week. Unless you’ve selected the “Run this Backup each time restarts” option in Advanced Options, will run at the stipulated time/day only.

    Run Daily - Choose the time at which the backup should run daily. This will execute the backup everyday at the time you have provided. Unless you’ve selected the “Run this Backup each time restarts” option in Advanced Options, will run at the stipulated time only.

    Run Every Few Hours - Choose the hours/minutes interval between each time must run. If is restarted in the interim, the backup will run once starts. Thereafter, automatically reschedules the next backup after the period you’ve set. For example, if you created a backup schedule at 10 am and set it to run every 1 hour, will first run the schedule at 10am and the next scheduled time will be 11am. However, if at 10.15am, is restarted, then the schedule will run once at 10.15am and the next scheduled time will now be set to 11.15am.

    Additionally, you can also select the days in which this schedule should run. For example, if you do not want to run this backup schedule on Saturdays and Sundays, uncheck the checkbox against these days. If the "Select all days" checkbox is checked, then the schedule will be run on all the days of the week.

    Run Once Only - Choosing this option will do the backup only once at the stipulated time.

  • Step 5: Differential Backup Scheduling (Optional)

    Configuring this step is optional. will do a differential backup dump of the database with respect to the previous full backup whenever this differential backup is scheduled.

    This step has the following options from which any one can be chosen.

    Run Weekly - Choose the time and the day of the week the backup should run. will execute the backup every week on the particular day and time chosen. Note that you can select multiple days of the week if you wish to run the backup on multiple days of the week. Unless you’ve selected the “Run this Backup each time restarts” option in Advanced Options, will run at the stipulated time/day only.

    Run Daily - Choose the time at which the backup should run daily. This will execute the backup everyday at the time you have provided. Unless you’ve selected the “Run this Backup each time restarts” option in Advanced Options, will run at the stipulated time only.

    Run Every Few Hours - Choose the hours/minutes interval between each time must run. If is restarted in the interim, the backup will run once starts. Thereafter, automatically reschedules the next backup after the period you’ve set. For example, if you created a backup schedule at 10 am and set it to run every 1 hour, will first run the schedule at 10am and the next scheduled time will be 11am. However, if at 10.15am, is restarted, then the schedule will run once at 10.15am and the next scheduled time will now be set to 11.15am.

  • Step 6: Transaction Log Backup Scheduling (Optional)

    Configuring this step is optional. will do a transaction log backup dump of the database with respect to the previous backup (full/differential/transaction log) whenever this transaction log backup is scheduled.

    This step has the following options from which any one can be chosen.

    Run Weekly - Choose the time and the day of the week the backup should run. will execute the backup every week on the particular day and time chosen. Note that you can select multiple days of the week if you wish to run the backup on multiple days of the week. Unless you’ve selected the “Run this Backup each time restarts” option in Advanced Options, will run at the stipulated time/day only.

    Run Daily - Choose the time at which the backup should run daily. This will execute the backup everyday at the time you have provided. Unless you’ve selected the “Run this Backup each time restarts” option in Advanced Options, will run at the stipulated time only.

    Run Every Few Hours - Choose the hours/minutes interval between each time must run. If is restarted in the interim, the backup will run once starts. Thereafter, automatically reschedules the next backup after the period you’ve set. For example, if you created a backup schedule at 10 am and set it to run every 1 hour, will first run the schedule at 10am and the next scheduled time will be 11am. However, if at 10.15am, is restarted, then the schedule will run once at 10.15am and the next scheduled time will now be set to 11.15am.

    Additionally, you can also select the days in which this schedule should run. For example, if you do not want to run this backup schedule on Saturdays and Sundays, uncheck the checkbox against these days. If the "Select all days" checkbox is checked, then the schedule will be run on all the days of the week.

  • Advanced Settings (Optional)

    If you want to configure some additional advanced options - like, CPU utilization, Compression settings, etc. click on the Advanced Options button.

    Advanced Backup Schedule Settings

    Maximum Number Of Full Backups : You can specify the maximum number of full backups that need to be stored in the server. For example if you configured a monthly full backup, then will take a full backup every month. After 6 months you will have 6 separate full backups in the backup server along with differential and transaction logs under each of the 6 full backups. If your policy is to retain only backup data for 6 months, then you can configure "Store maximum of 6 full backups" here. On the 7th month, , after finishing the 7th full backup, will delete the first full backup along with its differential and transaction log backups thus retaining always the last 6 month backups.

    Differential Backup Count : If the MSSQL Server differential backups are configured more frequently [Run every few hours] in SQL Server backup and the MSSQL Server databases are changing rapidly, then the clients backup space usage will grow faster. To avoid this, you can specify maximum number of Differential backups to be kept in the server. If the number of differential backups count exceeds this count then the oldest differential backup file and the transactions logs taken between that differential backup and its successive differential/full backup job are deleted. The default value is 5 and minimum value is 2.

    If you are upgraded from 2.1 version, then the older SQL Server backup differential backup files will be deleted automatically when a new differential backup configure.

    Read and verify the integrity of the backup on completion : If you enable this option, then SQL Server will perform a verification of the backup once it completes.

    Run this backup immediately after saving this configuration (thereafter, Backups will be scheduled as per the schedule stipulated) : If you enable this option, then this backup schedule will be executed once immediately after you save this backup schedule – even if its not yet ‘time’ as per the schedule. For example if you configure a daily backup at 10.00 AM but you configured the backup at 8.00 AM, then the backup schedule will be run once immediately (8.00 AM) and then as per schedule (eg. daily at 10.00 AM).

    Attempt to run this backup every 5 minutes in case of non completion because of a problem (Network error etc.) : If enabled, the user can choose to reschedule this backup every 5/10/15/20/25/30 minutes, whenever an attempted backup is incomplete because of a network error, server crash etc. The number of times the backup will be rescheduled (due to an error) can be configured to be in between 1-5 or unlimited, with the default setting as 5. This is to ensure that an incomplete backup is completed as soon as possible without waiting for the next actual schedule time. Note that this option is enabled by default.

    Data Integrity Check : supports data integrity check where the data sent from the client machine is verified for integrity at the backup server. When a file's integrity check fails, flags that file as skipped and the file is then again backed up during the next backup schedule.

    Other Backup Settings

    Set CPU Utilization

    For each backup schedule, you can also specify the CPU utilization level as Low, Medium or High. When you set the CPU Utilization to high, the backup will be really fast - but the backup process may affect the speed of other active applications. If CPU utilization is set to Low, the backup will take longer but will be done in a non-intrusive manner allowing you to comfortably work on the computer as the backup continues in the background. In general for a continuous backup, it is best to set the CPU utilization to Medium or Low. For backups which are done every day or every week in ‘off-hours’, the CPU utilization can be set to High. Actual performance is obviously a function of your individual machine and your network. We encourage you to experiment with different settings to figure what works best for you.

    Set Compression

    If compression is enabled, data will be compressed in the client before it is uploaded into the backup server.

    Enable - Compresses the data before backing it up. It might reduce the rate at which the client can send data to the backup server but it might save significant bandwidth and time when the data is backed up to a remote server over a WAN/Internet with limited bandwidth speed. Also, this saves disk space in the backup server.

    No Compression - This option is suitable (fastest) for backing up data within a local network where bandwidth is not the limitation, but occupies more space on destination machine as no compression is being done.

    Saving the configured backup

    Once all the SQL Server database(s) to be backed up are chosen and the configuration options are set, you simply need to click on the "Configure/Save Backup” button to activate the backup as per the schedule- on to the configured backup servers.

Troubleshooting Tips

The following message is displayed in the backup report. "SQL Server Backup aborted because of error in getting the virtual device backup configuration. Check your database properties.". Show/Hide

Cause 1 : If 'SQL Server' service is running in Local System account.

Solution 1 : Please check whether 'SQL Server' service is running in Local System account. If the SQL Server service is running in Local System Account, please change the SQL Server service's logon account to some other user account(Administrator) who has the full admin rights. You can change the SQL Server service's logon account type by following the steps:

a) Open the Services control applet through "Control Panel -> Administrative Tools -> Services" shortcut or by running "services.msc" command in "Start -> Run" tool.
b) Select the SQL Server service and then open its Properties dialog.
c) In the SQL Server services Properties dialog, go to the "Logon" tab and update the user logon to be of the power user or a user who has full admin rights to list the SQL Server databases.
d) After providing the correct user name and password details, click 'Apply' and 'Ok' to apply the changes.
e) Restart the SQL Server service.

After changing the SQL Server service logon account, re-schedule the SQL Server backup and check whether the issue resolved.

Cause 2 : If System resource utilization is high or Multithread backup is enabled in and one more backup is trying to start database dump process at a time.

Solution 2 : Adjust the "TimeOut" attribute value of "BeforeRun" tag in the plugin configuration file [< Path>/plugins/AdvMSSQLServer.sgpl] by following the instructions given below:

- Stop
- Edit < Path>/plugins/AdvMSSQLServer.sgpl
- Set the <StoreGrid><Configuration><Module> tag, "TimeOut" attributes value to desired value.
- Save the configuration file and restart

The following message is displayed in the backup report. "Check registration of SQLVDI.DLL and value of IID. This failure might happen if the DLL was not registered.". Show/Hide

Cause : If the mssql server being upgraded from MSSQL Server 7, then there will be a problem in loading dll required for VDI backup.

Solution : You need to change the threading model in the registry entry. For more details about this, please refer the following url :- http://support.microsoft.com/kb/323602


The backup schedule fails with connection failure message [SG083, SG132...] in the backup report. Show/Hide

Cause : When is doing the local dump of the SQL server data, the client's socket connection to the backup server will be idle. Depending upon the time the dump process takes, some firewall/router/NAT settings may close this idle socket connection after a timeout period. Hence, after the dump process, when tries to transfer the dump file to the backup server, the connection is found to be invalid and the backup schedule fails.

Solution : Enable the Keep Alive setting for the backup in the configuration file [< Path>\conf\SGConfiguration.conf] by following the instructions given below:

- Stop
- Edit < Path>\conf\SGConfiguration.conf
- Set the <StoreGrid><Configuration><Module> tag, "KeepAliveEnabled" attribute's value as "1" and set the "KeepAliveTime" attribute value to desired value
- Save the configuration file and restart

When I try to create a MSSQL Server backup, i can see the SQL Server database(s) but the check box to click on is all greyed out. Show/Hide

Reason : If you had configured a MSSQL Server database(s) for backup in a backup schedule, the same should not be configured as a part of another backup schedule and therefore the checkboxes will be greyed out. The other backup schedule should be deleted first and there after it can be configured for backup in another backup schedule.

MSSQL Server changes the reference of the database dump when two schedules try to backup the same SQL Server Database and hence the backed up data of both the backup schedules might become inconsistent and therefore cannot be restored when backed up across two schedules.

Limitations

  1. If locally dumped SQL Server database(s) backup data is deleted manually while backup is in progress, then will not have the information about the manually deleted file in client machine backup report and in server backup report.

  2. Local Dump path configured for SQL Server database(s) backup is not recommended to have special characters like single quote('), double quote("), forward slash(/), backward slash(\), question(?), colon(:), asterisk(*), lesser than(<), greater than(>), pipe(|) and plus(+).

  3. Please ensure that the dump location configured for the backup schedule has enough space and has valid read and write permission to accommodate the SQL Server database dump data. Also, the dump location (full path) should be less than 256 characters.

  4. If is killed while SQL Server database(s) backup schedule configured with multiple database(s) dump process is active, then in the next schedule locally dumped files for the database will be transferred to backup server and dump process for the remaining database(s) will be processed in the next schedule.

  5. If SQL Server database(s) backup schedule is suspended while local dump is in progress, then will suspend the backup after completing the current MS SQL database dump process. On resuming the same backup schedule, the dump process will not occur in the next schedule; instead, the locally dumped files will be transferred to server and an error message will be reported in backup report.

  6. In multiple databases backup, if a database does not support Differential/Transaction Log backups, then that Differential/Transaction Log backup will be skipped for that database and the error message will be reported in backup report.

  7. Number of differential backups count should be greater than or equal to 2.

  8. If the local dump location is configured as a FAT32 disk partition, then the local dump file size should not exceed 4 GB as the FAT32 file system cannot hold file size more than 4 GB. In this case, cannot dump the data files and the backup schedule will fail.

  9. Currently you can seed the local data to the remote server only. After performing the seed migration, you can continue this backup schedule only to the remote server, not to both remote server and local backup.

Print  
Technical support-