RDS Support for Database Backup

[sysadmind]

Currently, only the Integrity Check and the Index and Statistics Maintenance are supported for Amazon RDS for SQL Server as noted in the FAQ. The FAQ notes that automated backups are handled by RDS itself. While this is true, it glosses over the fact that backups are only handled at the instance level. This means that each backup contains the entire SQL instance including every database on that instance. In order to restore from these backups, the requirement is to restore the snapshot to an entirely new instance including all databases. I believe that there are scenarios where an instance has many databases, potentially very large, and restoring that instance may take a long time. This could be less than ideal if the intent is to restore some portion of data from an individual database on the instance.

I have been attempting to make the DatabaseBackup.sql script compatible with RDS and have had some success. Would this project be open to including RDS support? I have some working changes, however my work so far would not be considered production ready.

Here are some notes on my work so far for consideration. I would be happy to open a PR with the changes that I have for review so that any work required to make this change production ready could be pointed out.

• Native Backup and restore is supported on RDS if the option is enabled
• Database Backups in RDS use the msdb.dbo.rds_backup_database stored procedure
• Backups use S3 as a storage location. S3 is an object store and does not have "directories" in the traditional sense. There is no need to create a "directory" before saving a file. This means that most of the directory logic in the script is unnecessary.
• There is no direct S3 access from TSQL in RDS, so file cleanup does not seem possible from the script. S3 does support lifetime policies though, so the S3 bucket itself could be configured to remove backup files independent of the script.
• RDS does support database mirroring and availability groups so that support may need to be considered. This is handled by RDS though. I know the logic for files considers mirrors and availability groups
• The backup stored procedure does support splitting the backup into multiple files but there doesn't seem to be control over the files themselves, just the number of files.
• Full and Differential backups are supported
• Server and availability group names seem to be auto generated. This may make the default backup paths hard to navigate. RDS instances can have names, but I have found no way to access these names from TSQL. Users could work around this by overriding the directory structure parameters.
• RDS supports transaction log access, but I don't think it could be worked into the DatabaseBackup.sql script. The support is only for copying transaction logs to S3.
• RDS does not support accessing the msdb.dbo.log_shipping_* tables so the logic to look up this information would need to be skipped in the RDS case.

I'm happy to help in any way that I can. If there's a better place to discuss this, please let me know.