Re-creating Database from only *.mdf sql data file - Is It Possible & how?

Question

Post reply

Re-creating Database from only *.mdf sql data file - Is It Possible & how?

Doug Brewer

Newbie

Points: 2
More actions
January 5, 2005 at 8:32 pm

#85040

Hello:
I have had a hard-drive crash, with only a backup .mdf sql data file. Is there a way to restore the database from just this file. (I understand that not all the data might be restored because of no log files.)
This is SQL 2000.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

Try attaching the database in EM and it will create a new log file for you.

1. Right click "Databases"
2. Choose "All Tasks" -> "Attach Database"
3. Browse to your mdf file
4. Click "OK"
5. You will be prompted about the missing log file and asked if you want a new one to be generated.

Kathi

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Doug Brewer Newbie Points: 2 More actions · Answer 2

"5. You will be prompted about the missing log file and asked if you want a new one to be generated."

Didn't work. Instead of asking about the log, it say's it can't creat the database without the log.

Any other ideas?

Thanks.

Doug

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

I tried it on Pubs before posting the answer and it worked for me.

So, today, I created a new database and made sure that the recovery model was set to full. I then detached the db and deleted the log file. I then tried to attach the db and it worked.

Here is an article I found from a Google search, maybe it will have additional information that can help:

http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

hoo-t SSChampion Points: 12713 More actions · Answer 4

It may not be any different than the Enterprise Manager method, which may actually be doing this behind the scenes, but take a look at sp_attach_single_file_db in books online.

Steve