Solved

How do I open and use a .mdf file.

Posted on 2004-04-28
26
1,544 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 165 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

789 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