How to Backup SQL Server RDS to an S3 Bucket

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

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

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; 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

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

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

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

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:

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

Cancel a backup task:

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.