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.
Database Backups in RDS use the msdb.dbo.rds_backup_databasestored 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
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.
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.sqlscript 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.
msdb.dbo.rds_backup_databasestored procedureDatabaseBackup.sqlscript. The support is only for copying transaction logs to S3.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.