November 13, 2006 at 1:40 am
I had a failure on our RAID array that corrupted two database files, one was operational and the other was a scratch database that had been created for a developer to test some code.
The operational database was restored from backups and transaction logs but the scratch was only created on that day and there was no backup.
I detached the scratch database and created the developer another copy of a live DB to test, but then he told me that he had some important code in the scratch database and asked if I could get it back if only to get he code back.
When I tried to re-attach the scratch database I got the following error:
Error 823: I/O error 38(error not found) detected during read at offset 0000000000000000 in file abc.ldf
So I figured the LDF was corrupt and tried the SINGLE file attach command:
sp_attach_single_file_db 'abc', 'K:\abc\MSSQL$abc\Data\abcBAD\abcBAD.MDF'
This would not work and gave the following error:
Could not open new database 'abc'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'K:\abc\MSSQL$abc\Data\abcBAD\abcBAD.LDF' may be incorrect.
This seems to be looking for an LDF despite the fact that I am using the SINGLE file attach command.
Has anyone got any idea how I can recover this database?
All I need is the code and I need it quickly!
I think this highlights a weakness of storing everything in a single file, i.e. a single point of failure.
Any suggestions would be gratefully received.
November 13, 2006 at 4:19 am
See http://msdn2.microsoft.com/en-us/library/ms174385.aspx
Some potential problems you might be having...
1. Is the DB read only?
2. Did you have multiple files in the DB (ie more than just an mdf + log pair)?
3. Is the LDF file on disk? If so, rename it in case SQL is seeing it already existing and cannot overwrite it
November 13, 2006 at 8:09 am
Thanks Ian
I had a look at the link http://msdn2.microsoft.com/en-us/library/ms174385.aspx
I could not really get any clues from there.
With regard to your questions:
1. Is the DB read only?
The database has not been created yet, the file is not read only nor is the folder. The file was SUSPECT when I detached it. I tried restoring to a different virtual server on the cluster and got the same error.
2. Did you have multiple files in the DB (ie more than just an mdf + log pair)?
NO, there was/is only one LDF file, it was only 12mb and the MDF 600mb
3. Is the LDF file on disk? If so, rename it in case SQL is seeing it already existing and cannot overwrite it
The LDF was renamed already and put in a different folder.
I have been trying again just to confirm, I renamed the directory in case it was a factor, So I type the command again:
sp_attach_single_file_db 'abcBAD', 'K:\OldData\MSSQL$OLD\Data\Suspect\OldName.MDF'
and get
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'abcBAD'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'K:\OldData\MSSQL$Old\Data\OldNAME.LDF' may be incorrect.
I noticed that instead of creating a new LDF in the new SUSPECT subfolder it is referring to what the old LDF used to be called?
I thought that maybe it needed the old LDF even if it was not going to use it, so I put it back in it's orgininal folder and ran the query again. This time I got the following error:
Server: Msg 823, Level 24, State 5, Line 1
I/O error 38(error not found) detected during read at offset 0x00000000cb0000 in file
'K:\OldData\MSSQL$Old\Data\OldNAME.LDF'.
Connection Broken
I do not really care about the data in this DB although it is concerning the the file can become corrupt on a raid 5 array that did not fail but merely got out of sync with a second external array.
Are there any tools that will enable me to rip out the stored procedures from a suspect file?
November 15, 2006 at 6:25 pm
I'm not sure, although there's a tool (might be from RedGate) that lets you compare databases. This tool can do a comparison from a DB that isn't even attached to MS SQL. Could do the trick for you.
There are some clever folks who have written some data recovery articles for MS SQL on the net - I don't have time to look for them at the moment but if you hunt around a bit I believe they discuss situations such as yours. EG, being able to force an attach somehow by setting a "trace flag" and forcing the DB to not be suspect, etc (although it will still be corrupt).
November 16, 2006 at 1:57 am
I found a tool
http://recovery-for-sql-server.recoveronix-ltd.qarchive.org/
but have no way of knowing if they will work, US$500 is a lot to pay for a chance and I have seen reports of it not
The download is a demo and the site does not seem very trustworthy with recovery tools for everything, it could well be a malware site.
I have been going through those articles, e.g.
http://www.spaceprogram.com/knowledge/2002/06/recovering-from-deleted-log-file-on_12.html
but I do not want the data, I want the stored procedures, this is a major failing of Microsoft SQL Server cramming everything in a single MDF file.
The single file attach should have worked and imported it as suspect, even that command is going to be removed in future versions. So Microsoft SQL Server will have very limited data recovery when things go wrong (as they always do).
January 5, 2007 at 7:44 am
OK I had something similar one time when re-creating a test enviornment, long story...but the end result was all we had room for was to zip and move the .MDF files and not the .LDF files to the test server.
Our plan was to just use the single_file_attach_db procedure to re-attach the db's and let SQL re-create the log files. That didn't work and all the db's came up as suspect.
Found an article online and followed the steps...it worked on every database
http://www.myitforum.com/articles/18/view.asp?id=7381
Hope this helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply