Based loosely on Example E in the documentation, open a new query window and run:
RESTORE DATABASE MyDB_Copy FROM DISK = 'C:\blahblah\MyDB.bak'
WITH REPLACE, RECOVERY,
MOVE 'MyDB' TO 'C:\blahblah\Data\MyDB_Copy.mdf',
---------------------------------------^^^^^
MOVE 'MyDB_log' TO 'C:\blahblah\Data\MyDB_Copy.ldf';
-------------------------------------------^^^^^
The logical names are not important; the physical file names are. This makes assumptions about your logical file names and that there are only two; run EXEC MyDB..sp_helpfile;
to be sure.
If you need to restore logs, then change RECOVERY
to NORECOVERY
:
WITH REPLACE, NORECOVERY,
----------------^^
Then you can issue a series of:
RESTORE LOG MyDB_Copy FROM DISK = 'C:\blahblah\file1.trn' WITH NORECOVERY;
And on the very last one:
RESTORE LOG MyDB_Copy FROM DISK = 'C:\blahblah\fileN.trn' WITH RECOVERY;
Or if you only need part of a log up until a point in time (I assume you've checked where the LSNs and times are so you know exactly which files you need):
RESTORE LOG MyDB_Copy FROM DISK = 'C:\blahblah\fileN.trn' WITH
STOPAT = '<some point in time Friday>', RECOVERY;
The way you said worked in previous versions would never have worked, unless the backup came from a different server. By default it will try to put the new mdf and ldf files in the exact same place, and this isn't possible.
It doesn't matter how many backups are there. You would have the same error message if there was only one backup in the backup set.
I would either restore the backup 74 under different database name (put its name in "To database" textbox), or would rename current database to some other name, e.g. spetztest_OLD and restore the backup under original name. Then you can make sure you don't need the old one and drop it.
Best 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 usingFILE =
.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.