DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • How to Restore a Transaction Log Backup in SQL Server
  • High Availability and Disaster Recovery (HADR) in SQL Server on AWS
  • Using AWS Data Lake and S3 With SQL Server: A Detailed Guide With Research Paper Dataset Example
  • Securing AWS RDS SQL Server for Retail: Comprehensive Strategies and Implementation Guide

Trending

  • Revolutionizing Financial Monitoring: Building a Team Dashboard With OpenObserve
  • The Role of Functional Programming in Modern Software Development
  • How Clojure Shapes Teams and Products
  • SQL Server Index Optimization Strategies: Best Practices with Ola Hallengren’s Scripts
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Backup SQL Server RDS to an S3 Bucket

How to Backup SQL Server RDS to an S3 Bucket

Learn how to back up your SQL Server RDS database directly to an Amazon S3 bucket using msdb.dbo.rds_backup_database, with detailed examples.

By 
arvind toorpu user avatar
arvind toorpu
DZone Core CORE ·
Feb. 13, 25 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
3.6K Views

Join the DZone community and get the full member experience.

Join For Free

Managing backups for SQL Server RDS instances is crucial to 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.

Package Parameters

The stored procedure supports several parameters, which are categorized as required and optional.

Required Parameters

  • @source_db_name: The name of the database to back up.
  • @s3_arn_to_backup_to: The ARN indicating the Amazon S3 bucket to use for the backup, including the backup file name.
    • The file can have any extension but .bak is commonly used.

Optional Parameters

  • @kms_master_key_arn

    • The ARN for the symmetric encryption KMS key to use for encrypting the backup.
    • You can't use the default encryption key. Using the default key will result in backup failure.
    • If you don't specify a KMS key, the backup file won't be encrypted.
    • Only symmetric KMS keys are supported.
  • @overwrite_s3_backup_file

    • Determines whether to overwrite an existing file.
    • 0 (default): Doesn't overwrite an existing file. Returns an error if the file already exists.
    • 1: Overwrites the existing file, even if it isn't a backup file.
  • @type

    • Specifies the type of backup.
    • FULL (default): Performs a full backup.
    • DIFFERENTIAL: Creates a differential backup based on the last full backup.
    • Important: For differential backups to work, no snapshot should exist between the last full backup and the differential backup. If a snapshot exists, perform another full backup before the differential backup. Below is an example query to check the last full backup:
      MS SQL
       
      select top 1
          database_name,
          backup_start_date,
          backup_finish_date
      from msdb.dbo.backupset
      where database_name = 'mydatabase'
        and type = 'D'
      order by backup_start_date desc;


  • @number_of_files

    • Specifies the number of files (chunks) into which the backup will be divided. Maximum: 10.
    • If set to 1 or omitted, a single backup file is created.
    • Multifile backups must use a single *in the file name, which will be replaced with alphanumeric strings during generation. For example:
      • Input: backup*.bak, @number_of_files = 4
      • Output: backup1-of-4.bak, backup2-of-4.bak, backup3-of-4.bak, backup4-of-4.bak
  • @block_size

    • Specifies the physical block size (in bytes) for backup operations.
    • Valid values: 512, 1024, 2048, 4096, 8192, 16384, 32768, 65536.
  • @max_transfer_size

    • Denotes the maximum amount of data (in bytes) transferred per I/O operation.
    • Valid values: Multiples of 65536 bytes up to 4194304 bytes (4 MB).
  • @buffer_count

    • Total number of I/O buffers used during the backup process.

Command Syntax

MS SQL
exec msdb.dbo.rds_backup_database
    @source_db_name = 'database_name',
    @s3_arn_to_backup_to = 'arn:aws:s3:::bucket_name/file_name.extension',
    [@kms_master_key_arn = 'arn:aws:kms:region:account-id:key/key-id'], 
    [@overwrite_s3_backup_file = 0|1],
    [@block_size = 512|1024|2048|4096|8192|16384|32768|65536],
    [@max_transfer_size = n],
    [@buffer_count = n],
    [@type = 'DIFFERENTIAL|FULL'],
    [@number_of_files = n];


Example: Full Backup to S3

MS SQL
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

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


Disable Compression

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


Note: SQL Express does not support backup compression; enabling it in 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 Commands

Full Backup Command

MS SQL
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 = 'FULL';

Differential Backup Command

MS SQL
 
exec msdb.dbo.rds_backup_database
@source_db_name='mydatabase',
@s3_arn_to_backup_to='arn:aws:s3:::mybucket/differential_backup*.bak',
@type='DIFFERENTIAL',
@number_of_files=4;


Restore Command

Differential Restore Command

MS SQL
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';

Differential Restore Command

MS SQL
 
exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_restore_from='arn:aws:s3:::mybucket/backup1.bak',
@type='DIFFERENTIAL',
@with_norecovery=1;


Monitoring and Managing Backup Tasks

Check the task status:

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


Cancel a backup task:

MS SQL
exec msdb.dbo.rds_cancel_task @task_id = 1234;


Considerations

  • Ensure that the S3 bucket used in the commands has the necessary permissions to allow access from the RDS instance.
  • For sensitive data, you can use an AWS KMS master key to encrypt backups before storing them in S3 by specifying @kms_master_key_arn.

Additional Resources

For more detailed examples and information, refer to the official AWS documentation on SQL Server backups.

This guide offers a solid foundation for managing SQL Server RDS backups with Amazon S3. For more advanced configurations, please consult the AWS documentation or experiment with the provided parameters to fine-tune the backup process according to your environment.

AWS Backup sql

Opinions expressed by DZone contributors are their own.

Related

  • How to Restore a Transaction Log Backup in SQL Server
  • High Availability and Disaster Recovery (HADR) in SQL Server on AWS
  • Using AWS Data Lake and S3 With SQL Server: A Detailed Guide With Research Paper Dataset Example
  • Securing AWS RDS SQL Server for Retail: Comprehensive Strategies and Implementation Guide

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!