We use essential cookies and similar tools that are necessary to provide our site and services. We use performance cookies to collect anonymous statistics, so we can understand how customers use our site and make improvements. Essential cookies cannot be deactivated, but you can choose “Customize” or “Decline” to decline performance cookies.
If you agree, AWS and approved third parties will also use cookies to provide useful site features, remember your preferences, and display relevant content, including relevant advertising. To accept or decline all non-essential cookies, choose “Accept” or “Decline.” To make more detailed choices, choose “Customize.”
Customize cookie preferences
We use cookies and similar tools (collectively, "cookies") for the following purposes.
Essential
Essential cookies are necessary to provide our site and services and cannot be deactivated. They are usually set in response to your actions on the site, such as setting your privacy preferences, signing in, or filling in forms.
Performance
Performance cookies provide anonymous statistics about how customers navigate our site so we can improve site experience and performance. Approved third parties may perform analytics on our behalf, but they cannot use the data for their own purposes.
Allowed
Functional
Functional cookies help us provide useful site features, remember your preferences, and display relevant content. Approved third parties may set these cookies to provide certain site features. If you do not allow these cookies, then some or all of these services may not function properly.
Allowed
Advertising
Advertising cookies may be set through our site by us or our advertising partners and help us deliver relevant marketing content. If you do not allow these cookies, you will experience less relevant advertising.
Allowed
Blocking some types of cookies may impact your experience of our sites. You may review and change your choices at any time by selecting Cookie preferences in the footer of this site. We and selected third-parties use cookies or similar technologies as specified in the AWS Cookie Notice.
Your privacy choices
We display ads relevant to your interests on AWS sites and on other properties, including cross-context behavioral advertising. Cross-context behavioral advertising uses data from one site or app to advertise to you on a different company’s site or app.
To not allow AWS cross-context behavioral advertising based on cookies or similar technologies, select “Don't allow” and “Save privacy choices” below, or visit an AWS site with a legally-recognized decline signal enabled, such as the Global Privacy Control. If you delete your cookies or visit this site from a different browser or device, you will need to make your selection again. For more information about cookies and how we use them, please read our AWS Cookie Notice.
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.
This guide explains how to use the AWSEC2-RestoreSqlServerDatabaseWithVss automation runbook to restore a Microsoft SQL Server database on an Amazon EC2 instance from application-consistent snapshots created by the AWS VSS solution. By leveraging Windows VSS technology, this approach ensures:
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.
By automating the restoration process, AWS Systems Manager eliminates manual steps, reduces operational overhead, and provides a scalable, repeatable solution for database recovery. This guide walks through the prerequisites, the step-by-step recovery process, and best practices for troubleshooting to help you achieve a reliable, efficient SQL Server recovery strategy on AWS.
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
The first step is to review our Microsoft SQL Database that is running on an EC2 instance.
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..
Image not found
Install the VSS components on EC2 Windows Instance & Create your first VSS Snapshot
The first step is to install the VSS components to the EC2 instance. In your own environment if you have installed / enabled VSS components for your EC2 instances, you can skip this step.
There is two ways to install the VSS components, the first is to manually install the components, the other way is to use a Run command in Systems Manager to do the heavy lifting. This post will leverage the Run command to install the VSS components.
The Run command is named AWSEC2-VssInstallAndSnapshop, as this name would suggest, it will install the VSS components and then create a VSS snapshot.
Select Node Tools > Run Command from the left-hand menu
Image not found
3. In the Run Command console, select Run command
Image not found
4. Search and select for the Command document "AWSEC2-VssInstallAndSnapshot"
Image not found
5. To keep this post simple, I have updated the following Command parameters
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.
Image not found
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
Image not found
9. After 1 - 2 minutes you should see the Overall Status as Success
Image not found
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.
Image not found
Create future VSS Snapshots
To create further VSS snapshots using a Run Command, repeat steps 1 - 3 from the previous section. At step 4, search for the Command document name "AWSEC2-CreateVssSnapshot", then follow the remaining steps above from 5 onwards.
My preferred method, is to use the AWS CLI, it makes this a very quick and simple task. The code is below. Here full list of parameters that can be used with the below command.
Select Change Management Tools > Automation from the left-hand menu
Image not found
Select Execute Automation
Image not found
Search & select the Automation runbook "AWSEC2-RestoreSqlServerDatabaseWithVss"
Image not found
Input parameters
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)
Image not found
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)
Image not found
Select Execute
You can track the progress in the Execution Details, this should take between 3-5 minutes to complete
Image not found
Review Restored Database
The database been restored using VSS snapshots. The database has been restored as a new database in my exisiting EC2 instance as you can see below.
Image not found
Conclusion
In this guide, we’ve demonstrated how AWS Systems Manager Automation runbooks can be used to restore a Microsoft SQL Server database on an Amazon EC2 instance from application-consistent VSS snapshots. By leveraging Windows VSS technology, this solution enables fast, consistent, and automated database restoration without requiring downtime.
This approach simplifies database recovery by eliminating manual steps, reducing operational overhead, and ensuring a repeatable, scalable process. With the ability to customize restoration parameters, businesses can maintain flexibility while ensuring database integrity.
Beyond this specific use case, AWS Systems Manager Automation provides a powerful way to streamline administrative tasks, enforce policies, and enhance operational efficiency. Whether managing database restorations, automating system configurations, or optimizing workload management, automation helps reduce complexity and improve consistency.
As you reflect on this guide, consider how automation could simplify other routine tasks in your environment. What other database operations or IT processes could benefit from a structured, automated approach? With AWS Systems Manager, automation can go far beyond SQL Server recovery, this is just the beginning.
Any opinions in this post are those of the individual author and may not reflect the opinions of AWS.
