Amazon recently announced the availability of native backups to and from RDS for MS SQL. This is fantastic news for those of us used to working in the SQL world. More importantly, it provides a MUCH easier way to move your SQL data into and out of your RDS instance(s).
While getting things set up to do this may take a bit of time, once you do, I think you’ll find you’ll be much happier. You only need the following things:
- An RDS instance of MS Sql server to backup (skip if you are just importing a .bak file into an empty instance)
- An RDS instance of MS Sql server to restore to (skip if you are just taking a .bak you want to store off of AWS)
- An S3 storage bucket to contain your .bak files
- An option group defined (and applied to your RDS instances) that includes the SQLSERVER_BACKUP_RESTORE option added to it (which includes configuring it for a IAM role and the S3 storage bucket)
Please note, these instructions are for accomplishing these steps using the AWS Console web site rather than the CLI or APIs. Perhaps we’ll modify this for these in the future. We also won’t cover things like creating RDS instances as we assume that if you are interested in this, you’ll already know how to do it.
First thing we want to do is to create our Options group and apply it to the db(s) in question. The easiest way is to go into the RDS Dashboard and in the left pane, click on Option Groups. You will most likely have one (or more) called default:sqlserver-xxxxx-xx-xx and it will be described as “Default option group for sqlserver-type version“. Take note that the license type (like web in my case) and the version 12.00 match the Engine listed on your RDS instance.
Click on Create button, give it a Name, Description, and select the appropriate Engine and Major Engine Version. Once created, select your group and click Add Option. The only Option available to you should be SQLSERVER_BACKUP_RESTORE and it will ask you to select an IAM role (or create a new one). Select one you have or create a new one. Mark Apply Immediately and then hit Add Option button to add it.
Now go to your RDS instances, select the instance you want and under Instance Actions, select Modify. Note that many of the operations we are going to do cannot be accomplished while a db is being backed up just so you know.
After you’ve got everything in place, you can now jump into MS SQL Server Management Studio (or other Query tool) and do the following:
Backup Jobs:
Note that you must be logged into the db you wish to backup:
USE dbName;
exec msdb.dbo.rds_backup_database
@source_db_name='dbName',
@s3_arn_to_backup_to='arn:aws:s3:::bucketname/backupname.bak',
@overwrite_S3_backup_file=1
Restore Jobs:
EXEC msdb.dbo.rds_restore_database
@restore_db_name='dbName',
@s3_arn_to_restore_from='arn:aws:s3:::bucketname/backupname.bak';
View Your Jobs:
The following will show all the tasks run on the named db, most recent first.
exec msdb.dbo.rds_task_status
@db_name = 'dbname'
exec msdb.dbo.rds_task_status
@task_id = X
where X is the task ID given by SQL when you started the job
Cancel a Job:
exec msdb.dbo.rds_cancel_task @task_id=X;
where X is the task ID you want to cancel. Note that this can take upwards of 5 minutes or more to complete so use wisely.
General Notes:
- You cannot restore a backup to an RDS instance that contains the db to be restored.
- You can’t rename the db in the target that you want to restore to either.
- You CAN, however, delete the db and then you can restore to the same instance.
- These aren’t super fast (backup of a 100gb db took about an hour and 15 minutes and the restore took about 40 minutes. However in most instances, I’ve found it quicker than doing a snapshot and restoring the snapshot to a new instance.
Thoughts
This particular technique saved my bacon when an RDS instance was running out of space and I couldn’t shrink it enough to make a difference. Since you can’t change the storage allocation on a MS SQL instance in RDS, you need to create a whole new instance. I used this technique to copy the db over to the new instance with the larger storage allocation much quicker than I could have otherwise.
