Automating SQL Server Point-in-Time Recovery Using EBS Snapshots

This blog post introduces a new AWS feature that enables point-in-time recovery for SQL Server using EBS snapshots. It offers a detailed walkthrough for creating VSS-integrated snapshots and restoring SQL Server databases using EBS snapshots to simplify database backups and automate point-in-time restore.

Introduction

Amazon Web Services (AWS) recently introduced support for restoring Microsoft SQL Server databases running on Windows EC2 using Amazon EBS snapshots in “No Recovery” mode, enabling point-in-time recovery (PITR). This is made possible by integrating Microsoft Volume Shadow Copy Service (VSS) with EBS snapshots, allowing application-consistent component-based snapshots to capture metadata required for recovery.

AWS EBS snapshots deliver application-consistent backups, combining speed, cost-effectiveness, and simplified backup management. Newly introduced automation orchestrates the steps required to restore databases from snapshots to specific points in time.

Benefits of VSS-Integrated EBS Snapshots

Enhanced VSS-based EBS snapshot capabilities offer several key benefits for SQL Server backups:

• Cost Efficiency: Using EBS snapshots is one of the most cost-effective methods to back up SQL Server databases. For example, backing up a 1TB database costs approximately $51 per month.
• Point-in-Time Recovery (PITR): VSS-based EBS snapshots taken using the updated Run Command now support including metadata required to restore SQL Server databases with NORECOVERY, allowing transaction logs to be applied for PITR.
• Simultaneous Database Backups: All databases on an SQL Server instance are backed up simultaneously through coordinated snapshotting of the underlying EBS Volumes.
• Reduced Backup Windows: SQL Server flushes the buffer cache and freezes I/O briefly during snapshot initiation. Afterward, volumes remain fully operational as the snapshot completes in the background. From the OS perspective, creation appears near-instantaneous.
• Efficient Snapshot Storage: EBS Snapshots is incremental, which lowers costs when retaining multiple backups over time.
• Simplified Backup Management: Traditional SQL Server backups often involve saving to a local file system and then uploading to Amazon S3—or backing up directly to S3 with SQL Server 2022 and later, which has some limitations. EBS snapshots simplify and accelerate this process by removing intermediate steps and avoiding the limitations of S3-based backups.

Understanding Restore Behavior from VSS-Enabled EBS Snapshots

Restoring from EBS snapshots takes longer than creating them, as data must be initialized. Using the EBS Provisioned Rate for Volume Initialization, you can create fully performant EBS volumes within a predictable amount of time.

Manual restore involves creating EBS volumes from snapshots, attaching them to an EC2 instance, and using the VSS agent to restore from captured metadata. This can be complex, especially for databases spanning multiple volumes, so we developed an automation workflow to streamline it end-to-end.

To enable this automation, the required IAM Permissions must be granted for volume creation and attachment. These can be granted to the user or assumed by a role at runtime.

Walkthrough

In this walkthrough, you’ll go through the end-to-end process of backing up and restoring SQL Server databases on EC2 using Amazon EBS snapshots integrated with VSS.

First, use AWS Systems Manager to create application-consistent EBS snapshots with the AWSEC2-VssInstallAndSnapshot run command, which captures the necessary metadata for recovery in NORECOVERY mode.

Figure 1: Creating EBS Snapshots with AWS Systems Manager

Next, restore your database using the AWSEC2-RestoreSqlServerDatabaseWithVss automation. This automation identifies the snapshot, creates and attaches volume(s), and restores the database using captured metadata. You can restore the database with a new or existing name. Figure 2 illustrates the restore automation process.

Figure 2: Restore Automation with AWS Systems Manager

Pre-requisites

Ensure your EC2 instance meets these requirements:

• Windows Server 2016+ with the following tools installed:
  • .NET Framework 4.6+
  • Windows PowerShell 3.0+
  • AWS Tools for Windows PowerShell 3.3.48.0+
  • AWS Systems Manager Agent 3.0.502.0+

Creating EBS Snapshots with AWS Systems Manager

1. Open the AWS Console and navigate to Systems Manager
2. In the left-hand navigation pane under Node Tools, select Run Command.
3. In the Run Command Window click on the “Run command”.
4. Search for AWSEC2-VssInstallAndSnapshot and select it using the radio button; this will open the Command parameters dialog.
5. From the list of parameters, set Exclude Boot Volume to True, Set No Writers, Copy Only, and Create Ami to False.
6. Set the SaveVssMetadata parameter to True to captures the VSS Backup Component Document and SQL Server Writer metadata, which are required to restore the database with no recovery.
7. From the Target selection section, choose Instances manually
8. Select an EC2 instance from the list of Instances
9. Review other options, such as saving the Output to S3
10. Click Run to start the snapshot creation process
11. You will be taken to the Command execution screen, where you can monitor its status. Ensure that the command completes successfully.
12. After the command executes, snapshot creation will start and may take some time to complete in the background. You can monitor the progress by navigating to EC2 > Elastic Block Store > Snapshots in the AWS Console. Ensure that the snapshot status shows as “Completed” before proceeding to the restore steps.

See Figure 3 for the key steps and parameters involved in creating EBS snapshots with AWS Systems Manager.

Figure 3: Creating EBS Snapshots with AWS Systems Manager

Restoring Databases Using AWS EBS Snapshots

The newly introduced AWSEC2-RestoreSqlServerDatabaseWithVss automation simplifies the process of restoring SQL Server databases that were backed up using the AwsVssComponents version 2.4.0 or greater, with the Save Vss Metadata parameter set to true. This automation streamlines recovery by handling key tasks such as locating the latest snapshot, creating and attaching volumes to the EC2 instance, and initiating the restore process. You can use this automation to restore a database either with a new name or with the same name but pointing to the newly created volumes from the snapshot. The key steps in this automated restore workflow are illustrated in Figure 4: Restore Automation. See Table 1 bellow for input options.

Figure 4: Restore automation overview

Input Parameters

Table 1 Input parameters

Execute the Restore Automation

1. From the AWS Console, open Systems Manager
2. Under Change Management Tools, select Automation
3. Click Execute runbook button.
4. Search for and select AWSEC2-RestoreSqlServerDatabaseWithVss
5. Review Runbook details and select Next
6. On the Execute automation runbook screen, select the Simple execution option.
7. Specify Input parameters by selecting the EC2 instance that was backed up in the previous step.
8. Specify SourceDatabaseName, TargetDatabaseName
9. Optionally Specify SnapshotSetId or RestorePointOfTime. If neither was provided the latest snapshot will be used
10. Set RestoreWithNorecovery = True
11. Click Execute automation

See Figure 5 for the key steps and parameters involved in executing the restore automation of SQL Server databases backed up with EBS snapshots using AWS Systems Manager.

Figure 5: Restoring SQL server database from EBS Snapshots

To initiate the restore automation, click Execute to start the workflow. Once completed, log onto the target EC2 instance to validate the restore. You should see the newly attached EBS volumes restored from the snapshot, and the restored SQL Server database should now appear in the SQL Server Management Studio (SSMS).

Applying Transactional Log Backups

After the restore automation completes, the specified SQL Server database is restored, pointing to EBS volumes created from the snapshot. If the RestoreWithNoRecovery option is enabled in restore automation, the database will be created in a restoring state, allowing you to apply transaction log backups to achieve point-in-time recovery (PITR).

To apply transaction log backups and recover the database, follow these steps:

1. Restore Transaction Log Backups.

Ensure you have all transaction log backup files (.trn or .log) created after the snapshot. Restore each backup sequentially, in the order they were taken, to maintain the log chain’s integrity by executing the following sample command:

RESTORE LOG [YourDatabaseName]
FROM DISK = 'PathToYourTransactionLogBackup'
WITH NORECOVERY;

Replace [YourDatabaseName] with the name of your database and ‘PathToYourTransactionLogBackup’ with the full path to each transaction log backup file. The NORECOVERY option keeps the database in the restoring state, allowing subsequent log backups to be applied.

To restore the database to a specific point in time, use the STOPAT option with the RESTORE LOG command

RESTORE LOG [YourDatabaseName]
FROM DISK = 'PathToYourTransactionLogBackup'
WITH STOPAT = 'yyyy-mm-dd hh:mm:ss',
NORECOVERY;

Replace ‘yyyy-mm-dd hh:mm:ss’ with the desired recovery point timestamp.

2. Recover the Database

After all transaction log backups have been applied, bring the database online by executing:

RESTORE DATABASE [YourDatabaseName]
WITH RECOVERY;

Clean Up

After restoring your database, the automation will create new Amazon EBS volumes from snapshots. To optimize costs, you should clean up any unused volumes, as charges apply to both existing and newly created EBS volumes. You have several options, including retaining the new volumes by changing their drive letters and deleting the old volumes, or copying the database from the new volumes back to the existing volumes and then deleting the new ones. In this example, we’ve chosen the simpler approach of changing the drive letters of the newly created volumes and removing the old volumes. You can adopt this method or select another based on your specific availability requirements.

Step 1: Detach the Restored Database

1. Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
2. In Object Explorer, right-click the restored database, select Tasks > Detach…, check Drop Connections, and click OK to detach the database.

Step 2: Update Drive Letters

1. Stop the SQL Server service on the EC2 instance to prevent access to the database files during the drive letter changes.
2. Open Disk Management on Windows.
3. Remove drive letters from old volumes: Right-click each old volume, select Change Drive Letter and Paths, click Remove, and confirm.
4. Assign original drive letters to new volumes: Right-click each new volume, select Change Drive Letter and Paths, click Add, assign the original drive letter, and confirm.

Step 3: Reattach the Database

1. Start the SQL Server service.
2. In SSMS, right-click on Databases, select Attach…, click Add, locate the .mdf file on the new volume, and click OK to reattach the database.

Step 4: Detach and Delete Old Volumes

1. Detach volumes from EC2: In the EC2 console, navigate to Volumes, select the old volumes, click Actions, and choose Detach Volume.
2. Delete detached volumes: After detachment, select the volumes again, click Actions, choose Delete Volume, and confirm the deletion.

Limitations to Keep in Mind

This solution was designed to work on the original instance where the EBS snapshot was taken and metadata captured. Restoring databases to a PITR requires access to VSS and SQL writer metadata files captured during snapshot creation.

• Restores to New Disks: EBS snapshots can only be restored to newly created disks. You cannot overwrite or restore a database directly to existing volumes as you would with native SQL Server backups. When snapshots are taken, you can choose to exclude the root volume (C Drive), but all other drives are snapshotted. During restore automation, all volumes from the snapshot are restored and reattached. Since Windows has a limit of 26 drive letters that can be mounted simultaneously, you may encounter drive letter constraints. Also, please note that each newly created volume will generate additional EBS charges as long as it exists. It’s important to monitor and remove any unused volumes after restoration to optimize costs.
• The restore automation is designed for standalone instances. When restoring databases in High Availability (HA) configurations like Always On Availability Groups (AGs) or Failover Cluster Instances (FCIs), additional steps are necessary to integrate the restored databases into the HA environment. For AGs, after restoring the database on the primary replica, you need to initiate synchronization to restore it on the secondary replica(s). For FCIs, the new disks must be enabled for Multi-Attach, added to the cluster’s storage resources, and assigned to the SQL Server role to maintain high availability. Alternatively, you can copy the restored database files onto existing disks that are already part of the HA configuration.
• Brief I/O Suspension: During snapshot creation, I/O operations are briefly suspended (typically under 10 seconds) to ensure consistency. This short freeze may impact databases with highly sensitive latency requirements and should be performed during a backup window.

Conclusion

In this blog post, I outlined the prerequisites and provided a detailed walkthrough for creating EBS snapshots and restoring SQL Server databases using those snapshots. I also discussed the cost-effectiveness of this approach and addressed key limitations of AWS EBS snapshots integrated with VSS. Together, these techniques help improve backup efficiency, reduce recovery time, and offer a cost-effective solution for SQL Server backup and restore. By considering the limitations and using restore automation, you can streamline database management and enhance your database backup and recovery strategy.

AWS has significantly more services, and more features within those services, than any other cloud provider, making it faster, easier, and more cost effective to move your existing applications to the cloud and build nearly anything you can imagine. Give your Microsoft applications the infrastructure they need to drive the business outcomes you want. Visit our .NET on AWS and AWS Database blogs for additional guidance and options for your Microsoft workloads. Contact us to start your migration and modernization journey today.