Solved

How do I open and use a .mdf file.

Posted on 2004-04-28
26
1,518 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:a8le
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
DUMP is just included for backward compatability and shouldn't be used any more......
0
 
LVL 1

Author Comment

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

Author Comment

by:a8le
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:a8le
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Any luck?
0
 
LVL 1

Author Comment

by:a8le
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Radhika_chilka, this question is closed--I suggest you keep following the question that you already opened....
0
 

Expert Comment

by:Radhika_chilka
Comment Utility
Ok , i will ..
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now