DEV Community

Cover image for How to Backup SQL Server RDS to an S3 Bucket
Arvind Toorpu
Arvind Toorpu

Posted on • Edited on

How to Backup SQL Server RDS to an S3 Bucket

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';
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

Disable Compression

exec rdsadmin..rds_set_configuration 'S3 backup compression', 'false';
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

Monitoring and Managing Backup Tasks

Check Task Status

exec msdb.dbo.rds_task_status @db_name = 'MyDatabase';
Enter fullscreen mode Exit fullscreen mode

Cancel a Backup Task

exec msdb.dbo.rds_cancel_task @task_id = 1234;
Enter fullscreen mode Exit fullscreen mode

Billboard image

The fastest way to detect downtimes

Join Vercel, CrowdStrike, and thousands of other teams that trust Checkly to streamline monitoring.

Get started now

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay