AWS Cloud Operations Blog

Introducing the AWSSQLServer-Restore Automation document for SQL Server maintenance

In the first post in this five-part series, I introduced you to AWS Launch Wizard Systems Manager Automation documents for SQL Server and showed you how to run the AWSSQLServer-Backup document.

In this second post, I’ll show you how to restore a SQL Server backup from Amazon S3 using the AWSSQLServer-Restore document.

The SQL Server maintenance initiative is a joint venture between Launch Wizard and AWS Systems Manager to help ease the burden of maintenance operations on deployed SQL Server instances. These runbooks are implemented using a combination of SSM Automation documents and scripts that execute on your SQL Server instance. You can use the Systems Manager console or the AWS CLI to enter parameters and then execute the scripts on your SQL Server instance.

AWSSQLServer-Restore

The AWSSQLServer-Restore Automation document downloads SQL Server backups from Amazon S3 to local storage. This document can optionally restore a database from a backup using the latest FULL backup for the specified time range. It then applies the latest DIFF and LOG backups performed after the latest complete backup.

The document finds:

  • The latest FULL backup in the specified time range. (If no time range is specified, the document selects the latest.)
  • The latest DIFF that occurs after that FULL backup in the time range (if any).
  • The latest log files in the time range that occur after the FULL or DIFF backups.

Prerequisites for using this document

  • The instance must be running SQL Server.
  • An AWS Secrets Manager database credential must be configured.
  • The backup to use must have been performed by the AWSSQLServer-Backup document.
  • The Amazon S3 database backups must include at least one FULL database backup.
  • The username must be assigned, at minimum, the following database role: db_owner.
  • You might need to grant additional permissions or assign roles to the instances that allow access to AWS Secrets Manager credentials and Amazon S3.

Run AWSSQLServer-restore from the Systems Manager console

On the Documents page, choose the AWSSQLServer-Restore document.

The details page for the AWSSQLServer-Restore Automation document displays fields for Platform (Windows, Linux, macOS), Owner (Amazon), Status (Active), Created, and Target type.

Figure 1: AWSSQLServer-Restore

On Execute automation document, choose Simple execution.

Under Execute automation document, there are options for Simple execution, Rate control, Multi-account and Region, and Manual execution.

Figure 2: Execute automation document

Under Input parameters, choose the instance on which to perform the restore:

Under Input parameters, the Show interactive instance picker option is selected. In the list of managed instances, ec2-us-sql-prod01 is selected.

Figure 3: Managed EC2 instances running SQL Server

Enter parameters for the database, download directory, S3 bucket, data files directory, and more, and then choose Execute.

The Database, DownloadDirectory, S3Bucket, and DataFilesDirectory fields are highlighted. For Database, tpcc is entered. For S3Bucket, sql-backups-ssm is entered. For DownloadDirectory, g:\Restore is entered. For DataFilesDirectory, d:\MSSQL is entered.

Figure 4: Parameters

The console displays the execution status:

Under Execution status, the overall status is Success. All three executed steps succeeded. There are 0 failed, cancelled, or timed out steps.

Figure 5: Execution status

The document locates the backup in S3 and downloads it to the local path specified in the DownloadDirectory parameter (Figure 4):

In Windows Explorer, the backup file appears in the location specified on the instance.

Figure 6: Backup file

Next, because I included restore in the RestoreAction parameter (Figure 4), it also performs a directed restore to the location I specified. The document doesn’t overwrite the current database. It restores to a new database with a temporary GUID attached:

 In Object Explorer, a temporary GUID is appended to the restored database.

Figure 7: Object Explorer

The document placed the data and log files in the location I specified in the DataFilesDirectory parameter (Figure 4).

In Windows Explorer, the data and log files appear in d:\MSSQL\LOG.

Figure 8: Data and log files

After you validate the restored database, you can rename the original to _old and rename the restored database back to the original name.

To see the Automation step that performs the restore and its output, choose the execution ID link:

The ExecuteRestore step has a status of Success. Other details include the step execution ID, action (aws:runCommand), start time, end time, timeout, and more.

Figure 9: ExecuteRestore

To view the output of each step, choose the execution ID link:

Under OutputPayload, the download from S3 took 7 minutes. The database restore took 13 minutes.

Figure 10: Outputs

The output from the ExecuteRestore step shows that the download from S3 took 7 minutes. The SQL Server restore took 13 minutes.

Summary

In this post, I showed how you can run the AWSSQLServer-Restore document to restore a database backup stored in S3.

In part 3, I’ll show you how to run the AWSSQLServer-Index document to identify and reindex fragmented indexes.

In part 4, I’ll show you how to run the AWSSQLServer-DBCC document to execute database consistency checks (DBCC) against your database.

In part 5, I’ll introduce you to Maintenance Windows, a powerful tool included with AWS Systems Manager for scheduling these actions against your SQL Server instances.

For more information about running SQL Server workloads on AWS, see Best practices for deploying Microsoft SQL Server on Amazon EC2. For more information about SSM Automation documents, see AWS Systems Manager Automation in the AWS Systems Manager User Guide.

To read the other posts in this series, see:

Available in These AWS Regions

The AWS Launch Wizard Systems Manager Automation documents for SQL Server are generally available. You can use them in the following AWS Regions:

US East (N. Virginia) US East (Ohio) US West (N. California)
US West (Oregon) Canada (Central) South America (Sao Paulo)
Asia Pacific (Mumbai) Asia Pacific (Singapore) Asia Pacific (Sydney)
Asia Pacific (Seoul) Asia Pacific (Tokyo) EU (Frankfurt)
EU (Ireland) EU (London) EU (Stockholm)

Support for the AWS Regions in China and for the GovCloud Region is in the works. There is no additional charge for using these Automation documents, only for the resources they create.

About the author

Alan Cranfield

Alan Cranfield

Alan Cranfield is a Senior Systems Engineer in the EC2 Windows team where he focuses on enabling and optimizing SQL Server workloads for AWS.