Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Limitations of SQL Server Native Backup and Restore in Amazon RDS


By:   |   Last Updated: 2017-08-23   |   Comments (8)   |   Related Tips: More > 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:

Msg 3241, Level 16, State 0, Line 1
The media family on device 'C:\AdventureWorks_Encrypted.bak' is incorrectly formed. SQL Server cannot process this media family.

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:

Cannot find server certificate with thumbprint '<large hexadecimal string>'. RESTORE FILELIST is terminating abnormally.

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:

Aborted the task because of a task failure or a concurrent RESTORE_DB request.

Task ID x (RESTORE_DB) exception: Database AdventureWorks_Restore cannot be restored because there is already an existing database with the same file_guids on the instance.

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:

Database backup/restore option is not enabled yet or is in the process of being enabled. Please try again later.

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:

User: arn:aws:sts::xxxxxxxxxx:assumed-role/RDS-SQL-BACKUP-S3-UE/RDS-SqlServerBackupRestore is not authorized to perform: kms:DescribeKey on resource: arn:aws:kms:us-east-1:xxxxxxxxxx

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:

Aborted the task because of a task failure or an overlap with your preferred backup window for RDS automated backup.
Access Denied

You may also see an error message like this:

Please specify a bucket that is in the same region as RDS instance

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.


Last Updated: 2017-08-23


next webcast button


next tip button



About the author
MSSQLTips author Sadequl Hussain 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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, January 04, 2019 - 6:31:35 PM - Madhavan Back To Top

Did any one found the appropriate role for restore option for users (other than root) logins in AWS RDS ? 

I try to restore dventureworks2016.bxk from s3 bucket, i got the below exception, 

Database restores can only be performed by database logins with [CREATE ANY DATABASE] permissions.

I have provided all the permission for the user in msdb as said here in "

Grant database backup and restore permissions to users" section. 

Let me know if anyone got succeed on restoring the adventureworks backup ?

 

Regards

Madhavan


Wednesday, June 27, 2018 - 4:58:53 PM - SADEQUL HUSSAIN Back To Top

You can try making trhe AD group member of the db_owner or db_backupoperator group of the sourcxe database and see if that helps. If not, I am afraid SQL logins / and roles will be the option.


Monday, June 25, 2018 - 11:13:57 AM - Russ Back To Top

Sadequl,

Should this be true even if our RDS SQL domain is in a 2 way trust with our primary domain that it has no way of knowing?

I should have mentioned that in my original post.


Friday, June 22, 2018 - 10:46:43 AM - SADEQUL HUSSAIN Back To Top

Hi Russ,

Amazon RDS SQL has now way of knowing your users are coming from an AD group. It has to be SQL logins.


Thursday, June 21, 2018 - 12:20:44 PM - Russ Back To Top

Thank you for this awesome post.  We are trying to give access rights to an Active Directory group and users so that they can restore db's from our S3 bucket using Windows Authentication to log in to our RDS instance from SSMS.   We have the group created as a user in msdb, but when attempting to restore we get a "The EXECUTE permission was denied on the object 'rds_restore_database', database 'msdb', schema 'dbo'." error.  So we need to give Execute permissions to the AWS stored procedures.

However even as the master account, it still gives access denied messages any time trying to modify perms of msdb (or any of the built in system databases for that matter) : GRANT EXECUTE ON dbo.rds_restore_database to "OurDomain\SQL-Admins" results in "Cannot find the object 'rds_restore_database', because it does not exist or you do not have permission."

Is there something glaring that we are missing, or could you modify your user creation script to use an AD group or AD user?

Thanks in advance!


Thursday, February 15, 2018 - 11:25:03 AM - larry marx Back To Top

 in case it helps someone in the future...

i found setting the bucket permissions to public write helped for the access denied error. tha IAM role that wa automatically created when creating 

new rds options wasn't allowing write to the bucket. granted, this may have been an oversight/error on my part, but the easy workaround is

set the bucket to public write

 

btw, of course the post author gets tons of credit. my fix may not be 'best practice' but wouldnt have tried it if not for this post

 

 


Sunday, November 19, 2017 - 2:12:35 AM - Sadequl Hussain Back To Top

Hi Paul,

Please check the section "Grant database backup and restore permission to users" section in the tip. You can perhaps create a new account from master user and grant required permissions to that account. Master user should be able to do that. You can then login as that user and try to run t5he backup.


Friday, November 17, 2017 - 11:40:28 PM - Paul Back To Top

Great article. Just what I was looking for. I am having an issue though that my master user does not have permission for the ‘rds_backup_database’ object.

The error I am receiving is, “The EXECUTE permission was denied on the object ‘rds_backup_database’, database ‘msdb’, schema ‘dbo’.”

Do you have any idea how I can get around this? My master user also cannot create new users etc on the msdb database either.

Thanks for your time.


Learn more about SQL Server tools