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.
Join the DZone community and get the full member experience.
Join For FreeManaging 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.
- The file can have any extension but
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
- Input:
-
@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 to4194304
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.
Opinions expressed by DZone contributors are their own.
Comments