October 13, 2010 at 6:20 pm
One of our hard drive arrays crashed so SQL Server 2005 was not shutdown properly. I was able to recover the MDF file for a database but the LDF file says 0 bytes. When I try to attach the DB in SQL it throw an error saying the LDF file is bad due to it not being shutdown cleanly. Is their anyway to restore the DB with just the MDF file? I did lots of searching online and tried about everything. The only other way I am thinking is possibly using one of those commercial SQL recovery tools. I tried a demo of one and was able to open the MDF and see the data Just trying to see if there was a way to do this without purchasing a tool. Thanks.
October 13, 2010 at 8:22 pm
You best option is to restore from backups and apply all transaction logs up to the point of failure.
You can try to attach the database and let it rebuild - but that will probably not work because it was not shutdown cleanly. Lookup CREATE DATABASE and the option FOR ATTACH, there is an option to rebuild the log file.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 13, 2010 at 8:28 pm
You could save yourself the money of the tool and just rebuild the transaction log in EMERGENCY mode - you'll get the same transactional consistency on the data as from the tool. Check out Creating, detaching, re-attaching, and fixing a suspect database.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 14, 2010 at 10:34 am
The transaction log is bad so I can't re-build from it. I have a good MDF file though.
October 14, 2010 at 10:44 am
mattwilson247 (10/14/2010)
The transaction log is bad so I can't re-build from it. I have a good MDF file though.
Did you try Paul's suggestion?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2010 at 2:24 pm
I see. He is saying to re-build the LDF in Emergency mode. I will look at his link and try it. Thanks.
October 14, 2010 at 2:27 pm
mattwilson247 (10/14/2010)
I see. He is saying to re-build the LDF in Emergency mode. I will look at his link and try it. Thanks.
Yup.
And just for reference, Paul used to be the program manager for the SQL Server Storage Engine. He is absolutely the best person for solving corruption problems of any form. I don't think there's anyone in the world who knows more on that subject than he does.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply