SQL Server 2012 Restore backup to new DB Name

I seem to remember that, in 2008, you could restore a backup to a new copy of a DB, by changing the name in the "Destination Database" field from the restore-wizard. It would create a brand new DB, which is a copy of the original DB restored to the point in time that you wanted. I have not for the life of me figured out how to make SQL 2012 do this.

Now, I understand (thanks to Aaron Bertrand) that this didn't really change, and that 2012 is actually making it more obvious to me that this strategy was a bad idea in the first place!

So, what I need to do is this: Create a new DB, 'MyDB_Copy', from an existing DB, 'MyDB', by using its backup files. We have nightly full-backups (.bak) and every-15-minute TLogs (.trn). I don't want the existing 'MyDB' to be affected/touched at all, because it's "live".

After the MyDB_Copy is created from the main full-backup file, I then need to restore a few dozen TLog backups to get it to a certain point in time.

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 = '', 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.

StackExchange Database Administrators Q#51489, answer score: 18