
Seamless SQL Server Recovery on EC2 with AWS Systems Manager
Learn how to restore SQL Server databases on Amazon EC2 using AWS VSS technology and automation runbooks. This guide shows how to achieve rapid database recoveries without downtime while reducing operational complexity.
- Fast database restoration times to meet recovery objectives.
- Minimal disruption by restoring without shutting down or pausing the SQL Server application.
- Flexible recovery options, including the ability to restore the database in recovery or restoring mode.
- Amazon EC2 instances have SSM agent installed
- Your EC2 database instance must be configured with basics disks (this solution won't work if you use dynamic disks)
- You can use this automation runbook if you have a standalone Microsoft SQL server or for the Primary database in an Microsoft SQL Always On availability group
- Log in to Microsoft SQL Server Management Studio
- The image below illustrates that there is multiple databases on the EC2 instance. The CustomerFeedbackDB is the database that will be restored using VSS snapshots..

- Navigate to the SSM Console Page .
- Select Node Tools > Run Command from the left-hand menu



- Exclude Boot Volume "True"
- Save Vss Metadata "True" <- This is important, as without this, it will not save our Vss meta data files.
- Select the targets for the Run document, This can be doing with instance tags, resources groups or selecting the instances manually. For this post I have manually selected the instances.

- Under input options, I have un-checked the Enable an S3 bucket option. You can enable this if you want or need to write all command output to an Amazon S3 buckets
- Select Run, this should take about 1 - 2 minutes to run


- To confirm that the VSS snapshots have been taken, login to your EC2 instance and browse to the default location "C:\ProgramData\Amazon\AwsVss\VssMetadata" Note: ProgramData is a hidden folder.
- You should see at least two XML files with the naming convention ending in BCD and SqlServerWriter, as shown below.

- Navigate to the SSM Console Page .
- Select Change Management Tools > Automation from the left-hand menu

- Select Execute Automation

- Search & select the Automation runbook "AWSEC2-RestoreSqlServerDatabaseWithVss"

- Scroll to the bottom of the runbook page and select Next
- Select Simple execution
- Review the Runbook details, below is what the Runbook will do:
- Update or install the latest version of AWS VSS Components on the target instance.
- Obtain the VSS Snapshot Set ID corresponding to the snapshot set that you will use to restore the database with the PrepareVssRestore-pitr-test run command document.
- Obtain the EC2 snapshots that belong to the snapshot set and create new EBS volumes from them.
- Attach the newly created EBS volumes to the instance.
- Perform the database restore operation through VSS with the RunVssRestoreForSqlDatabase-pitr-test run command document.
- Under Input parameters, select the EC2 instance where the restore will be performed. I will be restoring to the original SQL database EC2 instance (this runbook will create a new EBS volume and attach it as a new drive to the exisiting EC2 instance)

- Input the SourceDatabaseName (I am restoring the database "CustomerFeedbackDB")
- Input the TargetDatabaseName.
- If there is a specific SnapshotSetId or RestorePointofTime you want to restore to, input the SnapshotSetId or the point of time "MM-dd-yyyy:hh-mm" I have left these both blank as it will default to the most recent snapshot.
- If you plan to leave the database in a restoring state select True for RestoreWithNorecovery, I have selected false.
- Confirm that the default MetadataPath is correct (%PROGRAMDATA%\Amazon\AwsVss\VssMetadata)

- Select Execute
- You can track the progress in the Execution Details, this should take between 3-5 minutes to complete


Any opinions in this post are those of the individual author and may not reflect the opinions of AWS.