Backup / Restore of Microsoft SQL Server (VSS-aware backup sets)
Creation Date: May 01, 2010
Revision Date: June 29, 2016
Product: DS‑Client (Windows)
Summary
This article covers various backup / restore issues for Microsoft SQL Server backups (using the VSS-aware backup set type).
See Also
Backup of a Microsoft SQL Server Cluster
MASTER database does not support Differential backup
VSS-aware backup sets support only backup of the master database from any Microsoft SQL Server instance with the Full Database backup policy.
If you add the master database to a VSS-aware backup set with the Full+Differential policy selected, the backup will fail on every ‘differential’ backup attempt.
Database Backup Policy
Database backup policy options can help reduce the backup time for very large databases (where the dump takes up significant time).
You can configure the backup set to perform differential database backups. Differential backups contain all the changes to the database since the last full dump.
Full dump: Always | [Default] This is the default behavior for all Microsoft SQL Server backups. Each backup of the SQL Server performs a full dump (VSS Snapshot) of each database. |
Full dump: Plus Differential | Performs a full dump (VSS Snapshot) of the database on first backup, followed by differential backups until another full backup is needed (*). |
(*) A full dump is needed (other than first time) when: • a database has been backed up by another backup set (or manually from the SQL tools); • a database has been restored (either from another database or an earlier generation); • the database properties have been modified (recovery model switched to ‘simple’); • a set number of consecutive differential backups have been performed, unless over-ruled with the ‘Do not start full dump’ option. This uses the same number set in the DS-Client Advanced Configurations “MasterGenerations” parameter (note: this parameter is also used for another purpose). |
NOTE: DS-Client can take advantage of the differential backup policy if the backup set (of the SQL database) is the only process that backs up that target. If another backup set is used to backup the database (or the internal SQL commands are used to perform manual backups / dumps), then the backup policy requires the next backup to be a full dump.
Database Backup Policy Full Backup Schedule Over-ride Rules:
• These rules are optional. They allow greater control when a Full Backup can occur (e.g. not during business hours).
IMPORTANT: Full dumps can be delayed, but a full database dump needs to be performed at regular frequency to protect the integrity of the database backup. By default, DS-Client allows a full backup to be skipped a maximum of 120 consecutive times. This number is configurable in the DS-Client Advanced Configuration options ‘MaxNonFullDumps’ parameter (see
“DS-Client Advanced Configurations”).
Integrating with Nimble Storage
You can create Microsoft SQL Server (VSS-aware) backup sets that can be integrated with Nimble Storage to create persistent hardware snapshots of the Nimble Storage volumes. Users can store the databases on Nimble Storage volumes and then restore only the selected databases from the Nimble Storage snapshot or revert the entire volume.
When you integrate with Nimble Storage, DS-Client creates snapshots in up to three locations:
• DS-System
• Production Nimble Storage location
• Nimble Storage replication partner (Optional)
DS-Client enforces retention on the snapshots at these locations based on your configuration.
NOTE: To specify how the copying of data from the snapshot to the destination is triggered, you can configure the
DSClientPerformCopyFromSnapshot advanced configuration parameter. For more information, see
“DS-Client Advanced Configurations”.
Prerequisites for Nimble Storage integration
Before you can integrate a Microsoft SQL Server (VSS-aware) backup set with Nimble Storage, you must do the following:
1. Ensure that the Nimble Storage Windows Integration Toolkit has been installed on the Microsoft SQL Server host. For information, see the Client Software Installation Guide.
2. Ensure that the VSS Nimble Provider has been installed on the Microsoft SQL Server host. For information, see the Client Software Installation Guide.
3. Manually create one or more volume collections, depending on your environment and user requirements. Configure each volume that contains the database(s) you plan to create snapshots for with a volume collection, and create an externally triggered schedule for each collection.
4. (Optional) If you want to enable replication, configure a Nimble Storage replication partner in the Nimble Storage Management Console. You must provide the name or IP address of the replication partner on which you want the hardware snapshots to be created.
5. If you have configured CHAP on the volumes where the Nimble Storage resides, you must provide the CHAP credentials for DS-Client to use. You can only provide one set of CHAP credentials for each Microsoft SQL Server (VSS-aware) backup set. All volumes added to the backup set must be configured with the same set of CHAP credentials.
NOTE: Determine the retention settings that you need in the production Nimble Storage, in the Nimble Storage replication partner, and in DS-System. You will be asked to specify the maximum snapshots to be kept at each of these locations.
Restoring a full database from Nimble Storage snapshots
When a Microsoft SQL Server (VSS-aware) backup set has been integrated with Nimble Storage, you can restore a full database from the hardware snapshots on the Nimble Storage array.
If you are restoring a full database from a production Nimble Storage and want to automatically establish the connection between the Microsoft SQL Server host and the production Nimble Storage where the hardware snapshot is stored, select Use automount for restore in the Restore Now Wizard.
If you are restoring a full database from a Nimble Storage replication partner, you must do the following before starting the restore process:
• In the Nimble Storage Management Console, set the snapshot online. This refers to the snapshot from which you want to restore the full database.
• Connect the hardware snapshot from the Nimble Storage to the Microsoft SQL Server host via the iSCSI initiator.
• If the volume containing the hardware snapshot is configured with CHAP, you must provide the required credentials.
• In the Microsoft SQL Server host, mark the disk online and assign a drive letter to the disk. This refers to the disk that contains the target database in the Microsoft SQL Server host.
Like other
Microsoft SQL Server backup sets, a
Microsoft SQL Server (VSS-aware) backup set that is integrated with Nimble Storage can be used in combination with another
Microsoft SQL Server (Classic) backup set configured with the
Transaction Log Only database backup policy. For more information, see
“Using the Transaction Log Only database backup policy”.
Limitations of Nimble Storage integration
The following limitations apply to Nimble Storage integration with Microsoft SQL Server (VSS-aware) backup sets:
• Full dump always is the only database backup policy that supports integration with Nimble Storage for Microsoft SQL Server VSS-aware backup sets.
• When the production Nimble Storage contains multiple partitions, the automount option for restoring a full database from Nimble Storage snapshots is not supported.
• The automount option for restoring from Nimble Storage snapshots is supported for restoring from the production Nimble Storage only.
Data integration issues
Each SQL database is presented as a component by the SQL VSS Writer. In the VSS-based “SQL restore”, a component must be restored with all its contents (the file snapshot) based on the rules specified in its Metadata.
• These issues are similar to those in VSS-aware backups of Microsoft Hyper-V.
Truncate Transaction Log behavior
The truncation of Microsoft SQL Server databases transaction logs is automatically handled by the SQL Server engine.
• For databases configured in Simple recovery mode, the transaction logs should be truncated whenever a full backup is taken (whether using traditional SQL backups or VSS-aware ones).
• For databases configured in Full or Bulk-logged recovery mode, the transaction logs should be truncated whenever a transaction log backup is performed, if a checkpoint has occurred since the previous backup.
Since Microsoft does not support VSS-aware transaction log backups for Microsoft SQL Server, the database administrator will need to manually take transaction log backups using various tools, like DS-Client using traditional SQL Server backups, or SQL Server Management Studio, to truncate them.
Note that whenever log truncation occurs, it will only free up the space within the log files, but not reduce the physical files' size. There are also various factors that can delay the transaction logs truncations (see Microsoft's site for more details).
Alternate Restore Location Issues for Pure Files
VSS-aware backups have the ability to restore the “pure files” from a backup. These files may be usable by the corresponding Microsoft management tools.
To restore Microsoft SQL Server databases in alternate location, you must first restore the database files as pure files using the Restore Wizard:
1. Select the VSS-aware backup set and start the restore wizard. Click File Restore.
The Database Tree changes to display the individual file components (not the database instances).
2. Browse and select the directories and files to restore. You will need the *.ldf and *.mdf files to restore an Microsoft SQL Server from pure files.
3. Click Next.
4. Select the restore location and complete the restore.
After the pure files have been restored, you can use Microsoft SQL Management Studio to attach the databases to the destination Microsoft SQL Server instance. Consult the Microsoft documentation for further instructions.
Pure Files restores of differential backups
If your VSS-aware SQL backup is using the Full+Differential policy, there is an additional differential file (*.mdf.diff) that forms part of the backup. Since this is saved in a format that only DS-Client would normally use, you have an additional, manual step to roll up the changes from the *.mdf.diff into the *.mdf file.
This assumes the "File Restore" method is selected in the first DS-Client restore wizard screen for the selected VSS-aware SQL Server backup set. After restoring the .mdf, .mdf.diff, and .ldf files for the selected database in any chosen folder in an alternate location, a command line tool named "VssDiffApply.exe" (available on the Release DVD in the "Tools" folder) needs to be used to merge the .mdf and .mdf.diff files for that database.
1. Copy the "VssDiffApply.exe" tool to the same location as the restored .mdf, .mdf.diff, and .ldf files.
2. Open the command prompt in Windows as an administrator.
3. Change directory to the location where the files were restored and run the following command:
VssDiffApply.exe [/y] <.mdf file> <.mdf.diff file>
4. After VssDiffApply.exe is run successfully, attach the .mdf database file to the desired SQL Server instance using SQL Management Studio.