AWS: RDS - 1


Amazon Relational Database Service (Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups.

Backup & Restore SQL Server

Backup database to S3

  • Assumption

    • DB name: sample_db
    • S3 bucket name: sql-server-backup
  • Backup with built-in stored proc

    exec msdb.dbo.rds_backup_database 
  • Track status

    exec msdb.dbo.rds_task_status @db_name='sample_db'

Restore DB into EC2

  • Restore with powershell

    #Copy-S3Object -BucketName 'nsw-prod-s3-sql-backups' \ 
    # -Key sample_db-20190531011003.bak \
    # -LocalFile L:\Backups\Automated\sample_db-20190531011003.bak
    Restore-SqlDatabase -ServerInstance 'localhost' -Database "sample_db" \
    -BackupFile "L:\Backups\Automated\sample_db-20190531011003.bak" \
    -ReplaceDatabase -KeepReplication -Verbose

Restore DB into RDS

  • Create IAM role which can access the backup file in the S3 bucket. e.g. RDS_RESTORE_S3_READONLY

    "Version": "2012-10-17",
    "Statement": [
            "Effect": "Allow",
            "Action": [
            "Resource": [
            "Effect": "Allow",
            "Action": [
            "Resource": [
  • Create a new option group, or copy or modify an existing option group.

  • Add the SQLSERVER_BACKUP_RESTORE option to the option group.

  • Associate the IAM role with the option. The IAM role must have access to an S3 bucket to store the database backups. e.g. RDS_RESTORE_S3_READONLY

  • Associate the option group with the DB instance.

  • Make sure the RDS has enough space to restore the db backup.

  • Restore the database

    EXEC msdb.dbo.rds_restore_database 
  • Check the progress

    EXEC msdb.dbo.rds_task_status 


  • After you modify the storage size for a DB instance, the status of the DB instance is storage-optimization. The DB instance is fully operational after a storage modification.

  • You can’t make further storage modifications until six (6) hours after storage optimization has completed on the instance.

  • You can’t reduce the amount of storage for a DB instance after storage has been allocated.

Rename database

  • Rename db with built-in stored proc

    EXEC rdsadmin.dbo.rds_modify_db_name N'<OldName>', N'<NewName>'
  • Troubleshoot -The database could not be exclusively locked to perform the operation.

    # Kill connection
    EXEC sp_who 
    KILL <spid>
    EXEC sp_who