SQL Svr 08 - Create a .mdf from a .bak

How do I get the mdf for a restored dB to appear?

Page 1 of 1

2 Replies - 3073 Views - Last Post: 27 May 2009 - 06:22 PM

#1 rhett.moeller  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 13-May 06

SQL Svr 08 - Create a .mdf from a .bak

Post icon  Posted 27 May 2009 - 12:43 PM

I recently installed SQL Server Express 2008 to aid in getting a work project going. I received a backup of a database, and was able to get that running successfully on my computer (I can view data in the tables).

But when I go into the directory where other database files are located (c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS08\MSSQL\Data), the restored database doesn't appear there. There are four dBs (.mdfs and .ldfs) that I assume were a part of the standard installation (master, model, MSDBData, and tempdb), but I don't see the one I've restored.

Have I overlooked a step in the restore process? I am trying to set up some ASP code to point to the dB I restored, and as far as I can tell I need the .mdf to be present so I can run my SQL queries against it. Or does ASP access a SQL Svr Express database differently than, say, an Access dB?

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Svr 08 - Create a .mdf from a .bak

#2 mikeblas  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 43
  • View blog
  • Posts: 390
  • Joined: 08-February 08

Re: SQL Svr 08 - Create a .mdf from a .bak

Posted 27 May 2009 - 06:07 PM

If you do "sp_helpdb yourdatabasename", you'll get some results that show you about your database, including information about the files that are in it. That includes the full path to the files.

Depending on how you restored the backup you had, you might have used the same path as the server had the files; or you might have overwritten an existing database; or you might have supplied new file locations. sp_helpdb will tell you what's going on, though.
Was This Post Helpful? 0
  • +
  • -

#3 rhett.moeller  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 13-May 06

Re: SQL Svr 08 - Create a .mdf from a .bak

Posted 27 May 2009 - 06:22 PM

Heh-- I just found the answer to this. The .mdf was created, just not in the directory I was expecting. I found this by walking through the Restore steps with a more critical eye.

1) I right-clicked on Database in the tree and chose "Restore Database. . ."
2) In the Restore Database dialog I assigned the destination a name, entered the database to restore from, and checked the checkbox for my source database at the bottom of the window
3) On the left side of the dialog I switched from the General to the Options page
4) About a third of the way down the Options page was a subwindow that displayed the destination location of the .mdf and .ldf. When I followed that same path, I was able to locate the files for my originally restored database.

Problem solved; now I just need to get the ASP stuff to work!

View Postmikeblas, on 27 May, 2009 - 05:07 PM, said:

If you do "sp_helpdb yourdatabasename", you'll get some results that show you about your database, including information about the files that are in it. That includes the full path to the files.

Depending on how you restored the backup you had, you might have used the same path as the server had the files; or you might have overwritten an existing database; or you might have supplied new file locations. sp_helpdb will tell you what's going on, though.


Thanks for the file reference, Mike. I was able to find what I needed just before you posted, I think; thanks for taking the time to respond, and I'll keep this file in mind for future reference.

Rhett
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1