Limitations of SQL Server Native Backup and Restore in Amazon RDS

By: Sadequl Hussain   |   Updated: 2017-08-23   |   Comments (11)   |   Related: > Amazon AWS

Problem

In a previous post of this blog series we talked about SQL Server database native backup and restore in Amazon RDS. Are there any limitations we need to be aware of?

Solution

The ability to backup individual databases from a SQL Server RDS instance has been a long-awaited feature for database administrators. As we saw in the first part of this article series, SQL Server databases in Amazon RDS can be both backed up to and restored from Amazon S3 buckets with relative ease.

However, this capability comes with some limitations. In this post, we will talk about some of the things you need to be aware of.

No differential, transaction log or filegroup backup or restore

The rds_backup_database stored procedure allows you to create a full backup of a database. Likewise, the rds_restore_database lets you restore a database from its full backup file.

What these commands don’t let you do is backup or restore transaction logs. In other words, there is no point-in-time-recovery (PITR) with SQL Server native backups in RDS.

The same type of restriction applies to differential or filegroup backups–SQL Server in RDS does not support differential or filegroup backups and restores.

We don’t consider this a huge hindrance to data recovery. Amazon RDS allows you to create scheduled instance snapshots and those snapshots are kept accessible for a rolling 35-day period. You can restore the instance within five minutes of a specified time in the last thirty-five days.

The RDS instance backup with SQL Server native backup can provide a nice disaster-recovery (DR) solution: you can use both to have a copy of the database available. If you need up-to-the-minute recovery within last thirty-five days, you can create a new instance from the RDS snapshot. Anything beyond that can be recovered from native backup files in S3.

Cannot restore KMS-encrypted backups from S3 to on-premises or EC2 SQL Servers

Suppose you want to take one of the encrypted backups from S3 and restore it on-premises or an EC2 instance or another cloud platform. Can you do that?

The answer is no. That’s because the encryption is done by a server-side KMS key when RDS backs up the database and that key is not available to your target SQL instance. In fact this is the error message you will get when you try to restore the encrypted back to an EC2 instance:

The solution is simple: create a manual backup without encryption and import it in the target server. If the encrypted backup has the data you are looking for which is no longer available in the database’s current state, you can follow these steps:

• Create an RDS instance in the same region which has the same version as the source system and has access to the S3 bucket and the KMS key.
• Restore the encrypted backup in that instance with the KMS  key.
• Backup the database without encryption to S3.
• Copy the backup file to EC2 or on-premises server and restore from there.

You may be wondering why you need a separate RDS instance to restore the encrypted backup. We will explain shortly.

Cannot restore backups of TDE-enabled databases

Conversely, you cannot migrate an on-premises database with Transparent Data Encryption (TDE) to RDS. TDE is available in Enterprise Editions of SQL Server and requires a number of steps to take effect. First, a master key is created in the master database of the instance which is then used to protect a certificate. The certificate is then used to protect a database level encryption key. The database is then enabled for encryption with that key. A database backup therefore does not include the certificate or the master key. Trying to restore the backup in the RDS instance will fail with an error message like this:

To migrate these databases you can use, manual processes like scripting out database structure and program code, exporting and importing data etc.

Cannot restore database in the same RDS instance

How many times have you been asked to restore a copy of a database in the same instance with a different name? Typically, if you have a database called “abc” in your production environment and want to restore a previous version of it, you wouldn’t overwrite the existing database. You would rename the database with something like “abc_old” and restore the backup as “abc”. Or, you would perhaps restore the backup as “abc_restored”.

None of these work with SQL Server native restore in Amazon RDS. If you have the original database present in the instance, you can’t restore its backup as a new database, nor will it allow you to overwrite the existing database.  It does not help even if you rename the original database.  In the following code snippet, we are trying to restore the AdventureWorks database in the same instance under a different name:

EXEC msdb.dbo.rds_restore_database 
     @restore_db_name = 'AdventureWorks_Restore', 
     @S3_arn_to_restore_from = 'arn:aws:s3:::rds-sqlserver-backup-bucket/Backup/AdventureWorks2014.bak'

If you run the rds_task_status stored procedure after some time, the error message in the “task_info” column will be like this:

However, this works if you delete the original database, which is probably something DBAs wouldn’t want. Also, even if you delete the original database and restore its backup, you cannot restore a second copy of the backup in the instance.

This is definitely annoying and understandably frustrating. The workaround is to:

• Restore the backup in a separate, new RDS instance
• Create an empty database in a SQL Server instance running in EC2 which has access the new RDS instance (same VPC, same Security Group etc.)
• Using SQL Server Import Export and Wizard, copy the data and schema to the empty database in EC2. Bear in mind, you still need to create all the views, stored procedures, UDFs, triggers and user accounts manually.
• Backup the database in EC2 and save the backup in S3 where the original RDS instance can pick it up from
• Run the restore command from the original RDS instance to restore the newly-created backup file

Obviously this is a long-winded process. After all, if you backup a database from one instance, you would expect it to be restorable in the same instance with a different database name. By the looks of it, Amazon still needs to work on this use case.

Target RDS instance needs to have access to the S3 bucket and KMS key

To restore the backup file to a separate RDS instance, the target RDS instance needs to have its option group enabled for SQLSERVER_BACKUP_RESTORE. Without this, RDS simply won’t know what to do and you will get an error message like this:

When you assign the SQLSERVER_BACKUP_RESTORE option to the target RDS instance’s option group, remember the option should use an IAM role that has access to the backup S3 bucket.

What happens when the IAM role has access to the S3 bucket but does not have access to the KMS key used for encryption? When you try to restore the encrypted database with the key specified, you will get an error message and this is an example of it:

The IAM role therefore needs to have access to the KMS key as well.

Grant database backup and restore permissions to users

The user account running RDS SQL Server native backup and restore commands needs to have appropriate permissions:

• The user needs to be a member of the target database’s db_backupoperator role.
• The user needs to be present in the msdb database.
• The user needs to have execute permission on the rds_backup_database, rds_restore_database, rds_cancel_task and rds_task_status stored procedures in the msdb database.

Here is a script to create such a user:

USE master 
GO 
CREATE LOGIN rds_backup_operator WITH Password = 'somecomplexpassword', DEFAULT_DATABASE=master, 
   CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 
GO 

USE msdb 
GO 
CREATE USER rds_backup_operator FROM LOGIN rds_backup_operator 
GO 
GRANT EXECUTE ON msdb.dbo.rds_backup_database TO rds_backup_operator 
GO 
GRANT EXECUTE ON msdb.dbo.rds_restore_database TO rds_backup_operator 
GO 
GRANT EXECUTE ON msdb.dbo.rds_task_status TO rds_backup_operator 
GO 
GRANT EXECUTE ON msdb.dbo.rds_cancel_task TO rds_backup_operator 
GO 

USE whateverdb 
GO 
CREATE USER rds_backup_operator FROM LOGIN rds_backup_operator 
GO 
ALTER ROLE db_backupoperator ADD MEMBER rds_backup_operator 
GO 

Maximum Native Backup File Size is 1 TB

At the time of this writing, the maximum backup size supported by RDS SQL Server native backup is 1 TB.

Everything needs to be in the same AWS region

SQL Server native backup and restore works fine when the RDS instance, S3 bucket and the KMS key (when using encryption) are all in the same AWS region.

For example, if you try to backup a database from RDS into a bucket in different region, the process fails with an “Access Denied” error message. In our tests, our RDS instance was running in the us-east-1 (North Virginia) region and its option group was enabled for SQL Server native backup and restore. The IAM role for the SQLSERVER_BACKUP_RESTORE option had full access to S3 buckets in both us-east-1 (North Virginia) and us-east-2 (Ohio) regions. When we ran the “rds_backup_database” command to backup a database to the S3 bucket in Ohio region, it failed with an error message like this:

You may also see an error message like this:

This may be a problem when backups need to be stored in a separate region for regulatory compliance. In such cases, we recommend creating the backup in an S3 bucket in the same region and copying/moving it to the final location with S3 cross-region replication.

Conclusion

Although SQL Server native backup and restore is a great addition to RDS’ growing features, there are few things to be mindful of when using it for production purposes. We would recommend testing for different scenario-based use cases, particularly when encrypted backups need to be restored.

Next Steps

• Learn more about SQL Server native backup and restore error messages from this AWS blog post.
• Test database backup and restore under various encryption strategy (TDE, client side and server side encryption, etc.).
• Automate the restore process with scripts.
• Learn more about S3 cross region replication.

About the author

Sadequl Hussain has been working with SQL Server since version 6.5 and his life as a DBA has seen him managing mission critical systems.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips