Configure SQL Server Backup
Overview
StoreGrid supports backup of Microsoft SQL servers using two types of plug-ins: 1. The Basic Plugin and 2. Advanced Plugin. Advanced Plugin is much more flexible and efficient while backing up SQL servers than the basic plugin though basic plugin may be good enough for backing up SQL servers with relatively less amount of data.
Basic SQL Server Plugin uses OSQL utility to dump the database(s) and then uploads the locally dumped file to the backup server.
Advanced SQL Server plugin uses VDI API to backup database(s) and then uploads the locally dumped file to the backup server.
StoreGrid 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 database(s) backup using StoreGrid.
SQL Server Connection Settings
"SQL Server Connection Properties" page is used to configure StoreGrid with SQL Server connection properties. StoreGrid 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 StoreGrid should connect to. StoreGrid 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 And Database
Enter Backup Schedule Name: The first step in configuring the SQL Server backup is to give a name for the backup schedule. StoreGrid 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.
Plugin Type: Drop-down box will be listed with default value as "Advanced SQL Server Plugin" if the advanced version of the (SQL + Exchange) plugin is currently enabled in "Advanced Options > Plugin Settings" page. With the advanced version of the plugin enabled, user will have an option to choose either the Basic or the Advanced plugin backup for "SQL Server Backup". If the advanced version of the (SQL + Exchange) plugin is currently disabled in "Advanced Options > Plugin Settings" page, then this field will be a non-editable name and set to "Basic SQL Server Plugin".
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 2: Set Basic Configuration
Backup Type : In StoreGrid, there are two ways of configuring a SQL Server backup:
Same Machine: When the databases contain large amount of data, it might take significant amount of time to transfer all the data to a remote backup server over WAN/Internet. In such cases, you can take backup of the databases 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, you will have to perform Local to Remote Migration (under Administration -> Advanced Options) in the client machine to make it to run its backups to the remote server directly thereafter. Please note: You will not be able to restore from the backup data present in the local external drive. It is meant only for quickly moving the large amount data to the remote backup server. Once the data is migrated to the remote server from the external drive, the client can restore from it .
Local Backup location:Enter the location/directory path to which the backup has to be stored in the same machine. Please ensure that you have enough disk space in the configured location (where the SQL Server database backup has to be saved).
Remote Server: Select this option if you want the backup of the databases to be transferred to the remote backup server directly when the backup schedule runs.
Select Backup Server - This is the list of peers/backup servers in which the backup data can be stored. Only one backup server is allowed for a SQL Server backup job. If you wish to configure backup to multiple backup servers, please create separate backup jobs for each server.
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 everyday 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 : Should the data be compressed or not.
Enable - Compresses the data to the maximum. 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.
Disable - 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.
Temporary location to store the 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.
Note - Basic SQL Server Plugin users should configure the temporary dump location in a local drive and not a mapped network drive.
-
Step 3: Set Password Protection
Password Protect This Backup - This option is to enable or disable encryption of the data that is being backed up. StoreGrid uses a well known encryption algorithm, Blowfish. 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.
Encryption Key Size - StoreGrid supports encryption using variable length (64 to 448 bits) 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. StoreGrid 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. StoreGrid will run the backup every month on the particular day and time chosen. Unless you’ve selected the “Run this Backup each time StoreGrid restarts” option in Advanced Settings, StoreGrid will run at the stipulated time/day only.
Run Weekly - Choose the time and the day of the week the backup should run. StoreGrid 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 StoreGrid restarts” option in Advanced Options, StoreGrid 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 StoreGrid restarts” option in Advanced Options, StoreGrid will run at the stipulated time only.
Run Every Few Hours - Choose the hours/minutes interval between each time StoreGrid must run. If StoreGrid is restarted in the interim, the backup will run once StoreGrid starts. Thereafter, StoreGrid 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, StoreGrid will first run the schedule at 10am and the next scheduled time will be 11am. However, if at 10.15am, StoreGrid 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. StoreGrid 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. StoreGrid 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 StoreGrid restarts” option in Advanced Options, StoreGrid 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 StoreGrid restarts” option in Advanced Options, StoreGrid will run at the stipulated time only.
Run Every Few Hours - Choose the hours/minutes interval between each time StoreGrid must run. If StoreGrid is restarted in the interim, the backup will run once StoreGrid starts. Thereafter, StoreGrid 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, StoreGrid will first run the schedule at 10am and the next scheduled time will be 11am. However, if at 10.15am, StoreGrid 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. StoreGrid 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. StoreGrid 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 StoreGrid restarts” option in Advanced Options, StoreGrid 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 StoreGrid restarts” option in Advanced Options, StoreGrid will run at the stipulated time only.
Run Every Few Hours - Choose the hours/minutes interval between each time StoreGrid must run. If StoreGrid is restarted in the interim, the backup will run once StoreGrid starts. Thereafter, StoreGrid 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, StoreGrid will first run the schedule at 10am and the next scheduled time will be 11am. However, if at 10.15am, StoreGrid 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.
-
Step 7: Advanced Options
There are four options here on how the backup should be scheduled.
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 StoreGrid 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, StoreGrid, 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 StoreGrid 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 StoreGrid 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, this backup will be rescheduled to run after 5 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 between 1–5, 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.
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
Limitations
- If locally dumped SQL Server database(s) backup data is deleted manually while backup is in progress, then StoreGrid will not have the information about the manually deleted file in client machine backup report and in server backup report.
- 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(+).
- Editing the Local temporary Dump location configured for SQL Server database(s) backup schedule to another location is not supported. You need to create a new backup schedule with a new dump location.
- 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.
- If StoreGrid 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.
- If SQL Server database(s) backup schedule is suspended while local dump is in progress, then StoreGrid will suspend the transfer of files to the backup server (till after the local dump process is completed). 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.
- 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.
- Number of differential backups count should be greater than or equal to 2.
- 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, StoreGrid cannot dump the data files and the backup schedule will fail.
|
Home 
Print
