1

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?

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
1
  • Not the answer I was hoping for but thanks for the confirmation and link to the documentation. Yeah no real benefit to combining backups into a single file.. except combining backups into a single file. I had a few databases with dependencies between them so I was backing them up together to remind me to restore them together. I also needed to upload them to S3 and thought I would be clever and just upload one file but it turns out to be a mistake and S3 handles uploading multiple files really easily anyway. I have just backed up each database one at a time now and it works fine.
    – Caltor
    Commented Aug 12, 2020 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.