RDS/SQL Server Backup and Restore to/from S3

 

RDS/SQL Server Backup and Restore to/from S3

Backup database to S3

  1. Under RDS Dashboard create a new option group with "SQLSERVER_BACKUP_RESTORE" option.
  2. Update your RDS instance to use the newly created option.
  3. Open SQL Management Studio, connect to RDS database and execute the following to kick off the 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

To check the progress of the backup, run the following:

USE [msdb] GO

DECLARE   @return_value int

EXEC  @return_value = [dbo].[rds_task_status]         @db_name =
'your_database_name',         @task_id = <<<found in result of previous query>>>

SELECT    'Return Value' = @return_value

GO

Restore database from S3 .bak file

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

More information, including how to export an encrypted backup and restore a database from an encrypted S3 backup file, visit:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html