By default, a Cloud Server Snapshot may not properly recover a Microsoft SQL Server database as there is no guarantee as to the database state at the time of snapshot. This article provides a suggested approach to address this limitation by using the "pre and post script" feature to use Microsoft's Volume Shadow Copy Service (VSS) feature to create a VSS backup that will allow the server to be properly restored.
This article provides an OVERVIEW of Approach
This article provides an overview of the suggested approach and is designed to be adapted by Windows engineers familiar with VSS and the issues of SQL Server backups as well as their specific application. Given the variety of potential configurations, some changes may be required for a user's specific scenario. We strongly recommend implementing and then testing any approach prior to relying on it for production purposes.
- Server must be Microsoft SQL Server versions 2012 onwards
- Clustering is NOT supported with this methodology
Enable VSS on a SQL Server
To enable VSS option on Volumes that has to be backed up, right-click on the desired volume:
Navigate to Properties. On the New window click on the "Shadow Copies" tab. On the "Shadow Copies" pane, select the desired Drive. Click on "Enable":
On the "Enable Shadow Copies Window" Select "Yes":
Create Powershell on Server to Activate New Shadow Copy and Delete Old Copies
Create a Powershell Script to Create a Volume Shadow Copy. This will be the pre-snapshot script.
Note: this is only an example. Please use your own partition and filename.
Ensure You Reference All Drives!
If you have multiple drives, all of them have to be part of the PowerShell code above. Duplicate the line above and include the respective drive letters.
Create a Powershell Script to Delete Older Shadow Copies. This will be the post-snapshot script.
Only an Example!
This is only an example. Please use your own partition and filename.
The below example deletes files older than 31 days. Your situation may require a different amount of retention. Please modify the script as needed
Configure Pre and Post-Script Feature to Use Powershell
Follow the instructions in How to Set Pre and Post Snapshot Scripts on a Cloud Server, referencing the script you set above
- Pre-Snapshot Script:
File Path on the Server - The absolute path to the first Powershell script created above that will create the Shadow Copy
Failure Handling - We recommend the CONTINUE option, in which case the system will take the snapshot even if powershell to copy the database isn't successful - the theory being that some snapshot is better than none. However, note that the failure is not logged by the Snapshot service so you should employ local alerting if possible. Alternately, setting it to the ABORT option will create a "failed" snapshot record (though no actual snapshot) which may make it clearer you're having problems so long as you are reviewing snapshot success periodically.
Script Timeout - The system will wait this long for the lock script to finish. We'd recommend setting this to 30 minutes or less but this may need to be modified depending on the number of drives and their size.
- Post-Snapshot Script:
- File Path on the Server - The absolute path to the second Powershell created above that will unlock the server
- Guest OS Username - Username with permission to run both scripts
Password - Password for the Username
Restoring SQL Server Using Cloud Server Snapshot and VSS
Create a Snapshot Preview Server as described in:
Confirm the snapshot represents what you want to restore and then migrate the server to production storage infrastructure as described in:
Check the GUI to identify available VSS Snapshots.
Choose the VSS snapshot you wish to restore and select Revert, following Microsoft best practice