Posted by Note: I’m new to both MS SQL Server and to RDS databases. My base expertise is as a Db2 (LUW) DBA.
There is a really complete document on this available from Amazon.
Scenario
In this scenario, I have just spun up a new RDS instance for a project using a CloudFormation template. I’ll blog about that some day, but for now, I’m too new to really do that topic justice. All I did was take a yaml file that a colleague made and change the values for a new environment. The part that is covered here is taking a backup of the existing environment and restoring it into the new one.
Tools
While I’m a command line fan in the Db2 world, I’m using SSMS here, which painfully only runs on Windows, so I’m running it from a Windows VM on my Mac. That said I’m mostly only using the “Run Query” functionality, so I’m taking a fancy gui and using it like a command line.
Procedure
Back up Source Database
This was the easy part. After connecting via SSMS to the existing environment, I used an RDS stored procedure to take a database backup to an S3 bucket in the same region. There are just 4 parameters that I specified as input:
- source_db_name – the name of the database that I’m backing up
- s3_arn_to_backup_to – the S3 bucket to use for the backup, and the file name for the backup
- overwrite_S3_backup_file – I specified 1. This means that if I need to run the same command twice, it’ll replace the backup from the previous command.
- type – I specified `’FULL’` for a full backup.
Note that there are encryption options during backup as well, see the link at the top of this article for more details there.
The command I used in a “New Query” window was this (names changed to protect the innocent):
exec msdb.dbo.rds_backup_database
@source_db_name='projprod',
@s3_arn_to_backup_to='arn:aws:s3:::mssql-backups/proj/proj_prod_20191119.bak',
@overwrite_S3_backup_file=1,
@type='FULL';
The output from that looked like this:
To verify that the backup was successful, I used this command:
exec msdb.dbo.rds_task_status @task_id=5
And the output from that looked like this:
When the backup is complete, you should also be able to see it in your AWS console for s3.
Restore into New RDS Instance
Once I had the backup on S3, I connected to it using SSMS, and then could easily do the restore of the database. There are only two parameters I specify here:
- restore_db_name – this is the database name to restore into. For the Db2 DBAs reading, this is what I would specify after the INTO keyword of the RESTORE command if I wanted to change the database name on restore.
- S3_arn_to_restore_from – this is the same S3 bucket and file name I specified on backup.
The command I used in a “New Query” window was this (names changed to protect the innocent):
exec msdb.dbo.rds_restore_database @restore_db_name = 'projbeta', @S3_arn_to_restore_from = 'arn:aws:s3:::mssql-backups/proj/proj_prod_20191119.bak';
Again, I can check status with:
exec msdb.dbo.rds_task_status @task_id=1
The results look like this:
And when complete:
Other Comments
In general, a backup of an MS SQL Server database looks to be as simple as the backup and restore of a Db2 database, at least on RDS, and nowhere near as complicated as setting up RMAN, at least for those unfamiliar with the platform.
I worry that I’m missing some essential steps on the encryption, and that is one of the things on my list to better understand. There’s a lot on that list as I delve RDBMSes other than Db2.
