16

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.

|improve this question

migrated from stackoverflow.com Oct 14 '13 at 15:49

This question came from our site for professional and enthusiast programmers.

  • Can you or Aaron share why this is a bad idea? or link to the issue where it is explained? – Thronk Oct 15 '13 at 4:44
  • I believe it's something about the logical-names not getting changed, and thus you would end up with two DBs in the same place that have different names and physical-files but identical logical-names, which causes problems for the maintenance-plans that depend on logical-names. In my situation, those restored/copied DBs never lasted for more than a few hours, but I can see why it's not a great practice. – NateJ Oct 17 '13 at 0:22
18

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.

|improve this answer
  • Okay, that helped a little. Now I need to restore a few dozen TRN files to get the DB back to the state it was Friday night (the full backups only happen daily; the TLog backups happen every 15 mins). Using your sample, I got the DB created from the main BAK file, but I get a new error when trying a similar statement to restore the log. It says "the log or differential backup cannot be restored because no files are ready to rollforward". – NateJ Oct 14 '13 at 16:01
  • @NateJ you should ask a full question then. :-) – Aaron Bertrand♦ Oct 14 '13 at 16:02
  • 1
    Awesome, I'm typing it up now! Thanks so much for your help. I'm bummed that I got a few negative rep's at StackOverflow but if this works it will be well worth it. – NateJ Oct 14 '13 at 16:09
  • 1
    @NateJ You should get that rep back because the question was deleted when it was migrated (or will be eventually). It wasn't me but I suspect it was because of your accusation that this worked in one version but didn't now, when that is clearly just a misunderstanding of what was happening... – Aaron Bertrand♦ Oct 14 '13 at 16:13
  • Oh I gotcha. Something like it used to work -- I may not be remembering exactly what/how, but I agree that it was misunderstood. My colleague swears up and down, but now that I think about it, it doesn't make sense. We must have done something differently, using the GUI. I feel like using the commands/scripts as you suggested is going to be better for us. :) The logs are restoring now, successfully so far! – NateJ Oct 14 '13 at 16:17
0

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

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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