0

I need to pull some data from an earlier backup of a current prod database, so I'm using the SSMS Restore Wizard to run a point in time restore and put it into a temporary restore database (for example, source DB=MyProdDB, Destination=TemporaryRestoreDB).

In addition to changing the name of the destination DB in the General page of the SSMS Restore wizard to be TemporaryRestoreDB, do I also need to modify the file names/locations located in the Files tab to not be the same as the MyProdDB names?

Essentially, I'm just double checking if SSMS is smart enough to automatically detect that since it's a different destination on the General tab that it needs new .mdf and .ldf files rather than the existing ones. I tried searching, however didn't seem to have much luck with finding an answer.

Edit: Please make sure to see Tibor Karaszi's comment on the answer below, SSMS 2012 has different requirements than other versions of SSMS on restoring to a different database/files.

2

In my experience, when you change the name of the database during a restore SSMS will automatically update the file names. However, I always double check that it is the case just to be absolutely sure.

  • That's what I would have thought as well, however even once I changed the destination name in the General tab, it didn't change the "Restore As" file name/path in the "Files" tab, it still lists the same name/path as the "Original File Name" which is the MyProdDB .mdf and .ldf files. – Sean K. Sep 18 '18 at 13:10
  • 2
    SSMS 2008 R2 and earlier did change the file names, but in a "dumb way". SSMS 2012 didn't change them (this is probably that you recall, Sean). SSMS 2014 and later changes the file name (again) and in a smarter way than 2008R2 and earlier did. So, do check the filenames, just in case and that the name it generates suites you. :-) – Tibor Karaszi Sep 18 '18 at 13:23
  • Ah, that's likely it then! So for SSMS 2012, one should manually update the Restore As file names/paths, for every other version, it should be fine. Thanks for the additional clarification Tibor! – Sean K. Sep 18 '18 at 13:29

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.