Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I open and use a .mdf file.

Posted on 2004-04-28
26
Medium Priority
?
1,558 Views
Last Modified: 2012-06-21
Hi all-  

I have an old backup .mdf file from a database that once deleted and now need it again.  

I have two problems:

1) I can't open the file.

2) I don't have the log file.

I need direction on how to do this.  Thank you in advance.
0
Comment
Question by:a8le
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
  • 4
  • +1
26 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10943521
You don't just open it--you need a sql server install to use it.

you can attempt to attach it to your SQL Server installation by executing sp_attach_single_file_db in query analyzer:

sp_attach_single_file_db 'what you want to call the database','thebakfilenameandpath.bak'
0
 
LVL 4

Expert Comment

by:kssaran
ID: 10946324
Hi you can just attach it from Enterprise Manager do the following,

Right click on the Database Folder
Click on the All Tasks ->
Click on Attach Database

Provide the name that you wanna give. Point the File to that old file .Mdf leave the log file blank. Then you will get ur database restored

Rgds
Sara

0
 
LVL 1

Author Comment

by:a8le
ID: 10946407
Arbert-

Thank you for the suggestion but it didn't work.  I did as you said:

1- Open SQL Analyzer

2- Ran this... EXECUTE sp_attach_single_file_db 'Customers','C:\Program Files\Microsoft SQL Server\MSSQL\Data\Customers.mdf'
GO

2A- Got this...Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Customers.mdf' is not a valid database file header. The FILE SIZE property is incorrect.

Then Tries This.........................................................................>

3- Menu File > New > Attach and Detach Database >    Attach_single_file_database_template > then Ran this....

-- =============================================
-- Attach database via sp_attach_single_file_db
-- =============================================
-- note: it builds a new log file and performs additional cleanup work
--        to remove replication from the newly attached database

EXECUTE sp_attach_single_file_db
@dbname   = N'Customers',
@physname = N'c:\program files\microsoft sql server\mssql\data\Customers.mdf'
GO

3A - Got this... Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name '<c:\program files\microsoft sql server\mssql\data\Customers.mdf>' may be incorrect.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:a8le
ID: 10946478
Kssaran-

I have also tried what you have said.  But after choosing the target database.  I get this Error: "The file you've specified is not a valid SQL Server database file."

-a8le
0
 
LVL 4

Expert Comment

by:kssaran
ID: 10946513
By any chance r u tying to restore the dabase mdf file from higher version to lower? or the file that you have is it any kind of Backup file rather than a cold dump of MDF?

Rgds
Sara
0
 
LVL 1

Author Comment

by:a8le
ID: 10946574
Sara-
Its from the same version. SQL Server 2000. But please explain the differences between a backup file and cold dump.  I'll try anything.  
0
 
LVL 4

Expert Comment

by:kssaran
ID: 10946603
Hi,

Cold Dump is the one that you just take the copy of the MDF File.

And the Backup File is nowthing but you run Backup script to create .bak file from the sql server and copy to either data catridge or to ur back up server.

I am sure if you have copied the MDF File when you try to attach it of coz it will look for the same Database Name if i am not wrong. So try in that manner


Rgds
Sara
0
 
LVL 1

Author Comment

by:a8le
ID: 10946652
Sara-

I did a cold dump.  I stop the server and then copied it. From c:program files/microsoft sql server/mssql/data/Customers.mdf

I never changed the name of my database file.  

This isn't just for my old database but also on another I got from ... onezeromedia.com ... I downloaded their open-source shopping cart and am trying to open the OZMCart.mdf within their zip file.

-Thnx
0
 
LVL 4

Expert Comment

by:kssaran
ID: 10946666
Then when you restore it with SQL Server 2000 it wont give any other trouble. Make sure that you give the same Name of the Database

Rgds
Sara
0
 
LVL 1

Author Comment

by:a8le
ID: 10946708
well sara-
Thnx much for all the help.  I am still without a database.  I guess it only my own fault.
-a8le
0
 
LVL 34

Expert Comment

by:arbert
ID: 10949727
DUMP is just included for backward compatability and shouldn't be used any more......
0
 
LVL 1

Author Comment

by:a8le
ID: 10951559
I agree never to do again... but is there still no way to restore it?
0
 
LVL 1

Author Comment

by:a8le
ID: 10954992
hi all-

I have increased the points, if anyone cares.  My projects have changed and now I need the database file from http://www.onezeromedia.com/news/ .  Can someone please help me.  I don't know the origins of the file but I think it is another dump file.  Onezeromedia seems to be more professional than me.  I couldn't image them "dumping files."  Can someone please go to the site and checkout the database please to see what type of file it is and what I should do to access it.  "Yes," I have tried to contact Onezeromedia.  No answer, no response to emails either.  If it is easier you may contact at nospama8le@hotmailnospam (minus nospam). Thanx.

-a8le
0
 
LVL 1

Author Comment

by:a8le
ID: 10955008
sorry-
that's a bad link... try this one...
http://www.onezeromedia.com/news.aspx?id=2003
-a8le
0
 
LVL 34

Accepted Solution

by:
arbert earned 660 total points
ID: 10955118
The definately aren't the brightest people....

The MDF file that's contained in the zip is actually a BAK file (a database backup).  I used this command and got a good restore.  Change the disk='?' to be the location of where you uncompressed the zip file and the mdf is located.  The WITH MOVE option tells sql server to restore the files to a different location from where the original files were located.


restore database OZMCart from disk='c:\OZMCart\OZMCart.mdf'
with move 'Store' to 'c:\store_data.mdf',
move 'store_log' to 'c:\store_log.ldf',
recovery


Brett
0
 
LVL 1

Author Comment

by:a8le
ID: 10955259
Brett-
Can you please go elaborate the process again please.  Am I using the Query Analyzer?  Thnx.
-Able
0
 
LVL 34

Expert Comment

by:arbert
ID: 10955276
What do you mean elaborate?  The above command will run in query analyzer--not sure how much easier I can make it.

Download the zip file, extract it to c:\ozmcart.....

Run the above restore SQL that I posted.....
0
 
LVL 34

Expert Comment

by:arbert
ID: 10955643
Any luck?
0
 
LVL 1

Author Comment

by:a8le
ID: 10955644
Brett-
Thnx for everything.  Sorry to annoy you with a stupid novice quesiton.  I am not that good with sql scripting/admin.  Brett, before I leave you alone... Is there anyway for me to move the location of the data/log files?  I want them in C://program files/microsoft sql server/mssql/....
-Able
0
 
LVL 34

Expert Comment

by:arbert
ID: 10955658
Yep, on the WITH MOVE--just change the C:\ that I had to whatever you want it to be like this:

with move 'Store' to 'c:\program files\microsoft sql server\mssql\store_data.mdf',
move 'store_log' to 'c:\program files\microsoft sql server\mssql\store_log.ldf'


The above isn't your fault.  It's the "idiots" that backed up a database and gave it a MDF extension!!!

For future reference, I just opend the file up with notepad so I can see the header (if it's not a really big file).  At the top of the file, I saw TAPE which led me to believe it was a backup file...


Glad you got it!
Brett
0
 
LVL 1

Author Comment

by:a8le
ID: 10955687
Again thank you- and yet another question...  can I rename store_data.mdf  & store_log.ldf  at the same time.  I tried renaming those things before in the enterprise manager, but it would let me because the database was already created.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10955747
Ya, you can also do that on the WITH MOVE command--just change the names store_data.mdf and store_log.ldf to whatever you want....

You can also use some ugly ALTER DATABASE statements to do it after the fact...
0
 

Expert Comment

by:Radhika_chilka
ID: 12726771
How to open Query Analyzer in MS SQL SERVER.

I am trying to open .mdf file.

I can not see anything on my desktop or in Start--> Programs--> showing someting as SQL Server. But i can see an image on a below bar saying MS SQL running.
0
 
LVL 1

Author Comment

by:a8le
ID: 12727109
Chika~

You have to install that.  You probably only installed the Server but not the administrative tools.

~a8le
0
 
LVL 34

Expert Comment

by:arbert
ID: 12727493
Radhika_chilka, this question is closed--I suggest you keep following the question that you already opened....
0
 

Expert Comment

by:Radhika_chilka
ID: 12727991
Ok , i will ..
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question