AWS Database Blog

Backup and Restore Strategies for Amazon RDS for SQL Server

Customers have asked for the right solution to safeguard their data on Amazon Relational Database Service (Amazon RDS) for SQL Server and meet their current Recovery Point Objective (RPO), the maximum acceptable amount of time since the last backup and Recovery Time Objective (RTO), the maximum acceptable delay between the interruption of service and restoration of service, requirements. One of the responsibilities of a database administrator is to protect the data of the company, and Amazon RDS for SQL Server has multiple options for creating backups of the data and also restoring it.

When putting together a backup strategy for Amazon RDS for SQL Server, you must take into account the following:

You can choose from multiple strategies for safeguarding data on Amazon RDS for SQL Server; backup strategies can include using automated snapshots, manual snapshots, AWS Backup, native backup and restore, or a combination. This post compares these strategies and also looks at the options and limitations of each. Finally, we demonstrate how to use AWS Backup to centrally manage automated and manual snapshots for Amazon RDS across your organization.

Amazon RDS for SQL Server Automated Snapshots

Amazon RDS creates and saves automated snapshots of your DB instance during the backup window of your DB instance. This storage volume snapshot of your DB instance backs up the entire DB instance, not just individual databases. Amazon RDS saves the automated backups of your DB instance according to the backup retention period that you specify. If necessary, you can recover your database to any point in time during the backup retention period.

The following diagram illustrates the architecture of a backup and restore solution using automated snapshots.

This method has the following benefits:

  • You can store the snapshot for 0–35 days, with 7 days being the default, when you create the RDS for SQL Server instance via the Amazon RDS console.
  • Log backups run as frequently as every 5 minutes depending on your workload. When restoring your instance, RPO can be as little as 5 minutes.
  • You can restore to a new RDS for SQL Server instance from a snapshot.
  • If an instance is encrypted using Amazon EBS or a database is encrypted in the instance using TDE, that instance or database is automatically restored with the same encryption.
  • You can copy your automated cross-Region backups.
  • The first snapshot of a DB instance contains the data for the full DB instance. Subsequent snapshots of the same DB instance are incremental, which means that only the data that has changed after your most recent snapshot is saved.

However, using automated snapshots has certain limitations:

  • You can’t create an encrypted snapshot of an unencrypted instance
  • You can’t restore an individual database
  • You can’t restore the instance to itself, so in the case of a Multi-AZ restore, the endpoints change
  • A snapshot of the DB instance must be encrypted using the same CMK as the DB instance
  • Storage I/O is suspended for a fraction of a second (approximately 10 milliseconds) during the snapshot backup process

Amazon RDS for SQL Server Manual Snapshots

Manual snapshots are one-time snapshots taken manually based on your needs. Amazon RDS creates a storage volume snapshot of your DB instance, which backs up the entire DB instance and not just individual databases. Creating this DB snapshot on a Single-AZ DB instance results in a brief I/O suspension that can last for fraction of a second depending on the size and class of your DB instance. For SQL Server, I/O activity is suspended briefly during backup for Multi-AZ deployments.

The following diagram illustrates the architecture of a backup and restore solution using manual snapshots.

This method has the following benefits:

  • You can take manual snapshots of an RDS for SQL Server instance any time and it’s not subject to the 35-day retention period.
  • Snapshots don’t expire; you can store them for as long as you need and can restore a snapshot at any time even after the instance has been stopped.
  • You can use a manual snapshot to create a new instance and change SQL Server Editions in some cases.
  • You can take a manual snapshot prior to a release in case you require a rollback. All snapshots created after the instance is launched are incremental.

However, manual snapshots have the same limitations as mentioned for automated snapshots, and the following additional drawbacks:

  • You can’t take manual backups when an automated snapshot is running

AWS Backup

AWS Backup enables you to centralize and automate data protection across AWS services. AWS Backup offers a cost-effective, fully managed, policy-based service that further simplifies data protection at scale. AWS Backup also helps you support your regulatory compliance obligations and meet your business continuity goals. Together with AWS Organizations, AWS Backup enables you to centrally deploy data protection (backup) policies to configure, manage, and govern your backup activity across your organization’s AWS accounts and resources, including Amazon Elastic Compute Cloud (Amazon EC2) instances, EBS volumes, Amazon RDS databases (including Amazon Aurora clusters), Amazon DynamoDB tables, Amazon Elastic File System (Amazon EFS), Amazon FSx for Lustre, Amazon FSx for Windows File Server, and AWS Storage Gateway volumes.

The following diagram illustrates the architecture of a backup and restore solution using AWS Backup.

This solution has the following benefits:

  • You can automate backup scheduling, retention management, and lifecycle management
  • You can centralize your backup strategy across your organization, spanning multiple accounts and Regions
  • You can centralize monitoring your backup activity and alerting across AWS services
  • You can implement cross-Region backups for disaster recovery planning
  • The solution supports cross-account backups
  • You can perform secure backups with secondary backup encryption
  • All backups support encryption using KMS encryption keys
  • The solution works with TDE
  • You can restore to a specific recovery point from the AWS Backup console

Using AWS Backup has certain limitations:

  • You can’t perform native SQL backup and restore
  • You can’t do cross-Region PITR

For more information about using AWS Backup for Amazon RDS for SQL Server, see the tutorial Amazon RDS Backup & Restore using AWS Backup.

Native SQL backup and restore

Amazon RDS supports native backup and restore for SQL Server databases. You can create a full backup of your RDS for SQL Server database and store the file in Amazon S3. You can then restore the backup file to an existing RDS DB instance that’s running SQL Server. You can also restore this backup file to an on-premises server or a different RDS DB instance that’s running SQL Server. You can enable native SQL backup and restore for Amazon RDS for SQL Server by adding the SQL_Server_Backup_Restore option group.

The following diagram illustrates the architecture of a native backup and restore solution.

This method has the following benefits:

  • You can take backups of individual databases on an RDS for SQL Server instance and restore an individual database instead of having to restore the complete instance
  • As of this writing, full and differentials are supported for native backup and restore
  • You can back up and restore TDE-encrypted databases to the same instance only (as shown in the following section)
  • The backup occurs directly from the S3 bucket
  • It uses native SQL Server backup functionality and support compression
  • Permissions are configured through AWS Identity and Access Management (IAM) roles
  • Multi-file backups are also supported

Native backup and restore has the following limitations:

  • You can’t restore TDE-encrypted database backups to or from another server.
  • As of this writing, log backups aren’t available.
  • A differential backup is based on the last full backup. For differential backups to work, you can’t take a snapshot between the last full backup and the differential backup. If you want a differential backup, but a manual or automated snapshot exists, you must do another full backup before proceeding with the differential backup.

For more information about native backup and restore for an RDS for SQL Server DB instance, see How do I perform native backups of an Amazon RDS DB instance that’s running SQL Server.

Back up and restore TDE-enabled database backups on the same instance

In this section, we demonstrate how to how to back up and restore TDE-enabled database backups on the same instance.

  1. Validate that the database is encrypted using TDE. 
    USE [master]
GO
SELECT [name] FROM sys.databases WHERE is_encrypted = 1
GO
SELECT db_name(database_id) as DatabaseName, * FROM sys.dm_database_encryption_keys
GO

  2. Run the native SQL backup for the TDE-enabled database using msdb.dbo.rds_backup_database (make sure to not include the KMS key parameter, which we discuss later in this section). 
    EXEC [msdb].[dbo].[rds_backup_database]
@source_db_name = 'AdventureWorks2019'
, @s3_arn_to_backup_to = 'arn:aws:s3:::<instance>/AdventureWorks2019_08162021.bak'
--, @kms_master_key_arn = 'arn:aws:kms:<kmw key>'
, @overwrite_s3_backup_file = 1
, @type = 'Full'
, @number_of_files = 1
GO

  3. Check the status of the native backup. 
    EXEC [msdb].[dbo].[rds_task_status] @task_id = 22

  4. Restore the database from the TDE-encrypted backup to the same instance using msdb.dbo.rds_restore_database. 
    EXEC [msdb].[dbo].[rds_restore_database]
@restore_db_name = 'AdventureWorks2019_TDE'
, @s3_arn_to_backup_to = 'arn:aws:s3:::<instance>/AdventureWorks2019_08162021.bak'
--, @kms_master_key_arn
--, @type
--, @with_norecovery
GO

  5. Validate the restore status. 
    EXEC [msdb].[dbo].[rds_task_status] @task_id = 24

  6. Confirm your new database is encrypted with TDE. 
    USE [master]
GO
SELECT [name] FROM sys.databases WHERE is_encrypted = 1
GO
SELECT db_name(database_id) as DatabaseName, * FROM sys.dm_database_encryption_keys
GO

When running a backup for a TDE-enabled database, you must comment out the parameter for the KMS key or you get an error like the following. This is not a permissions error, but an error because it doesn’t use the AWS managed key in your account.

[2021-08-16 13:49:22.070] Task execution has started.
[2021-08-16 13:49:22.083] Aborted the task because of a task failure or an overlap with your preferred backup window for RDS automated backup. 
[2021-08-16 13:49:22.087] Task has been aborted 
[2021-08-16 13:49:22.090] User: arn:aws:sts:: :assumed-role/ /RDS-SqlServerBackupRestore is not authorized to perform: kms:GenerateDataKey on resource: arn:aws:kms:us-east-1: :key/

  1. Query task status to validate that TDE database restore failure has occurred. 
    EXEC [msdb].[dbo].[rds_task_status] @task_id = 22

Conclusion

Understanding the possible backup solutions in Amazon RDS for SQL Server is key to safeguarding your data and insuring you meet your RTO and RPO needs, as well as insuring you can recover from critical events. In this post, we discussed different ways to backup and restore your RDS for SQL Server instances and database. This can help you devise a backup strategy that protects your data and meets your company’s requirements.

For more details on migrating SQL Server workloads to Amazon RDS for SQL Server, see Migrating Microsoft SQL Server Enterprise Workloads to Amazon RDS: Part 1. You can also review the best practices for working with Amazon RDS for SQL Server. For more details about disaster recovery options for RDS SQL Server, visit Managed Disaster Recovery on Amazon RDS for SQL Server.

About the Authors

Gene Mays is a Database Specialist Solutions Architect with expertise in SQL Server. He has worked with SQL Server for over 15 years and has extensive experience in various industries. He has helped many customers architect high availability and disaster recovery solutions for SQL Server as well as orchestrate large-scale migrations in AWS. Prior to working with AWS, Gene has experience supporting enterprise customers in the financial and health industries.

Yogi Barot is Microsoft Specialist Senior Solution Architect at AWS, she has 22 years of experience working with different Microsoft technologies, her specialty is in SQL Server and different database technologies. Yogi has in depth AWS knowledge and expertise in running Microsoft workload on AWS.