Going to be away at Orlando’s SQL Saturday this weekend and one of my company’s dev teams needs to make some modifications to the production database. We have a very experienced team and I have complete confidence everything will go well. However, as a best practice, we need to SNAPSHOT the instance before they begin. So, today’s post is a quick review on how to make a snapshot using the AWS Management Console.
Occasionally, I am asked what the difference is between a SNAPSHOT and a BACKUP. With AWS RDS, a SNAPSHOT is a backup of the entire instance and ALL databases. A single database backup is typically referred to as a FULL BACKUP. This can get confusing as many times people use the terms interchangeably.
FULL BACKUP
For this weekend I am also going to make a FULL BACK UP of the database to be changed. Many times it is easier to restore a single database to the already existing RDS server, than having to restore a snapshot of everything.
I have already configured my OPTION GROUP that is associated with my RDS instance to allow SQLSERVER_BACKUP_RESTORE and I have S3_INTEGRATION enabled.
Next, I will set up a SQL Agent job to launch the database backup. Here is my code snippet the Agent Job will run.
DECLARE @DbName nvarchar(100) = 'TestDb';
DECLARE @ServerName nvarchar(100) = 'MyServerName';
DECLARE @tSql nvarchar(2500);
SELECT @tSql = CONCAT(
'exec msdb.dbo.rds_backup_database
@source_db_name=''',@DbName,''',
@s3_arn_to_backup_to=''arn:aws:s3:::MyBucketName/backup/',@ServerName,'/install/',CONVERT(VARCHAR(10), GETDATE(),112),'/',@DbName,'.bak'',
@overwrite_s3_backup_file=1,
@type=''FULL'';')
EXEC(@tSql);I can check the status of the backup job by running this command.
EXEC msdb..rds_task_status;
SNAPSHOT
To make a snapshot, I navigate to the RDS Dashboard and open the instance.
On the MAINTENANCE & BACKUP tab, I want to check and make sure that I am not going to run into the same time that our regular maintenance snapshot will be made. From the screenshot below, I see that we’re good. Then right below that, I click the TAKE SNAPSHOT button.
On the popup window I will select the RDS instance I want and then give my snapshot a name. Since this is prior to a software install, I’ll add the date and word “preinstall” to my database name. Then click the TAKE SNAPSHOT button.
I am then taken to the SNAPSHOTS page where the status of my snapshot is shown.
I can also verify the status and completion on my RDS Dashboard.
Once the SNAPSHOT completes the status changes from BACKING-UP to AVAILABLE and I’m good to go.
RESTORES
In keeping this short, I’ll not be covering the RESTORE process today. Keep in mind that the FULL BACK UP of a single database to your existing RDS server might be a better option than a SNAPSHOT RESTORE. Some of my RDS servers have dozens of databases that serve a host of applications. The SNAPSHOT RESTORE creates a new instance of RDS and that could mean involving more people, time and resources in configuring those applications to work with it.