AWS RDS SQL Server Native Backups

Question

We have some AWS RDS SQL Server instances that we are setting up. Some of these will be Multi-AZ for HADR.

We know that RDS will take system level backups and snapshots. We are wanting to perform individual backups into S3 buckets so we can use the backups for stage and dev restores on central dev server.

We are using SQL Server Agent jobs to perform these backups but the jobs on SQL Server Agent read successful even if the backup to the S3 bucket fails.

What I am curious about is if anyone has figured out a solution to monitor these backups to the S3 buckets since SQL Server Agent jobs are not showing the failure correctly. Only possible solution I can think of is Powershell but would love to hear anyone else's solution if they have one.

Answer 1

As far as I understand, your task is not so much to make a backup - how to automatically restore it to another RDS SQL Server in the development environment.

In general, there are two ways on how to achieve it:

1. Create a native backup and restore it to dev
2. Export \ Import Data tier application

Amazon RDS SQL Server does not support the BACKUP DATABASE command, you need to use a stored procedure instead:

EXEC [msdb]. [Dbo]. [Rds_backup_database]
@source_db_name = '<database name>',
@ S3_arn_to_backup_to = N'arn:aws:s3:::<bucket name>/sample.backup '

To restore a database to a dev server, please use the following stored procedure:

exec msdb.dbo.rds_restore_database
@restore_db_name = '<database name>',
@s3_arn_to_restore_from = 'arn:aws:s3:::<bucket name>/sample.backup';

You will need to do some pre-configuration on your RDS SQL Server - you can find more details here.

Note. These stored procedures do not directly perform the backups, but run it asynchronously. After completing the procedure, you will receive a Task that should be tracked. You can view all the tasks using the

EXEC procedure [msdb].[Dbo].[Rds_task_status]

Unfortunately, this architecture makes it difficult to use Agent Job. And asynchrony prevents us from making a very reliable solution on powershell.

However, you do not have to use backup to transfer a database to a dev server.

You can use the sqlpackage.exe utility which is intended for data export / import. It is not recommended to use it for backups, as it is necessary to set a global read lock to export consistent data. However, for your task, this can be neglected.

SqlPackage can either create a .bacpac file or .dacpac file. If you specify a /p:ExtractAllTableData = True parameter for the /action:Extract command, then you will get actually identical files.

My experiments have shown that it is better to export to .dacpac, since in this case, you can avoid import errors using the /p option: TreatVerificationErrorsAsWarnings = True

Thus, to export the database, use

sqlpackage.exe /action:Extract /SourceServerName:<RDS Server name> /SourceDatabaseName:<db name> /SourceUser:admin /SourcePassword:<password> /TargetFile:<patch to target .dacpac> /p:ExtractAllTableData=True

and for import

sqlpackage.exe /action:Publish /TargetServerName:<RDS Server name>  /TargetDatabaseName:<db name> /TargetUser:admin /TargetPassword:<password> /SourceFile:<patch to source .dacpac> /p:TreatVerificationErrorsAsWarnings=True

Such a script is much easier to automate in powershell, since both commands are synchronous