Had to learn this yesterday to clone a production environment down to a lower environment. Figured it qualified for a blog post.
exec msdb.dbo.rds_backup_database
@source_db_name='xxxProd',
@s3_arn_to_backup_to='arn:aws:s3:::xxx-sql-native-backup/xxxProd.bak',
@overwrite_S3_backup_file=1,
@type='full';
exec msdb.dbo.rds_task_status; -- till lifecycle=SUCCESS
ALTER DATABASE xxxUAT SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
drop database xxxUAT;
exec msdb.dbo.rds_restore_database
@restore_db_name='xxxUAT',
@s3_arn_to_restore_from='arn:aws:s3:::xxx-sql-native-backup/xxxProd.bak';
exec msdb.dbo.rds_task_status; -- till lifecycle=SUCCESS
delete from xxxUAT.dbo.SensitiveTableStuff;
The Gotcha’s were:
- Had to set up an option group that added SqlServer Native Backup and Restore to the RDS instance. It took a few minutes to apply, the RDS instance did not reboot or go offline during this process.
- Could not restore over an existing database.
- Learned the hard way that while you can detach, you can’t re-attach a database using SSMS. Reattaching uses a custom stored procedure. And detaching and attaching had nothing to do with deleting.
