Restoring a .bak file to a SQL Server database

This is for my own benefit… when restoring a .bak file to a SQL Server database where the .bak file was not originally backed up from, right click –> All Tasks –> Restore Database –> From device –> Select Devices –> Add your .bak file. Click OK. Click the Options tab. Now on the left side you should see the Logical file name of the database from the .bak file. On the right side under the lable ‘Move to physical file name’, should be the path that the .bak file originally existed as. This field needs to be changed to the path & file name of the database you’re restoring too. After modifying those two fields, check the ‘Force restore over existing database’ and click ‘OK’.

This entry was posted in Systems Administration. Bookmark the permalink.

44 Responses to Restoring a .bak file to a SQL Server database

  1. Eric Herman says:

    Thanks for the 2003 May 14th “Restoring a .bak file to a SQL Server database” entry … that saved me a lot of work.

    I also needed to execute about a hundred
    “EXEC sp_changeobjectowner ‘foo.table_x’, ‘bar’”
    but I finally got there.

    Cheers,
    – Eric Herman

  2. Venkata Sridhar A. says:

    Thanks. That was useful….

  3. Jason Sumner says:

    Good man!

  4. Abdul says:

    Cheers for writing that SQL from .bak procedure, saved me time too.

  5. Larry says:

    This was very helpful. Saved me a lot of time.

    Thanks

  6. Jim says:

    Larry is right.Site is very helpful.

  7. Matt McCabe says:

    This helped us too! Thanks a bunch!

  8. Thanks a lot, it helped us a lot.

  9. Ashu says:

    Thanks, was very helpful.

  10. Tom says:

    Thank you – Just what I needed!

  11. beaudetious says:

    I was about to give up on this restore and start executing hundreds of SQL commands until I read this blog entry. Appreciate the heads up.

  12. aroon says:

    thanks for the help, this was quick and easy to find thanks to google =]

  13. Sastry says:

    Thank You. It was just what I needed

  14. Girish says:

    Great tip! Made life simple.

  15. geneo says:

    dude- this was the first google hit and was the one that did the job- THANKS!

  16. ronald says:

    WOW — thank you.. I was about to give up, as well.

  17. Jon says:

    Good trick.

  18. mayur says:

    i got stuck up doing this simple procedure, thanks for putting it up on here!

  19. robg says:

    Nice Post – Gave me the exact infp I needed. Thanks

  20. jason says:

    This is exactly what I needed. Just need to figure out why users can’t connect to the new sql server now.

  21. CodeCaster says:

    Jason wrote:
    This is exactly what I needed. Just need to figure out why users can’t connect to the new sql server now.

    I’m encountering the same issue here…

  22. Tina says:

    This was really helpful. Saved me a lot of time.

    thanks a lot.

  23. Gail says:

    Hi all – i have a sql .bak file of a sql server database. It won’t open in access. Will anything open this? THanks much!

  24. Shoba says:

    What a simple and uncomplicated set of steps. Brevity is so underappreciated.

    Many thanks

  25. Lee says:

    Thanks for the SQL instructions for restoring a SQL.bak file….

    I’m new to all this SQL stuff and finding this sort of hands-on info is nearly imposible…

    Thanks again…

  26. nigel says:

    top explanation. cheers nig

  27. mark says:

    Excellent explanation as i forgot being so long since i restored a data base, top marks!

  28. healy says:

    Very helpful, thanks a lot.

  29. Srinivasa says:

    I am getting the deactivation error. The backup I made is from a different machine in D:\Program Files\Microsoft SQL Server\MSSQL\data\navdbsIBS.mdf and navdbsIBS.ldf. In my machine, the database is stored in C:. I followed the steps mentioned above but I am getting the deactivation error. It says the .mdf and .ldf files are incorrect , Use WITH MOVE to identifiy a valid location for the file.

    Can anyone tell give me some input?

  30. A144 says:

    Wow, excellent help. Saved me a lot of time because it works. Thank you!!

  31. Jesper says:

    Thx dude

  32. Mike says:

    This small walk through saved me tonight. Thank you!

  33. jimmyc says:

    Sweeeet !!! And on 50% less caffeine… Impressive !!!

  34. Mike says:

    Thanks, i’ve been messing with these errors until I typed in Google exactly what I wanted to do…

    restore database from bak file

    and got your site. Thanks again. You rule.

  35. Thanks mate for the quick help. This is what I was looking for and it worked :)

    Cheers,
    Vijay

  36. Sangeeta Sharma says:

    Brief and useful. Just what I was looking for..

  37. Amol Wankhede says:

    This is Intresting.

    Well can you suggest If I dont want to create the DB First. I am asking this as You can type the DB name in the Dropdown of the “restore to” on the Restore wizard

    thanks Any ways.

  38. Sutha says:

    Excellent work !!!

  39. joshua says:

    Hi
    the article to restore database johnson worked lik a charm.

    Cheers…

  40. ChUckLeS says:

    Yes, just like everyone else, thank you thank you thank you. I kept encountering an error, until I did a quick google, and you saved the day.

    Have a good one.

    Cheers,

    Charles A.

  41. Clarisa says:

    We switched servers and didn’t realize our database was still being read from the old server. I have the .bak files but there are seven , each dated. When you restore the file, would it be all of them to restore or just the latest one? Also, would you need Enterprise Manager to restore your database w/ the .bak file and how long is that process approximately? Thank You!

  42. Thomas STratton says:

    A note – the name for the new mdf file has to be different than the existing mdf file for this to work.

  43. NN says:

    WHERE do you right-click? I don’t get that option when I click on a db in my application. I am using MS SQL Maestro and I’m stuck trying to restore some dropped tables from a .BAK file.

    So sad.

    THANK YOU.

  44. jeff says:

    Awesome man, thanks!!!