Restoring a .bak file to a SQL Server database

Posted on May 14, 2003 by ajohnson

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:
    November 6, 2003 at 5:22 pm

    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:
    February 13, 2004 at 8:22 am

    Thanks. That was useful….

  3. Jason Sumner says:
    April 30, 2004 at 11:34 pm

    Good man!

  4. Abdul says:
    September 24, 2004 at 10:52 am

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

  5. Larry says:
    November 4, 2004 at 4:18 pm

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

    Thanks

  6. Jim says:
    December 15, 2004 at 6:17 am

    Larry is right.Site is very helpful.

  7. Matt McCabe says:
    January 3, 2005 at 7:10 pm

    This helped us too! Thanks a bunch!

  8. Jose Martinez says:
    May 18, 2005 at 5:40 pm

    Thanks a lot, it helped us a lot.

  9. Ashu says:
    June 15, 2005 at 8:03 am

    Thanks, was very helpful.

  10. Tom says:
    July 22, 2005 at 1:29 am

    Thank you – Just what I needed!

  11. beaudetious says:
    August 26, 2005 at 10:48 am

    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:
    August 30, 2005 at 9:17 pm

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

  13. Sastry says:
    September 8, 2005 at 12:49 pm

    Thank You. It was just what I needed

  14. Girish says:
    September 15, 2005 at 8:03 am

    Great tip! Made life simple.

  15. geneo says:
    September 27, 2005 at 1:35 pm

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

  16. ronald says:
    October 20, 2005 at 12:45 pm

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

  17. Jon says:
    October 20, 2005 at 5:05 pm

    Good trick.

  18. mayur says:
    October 31, 2005 at 3:10 pm

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

  19. robg says:
    December 4, 2005 at 3:11 pm

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

  20. jason says:
    December 10, 2005 at 5:19 pm

    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:
    December 12, 2005 at 10:14 am

    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:
    December 16, 2005 at 4:01 am

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

    thanks a lot.

  23. Gail says:
    February 8, 2006 at 7:07 pm

    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:
    February 21, 2006 at 10:42 am

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

    Many thanks

  25. Lee says:
    February 21, 2006 at 3:21 pm

    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:
    March 21, 2006 at 5:45 pm

    top explanation. cheers nig

  27. mark says:
    March 24, 2006 at 6:11 pm

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

  28. healy says:
    April 5, 2006 at 4:26 pm

    Very helpful, thanks a lot.

  29. Srinivasa says:
    April 11, 2006 at 12:51 pm

    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:
    May 8, 2006 at 1:07 pm

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

  31. Jesper says:
    May 12, 2006 at 7:47 am

    Thx dude

  32. Mike says:
    October 19, 2006 at 6:30 pm

    This small walk through saved me tonight. Thank you!

  33. jimmyc says:
    November 2, 2006 at 11:49 am

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

  34. Mike says:
    November 23, 2006 at 2:31 am

    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. Vijay Bhatter says:
    December 4, 2006 at 3:13 am

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

    Cheers,
    Vijay

  36. Sangeeta Sharma says:
    December 11, 2006 at 10:47 am

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

  37. Amol Wankhede says:
    January 3, 2007 at 5:14 pm

    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:
    January 3, 2007 at 10:32 pm

    Excellent work !!!

  39. joshua says:
    February 18, 2007 at 9:13 pm

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

    Cheers…

  40. ChUckLeS says:
    March 19, 2007 at 12:13 pm

    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:
    April 24, 2007 at 9:37 am

    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:
    May 4, 2007 at 6:10 am

    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:
    August 29, 2007 at 1:43 pm

    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:
    September 24, 2007 at 5:34 pm

    Awesome man, thanks!!!