Systems Administration

Restoring a .bak file to a SQL Server database

44 Comments

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’.

44 thoughts on “Restoring a .bak file to a SQL Server database”

  1. 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

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

  21. 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…

  23. 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!

  25. 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…

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

  29. 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?

  34. 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.

  37. 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.

  40. 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. 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. A note – the name for the new mdf file has to be different than the existing mdf file for this to work.

  43. 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.

Comments are closed.