SQL script for RDS backup

RDS is a managed database service. Backup and restore via S3 bucket and SQL mgt studio.

SQLSERVER_BACKUP_RESTORE option group is set

Backup to S3 bucket

/* ONLY runs with Admin user (Master user of this DB instance) */

exec msdb.dbo.rds_backup_database 
@source_db_name = 'mydatabasename',
@s3_arn_to_backup_to = 'arn:aws:s3:::mybucketname/mydatabasename.bak',
@overwrite_S3_backup_file = 1;

Restore from S3

/* ONLY runs with Admin user (Master user of this DB instance) */

exec msdb.dbo.rds_restore_database 
@restore_db_name = 'mydatabasename',
@s3_arn_to_restore_from = 'arn:aws:S3:::mybucketname/mydatabasename.bak';

Check Status

exec msdb.dbo.rds_task_status;

Create Login SQL

CREATE LOGIN [myDOMAIN\myUSER] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english];
GO
EXEC sp_addsrvrolemember 'myDOMAIN\myUSER', 'processadmin';
EXEC sp_addsrvrolemember 'myDOMAIN\myUSER', 'setupadmin';   
GO
GRANT ALTER ANY CONNECTION, ALTER ANY LINKED SERVER, ALTER ANY LOGIN, ALTER ANY SERVER ROLE, ALTER SERVER STATE, ALTER TRACE, CONNECT SQL, CREATE ANY DATABASE, VIEW ANY DATABASE, VIEW ANY DEFINITION, VIEW SERVER STATE TO [myDOMAIN\myUSER] WITH GRANT OPTION;
GO

Set S3 Bucket Policy

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "PublicRead",
            "Effect": "Allow",
            "Principal": "*",
            "Action": [
                "s3:GetObject",
                "s3:GetObjectVersion"
            ],
            "Resource": "arn:aws:s3:::mybucketname/*"
        }
    ]
}