Restore an SQL 2008 .bak file to my local SQL 2008 server

I know for some of you it sounds like an easy and straightforward process, however, I found it challenging and that’s why I decided to share it with you.

I get a backup file from our live server “database_name_634182143083281212.bak” and downloaded locally. Then I open the SQL 2008 management studio and create an empty database called “my_new_database”.

I open a new query window and choose the master database. Look this is very important as the “my_new_database” doesn’t need to be in any process when you try to restore it. Next use the next few lines to find out the real database logical names.

get file logical names


restore filelistonly from disk = 'E:\path_to_bak_file\database_name_634182143083281212.bak'
GO

That’s it now you need both logical names in order to restore data and structure to the new blank database you just created a few minutes ago. Have a look on the screen shot below.

sql2008-restore

sql2008 restore script result

restore script


RESTORE DATABASE my_new_database FROM disk = 'E:\path_to_bak_file\database_name_634182143083281212.bak'
WITH
   MOVE 'the logical name from previous operation check row 1' TO 'E:\path_to_sql2008_file_onyour_machine\my_new_database.mdf',
   MOVE 'the logical name from previous operation check row 2' TO 'E:\path_to_sql2008_file_onyour_machine\my_new_database_log.ldf'
GO

Now run the query and everything should operate smoothly. I use the restore filelistonly script because the logical names are completely different than the name you see on your database. The specific database had moved from SQL 2005 to SQL 2008 about a year ago and no matter what name I gave it, it’s logical name was still the one that given when first time created. Hope this helps!

Tags: ,

  1. Mpinno Cento’s avatar

    I’ve never been blessed like today.
    You guys made my day, my month, my year…….!!
    Thanks a lot.
    Cento.

    Reply

  2. MrX’s avatar

    Thanks a lot man. I lost all the day because of the logical name :-)

    Reply

  3. somy’s avatar

    Thank you for your tutorial.
    I had a .bak file from my database at a server and I tried to restore it with your method to local host at sql server, but I encountered the following error:

    The database was backed up on a server running version 10.50.2500. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

    I will be happy if you help me,
    thank you

    Reply

  4. pushpa’s avatar

    this site is very good.

    Reply

  5. JT’s avatar

    Thanks. Much appreciated.

    Reply

  6. H’s avatar

    Dude. thank you. you made my day :)

    Reply

Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>