Amazon RDS for SQL Server – Support for Native Backup/Restore to Amazon S3

Regular readers of this blog will know that I am a big fan of Amazon Relational Database Service (Amazon RDS). As a managed database service, it takes care of the more routine aspects of setting up, running, and scaling a relational database.

We first launched support for SQL Server in 2012. Continuing our effort to add features that have included SSL support, major version upgrades, transparent data encryption, enhanced monitoring and Multi-AZ, we have now added support for SQL Server native backup/restore.

SQL Server native backups include all database objects: tables, indexes, stored procedures and triggers. These backups are commonly used to migrate databases between different SQL Server instances running on-premises or in the cloud. They can be used for data ingestion, disaster recovery, and so forth. The native backups also simplify the process of importing data and schemas from on-premises SQL Server instances, and will be easy for SQL Server DBAs to understand and use.

Support for Native Backup/Restore
You can now take native SQL Server database backups from your RDS instances and store them in an Amazon S3 bucket. Those backups can be restored to an on-premises copy of SQL Server or to another RDS-powered SQL Server instance.  You can also copy backups of your on-premises databases to S3 and then restore them to an RDS SQL Server instance. SQL Server Native Backup/Restore with Amazon S3 also supports backup encryption using AWS Key Management Service (AWS KMS) across all SQL Server editions. Storing and transferring backups in and out of AWS through S3 provides you with another option for disaster recovery.

You can enable this feature by adding the SQL_SERVER_BACKUP_RESTORE option to an option group and associating the option group with your RDS SQL Server instance. This option must also be configured with your S3 bucket information and can include a KMS key to encrypt the backups.

Start by finding the desired option group:

Then add the SQL_SERVER_BACKUP_RESTORE option, specify (or create) an IAM role to allow RDS to access S3, point to a bucket, and (if you want) specify and configure encryption:

After you have set this up,  you can use SQL Server Management Studio to connect to the database instance and invoke the following stored procedures (available within the msdb database) as needed:

• rds_backup_database – Back up a single database to an S3 bucket.
• rds_restore_database – Restore a single database from S3.
• rds_task_status – Track running backup and restore tasks.
• rds_cancel_task – Cancel a running backup or restore task.

To learn more, take a look at Importing and Exporting SQL Server Data.

Now Available
SQL Server Native Backup/Restore is now available in the US East (N. Virginia), US West (Oregon), Europe (Ireland), Europe (Frankfurt), Asia Pacific (Sydney), Asia Pacific (Tokyo), Asia Pacific (Singapore), Asia Pacific (Mumbai), and South America (São Paulo) Regions. There are no additional charges for using this feature with Amazon RDS for SQL Server, however, the usage of the Amazon S3 storage will be billed at regular rates.

— Jeff;