AWS Cloud Operations & Migrations 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-Backupdocument. - The Amazon S3 database backups must include at least one FULL database backup.
- The
usernamemust 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.
Figure 1: AWSSQLServer-Restore
On Execute automation document, choose Simple execution.
Figure 2: Execute automation document
Under Input parameters, choose the instance on which to perform the restore:
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.
Figure 4: Parameters
The console displays the execution status:
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):
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:
Figure 7: Object Explorer
The document placed the data and log files in the location I specified in the DataFilesDirectory parameter (Figure 4).
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:
Figure 9: ExecuteRestore
To view the output of each step, choose the execution ID link:
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:
- Introducing the AWSSQLServer-Backup Automation document for SQL Server maintenance
- Introducing the AWSSQLServer-Index Automation document for SQL Server maintenance
- Introducing the AWSSQLServer-DBCC Automation document for SQL Server maintenance
- Introducing Maintenance Windows for scheduling Automation documents against your SQL Server instances
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.