Restore specific MSSQL backup from media set using AWS rds_restore_database

Question

I have backed up several Microsoft SQL Server databases to a single media set (.bak file). I have uploaded the database to S3 and am now trying to restore the database using the following script where the database is named MyDB and the backup media set file is MyBackup.bak.
EXECUTE msdb.dbo.rds_drop_database N'MyDB'
EXECUTE msdb.dbo.rds_restore_database @restore_db_name = 'MyDB',@s3_arn_to_restore_from='arn:aws:s3:::dev-web/DbBackup/MyBackup.bak';

Unfortunately this just seem so to restore the first backup from the media set which is from another database. Is there a way to specify which backup I want to restore from the .bak file?

Aaron Bertrand · Accepted Answer

Seems from the documentation that the procedure doesn't support specifying which file within a .bak you're actually trying to restore. The native RESTORE DATABASE command allows you to specify this using FILE = .
I think the solution in your case is to always back up any database to a single .bak file (name the backup file uniquely, and always use WITH INIT when backing up). Combining multiple backups into a single file doesn't have a single benefit that I can think of, especially combining multiple backups of different databases.
To get to the backup you want, you may have to download the multi-file backup file, restore it on your own instance using RESTORE DATABASE ... WITH FILE =, back that up to a new, single-backup .bak file, and upload it back to AWS. But I would consult with AWS support in case they have any other (easier) options.

Restore specific MSSQL backup from media set using AWS rds_restore_database

One Answer

Add your own answers!

Ask a Question