Backup for SQL Server RDS

Michael, 15 December 2018

Earlier I described how I performed a restore of a database into Amazon RDS for SQL Server. While I don’t play in SQL Server a lot I had a different requirement from another client a few weeks later that required me to perform a backup of an existing RDS instance.

Now, for those that have never done it, there is effectively two steps in perform the backup:

  1. Starting the backup process by calling the stored procedure msdb.dbo.rds_backup_database.
  2. Monitoring the progress of the backup to ensure that it completes successfully by calling the stored procedure msdb.dbo.rds_task_status.

There are a few error conditions that can occur and most of them relate to insufficient permissions on the S3 bucket.

The following script will perform a synchronous backup of RDS on SQL Server and will wait for the backup to complete before it returns a success. We ran this code from a GitLab runner.

Some points to note:

  • Line 3 needs to be updated for your hostname.
  • Line 4 needs to be updated to a user with access to perform a backup of the database.
  • Line 5 needs to be updated to be the password for the user defined in Line 4.
  • Line 6 needs to be updated for the ARN of the bucket and key to backup to.
  • Line 7 needs to be updated to the database that needs backing up.
  • Line 10 actually performs the backup.

The rest of the script after line 10 will check the backup to see if it is successful. If the backup fails (generally related to S3 permissions) then the exit code should be 1, otherwise the exit code will be 2. The script expects the backup to complete in 60 minutes.