AWS Logo
Menu
Seamless SQL Server Recovery on EC2 with AWS Systems Manager

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.

systems-managerssmautomationms-sql-server
Greg Vinton
Amazon Employee
Published Mar 12, 2025

Solution Overview

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.

Prerequisites

Walkthrough

The first step is to review our Microsoft SQL Database that is running on an EC2 instance.
  1. Log in to Microsoft SQL Server Management Studio
  2. 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..

MS SQL Management Studio

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.
  1. Navigate to the SSM Console Page .
  2. Select Node Tools > Run Command from the left-hand menu
select run command from SSM
3. In the Run Command console, select Run command
select run command button
4. Search and select for the Command document "AWSEC2-VssInstallAndSnapshot"
search for AWSEC2-VssInstallAndSnapshot
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.
  1. 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.
Select target instances
  1. 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
  2. Select Run, this should take about 1 - 2 minutes to run
9. After 1 - 2 minutes you should see the Overall Status as Success
  1. 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.
  2. You should see at least two XML files with the naming convention ending in BCD and SqlServerWriter, as shown below.
metadata files

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.

Using SSM Automation to restore your SQL Server database from VSS snapshots

In this section we will restore one of our databases using Systems Manager Automation.
  1. Navigate to the SSM Console Page .
  2. Select Change Management Tools > Automation from the left-hand menu
  1. Select Execute Automation
Select Execute Automation
  1. Search & select the Automation runbook "AWSEC2-RestoreSqlServerDatabaseWithVss"
Select AWSEC2-RestoreSqlServerDatabaseWithVss runbook
Input parameters
  1. Scroll to the bottom of the runbook page and select Next
  2. Select Simple execution
  3. 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.
  4. 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)
select instance
  1. Input the SourceDatabaseName (I am restoring the database "CustomerFeedbackDB")
  2. Input the TargetDatabaseName.
  3. 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.
  4. If you plan to leave the database in a restoring state select True for RestoreWithNorecovery, I have selected false.
  5. Confirm that the default MetadataPath is correct (%PROGRAMDATA%\Amazon\AwsVss\VssMetadata)
input parameters
  1. Select Execute
  2. You can track the progress in the Execution Details, this should take between 3-5 minutes to complete

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.

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.

Comments