How to Backup SQL Server RDS to an S3 Bucket

Managing backups for SQL Server RDS instances is a crucial part of ensuring data availability and disaster recovery. AWS provides tools to facilitate this process, including commands to back up SQL Server RDS databases directly to Amazon S3. This article walks you through the commands and configurations needed to perform backups and restores.

Backing Up SQL Server RDS to S3

The primary stored procedure used for creating backups is msdb.dbo.rds_backup_database. This command allows you to specify the database to back up and the S3 location where the backup will be stored.

Example: Full Backup to S3

exec msdb.dbo.rds_backup_database
    @source_db_name = 'MyDatabase',
    @s3_arn_to_backup_to = 'arn:aws:s3:::mybucket/MyDatabase_backup_full.bak',
    @overwrite_s3_backup_file = 1,
    @type = 'FULL';

Configuring Backup Compression

To save space and reduce transfer time, you can enable compression for SQL Server RDS backups using the rdsadmin commands.

Enable Compression

exec rdsadmin..rds_set_configuration 'S3 backup compression', 'true';

Disable Compression

exec rdsadmin..rds_set_configuration 'S3 backup compression', 'false';

Note: SQL Express does not support backup compression, and enabling it on such instances will result in backup failure.

Performing Native SQL Server Backups

Amazon RDS also supports native SQL Server backup functionality. Below are commands for backup and restore operations.

Backup Command

exec msdb.dbo.rds_backup_database 
    @source_db_name = 'MyDatabase', 
    @s3_arn_to_backup_to = 'arn:aws:s3:::mybucket/MyDatabase_backup_diff.bak',
    @overwrite_s3_backup_file = 1,
    @type = 'DIFFERENTIAL';

Restore Command

exec msdb.dbo.rds_restore_database 
    @restore_db_name = 'MyDatabase',
    @s3_arn_to_restore_from = 'arn:aws:s3:::mybucket/MyDatabase_backup_full.bak',
    @type = 'FULL';

Monitoring and Managing Backup Tasks

Check Task Status

exec msdb.dbo.rds_task_status @db_name = 'MyDatabase';

Cancel a Backup Task

exec msdb.dbo.rds_cancel_task @task_id = 1234;