Overwrite Existing DB from Backup

Question

I'm attempting to bring a copy of our production database to our staging database. They are on different RDS instances. I tried using the native SQL Backup/Restore methods but I keep getting an error.
Code I ran on production:

exec msdb.dbo.rds_backup_database @source_db_name='DBName',@s3_arn_to_backup_to=N'arn:aws:s3:::path/to/backup/DBName2019-09-17.bak'

It worked just fine. When I attempt to restore using this command (I ran this on the staging RDS instance):

exec msdb.dbo.rds_restore_database @restore_db_name='DBName',@s3_arn_to_restore_from='arn:aws:s3:::path/to/backup/DBName2019-09-17.bak'

I receive an error: Msg 50000, Level 16, State 1, Procedure msdb.dbo.rds_restore_database, Line 91 Database 'DBName' already exists. Two databases that differ only by case or accent are not allowed. Choose a different database name.

I've been googling for a bit and can't seem to find a definitive answer. I have several databases on the staging RDS instance; I don't relish the idea of having to create a new RDS instance every time I want to bring a copy of production into staging...

How do I restore a copy of production into staging without having to create a new RDS instance?

Answer 1

AFAIK - there is no Overwrite option in RDS for restoring the database to the existing name.

Execute the drop database command first and then do the restore.

USE [master];
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'database_name')
BEGIN
ALTER DATABASE [database_name]   SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  
DROP DATABASE [database_name]; END;

GO

EXEC msdb.dbo.rds_restore_database @restore_db_name='database_name', 
@s3_arn_to_restore_from= 'arn:aws:s3:::bucket_name/file_name_and_extension'; 
GO

EXEC msdb.dbo.rds_task_status;

you can use the last command to check the status of the backup or Restore