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'
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.
RESTORE DATABASE my_new_database FROM disk = 'E:\path_to_bak_file\database_name_634182143083281212.bak'
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'
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!