SQL Server 2012 Restore backup to new DB Name

Question

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.

Answer 1

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.

Answer 2

All you need to do to restore the same database multiple times is change the name of the disk files for that database. Obviously you need to give the database a different name to any other database in SQL Server as well. In SSMS after you have selected the .bak file for restore and typed in the name for the database, you then click 'Files' in the "Select a page" section on the left and just change the name of the disk files.

Cheers Doug