2

I want to backup/restore SQL Server RDS to S3 bucket. Both RDS and S3 are in different account. How to implement it. Query I am using to backup/restore is:

Backup:

USE [msdb]
GO

DECLARE   @return_value int

EXEC  @return_value = [dbo].[rds_backup_database]
      @source_db_name = 'your_database_name',
      @S3_arn_to_backup_to = 'arn:aws:s3:::your-bucket-name/folder/db.bak',
      @KMS_master_key_arn = NULL,
      @overwrite_S3_backup_file = NULL

SELECT    'Return Value' = @return_value

GO

Restore

exec msdb.dbo.rds_restore_database 
    @restore_db_name='database_name', 
    @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/folder/file_name_and_extension';
ashvanee
  • 43
  • 1
  • 4

2 Answers2

1

Basically you add SQL_SERVER_BACKUP_RESTORE to an option group. Associate this group with the RDS SQL Server instance. Make sure you configure this option with the S3 bucket information and include a KMS key for backup encryption.

So, first find the desired option group from the management console.

  1. Add the SQL_SERVER_BACKUP_RESTORE option.

  2. Create an IAM role to enable RDS to access S3 and point to a bucket. You can also specify and configure encryption at this time.

  3. Use the SQL Server Management Studio to connect to the DB instance and call up the following stored procedures as required:

rds_backup_database – Back up a single database to an S3 bucket.

rds_cancel_task – Cancel a running backup or restore task.

rds_restore_database – Restore a single database from S3.

rds_task_status – Track running backup and restore tasks.

For example: to backup: rds_backup_database and rds_task_status, and for restoring a single database use rds_restore_database.

These are the basics for backup and recovery. Additional tasks may be available via CLI or the console. Detailed customization likely requires scripting.

Oron Zimmer
  • 336
  • 1
  • 3
0

enter image description here

Both options required on your option group!!!

Olusegun Orija
  • 21
  • 3