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:
- Create a native backup and restore it to dev
- 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:
@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