November 26, 2013 at 6:16 am
Hi Everyone,
I'm looking to buy one or more recovery tool software, but it's very difficulty to find real world experiences on the internet.
Most of the 'tips' are from the manufacturers, advertising on forums as fake users/specialists.
The rest are just resulted from some googling and grabbing the first results links.
Thats's not what I'm looking for.
I want you (fellows, experts, MVPs, MCM), to share your personal real world experiencies with low level 3rd party recovery software, to be used on those situatons where everything else was tried and failed (including emergency mode, DBCC CHECKDB/CHECKTABLE REPAIR_ALLOW_DATA_LOSS, and so on).
DBCC is a very limited tool and when we didn't have any alternative options left, something recovered / partial recovery may be better than nothing.
There are many situations where one of the last rows of a critical system table (like sysrscols) gets corrupted in a large database.
So most of your database could be saved.
Other times, the size of the database files, for some odd reason are different on the metadata and on the disk, so SQL Server continues to read the file after the end of the file and aborts the connection.
This is something that could be fixed (the metadata information) and a good tool, should report any orphan allocation on metadata, beyond that point, giving us the option to accept that kind os data loss and fix it !
More than that, its very likely that the information on the page header of the actual data page is intact, so an incorret metadata about allocation could be corrected, even in that chaotic situation.
But I didn't found a tool with this kind of simple logic, but precise/detailed error reporting.
Most of them just prints how many rows were successfully read from each table with no reports, on what was wrong, what was fixed, what was lost, and so on (like DBCC actually does on many situations).
This kind of report can make us confident and able to agree an acceptable level of loss with the user or the client.
Bad experiences with some marketed tools are very useful too.
If we don't know what to buy, information on what TO DO NOT Buy is better than nothing.
Links to articles about unsupported system tables fixing/editing, using Dedicated Administration Connection, allowing updates on system tables,... and so on, are also very welcome.
Please no discussion or warnings. I'm aware about the risks, but as I told you before, I'm looking for solutions to exceptional situations where everything else was tried.
I'm sure it will help many of us.
No advertising, please !
November 26, 2013 at 6:42 am
Most of them don't do what they promise. I personally wouldn't use any.
As for what you use when repair doesn't work - restore. Restore from backup. Emergency mode repair is a last resort, it's not guaranteed to work, it should never be the first thing tried.
Fixing incorrect metadata is no where near as easy as you suggest. Irrepairable metadata damage occurs when there's no way to recreate the correct values consistently, so what should such a tool so? Make up values?
It's possible to repair a DB with a hex editor in some cases, such as damaged file or database header, I only know a handful of people in the world capable of doing it though and it's extremely time consuming.
In the case of a single row damaged in sysrscols, you would be able to query most of the database, only the table or index that is described by the damaged row would fail, so it would be possible, if tedious, to export all the other tables, possibly export some columns of the damaged table depending on indexes and then recreate the database. I've advised that several times in the past for databases with damaged system tables and no backups.
Every time I've seen errors around the size of the file, it's been that the metadata is correct and the file has been truncated on disk. That's not exactly fixable, it means a good part of the database is missing. Again, may be possible to construct SELECT statements to extract some data. That's definitely a 'restore from backup' case though.
To be honest though, a well-tested recovery strategy will server you far better than any 3rd party repair tool.
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
November 26, 2013 at 7:35 am
Hi Gail,
Thanks for your promptly answer.
I strongly agree with you about backup strategies, but as you also may have experienced many times in your career, a customer came to us with a damaged database and no backups available.
I've been working with SQL Server in the past 20 years and helped a lot of customers in different crash situations.
I was very explicit in my question, about two things:
* I'm talking about situations where everything else failed;
* I wasn't looking for warnings on risks (Yes, you're right: "Emergency mode repair is a last resort, it's not guaranteed to work, it should never be the first thing tried. " I knew that before asking and that's why I do thank you, but would like to reinforce that this kind of warning is not what I'm looking for).
About fixing metadata, I'm not discussing about the complexity of parsing the files and checking each page's content and I really don't want to go this way.
When I said simple logic, once you know the internal structures and have code to handle all that parsing, the high-level logic to accomplish that next step is simple.
Irreparable metadata is irreparable metadata, period. Again, I agree with you, but thank you !
Your database content may also have more reparable metadata, I would like to have a tool that continues after founding a critical error, because I agree to loose what is really IRREPARABLE.
A tool developed by one of the only "handful of people in the world capable of doing it though and it's extremely time consuming.", to make possible to repair a DB in some cases, such as damaged file or database header.
"In the case of a single row damaged in sysrscols, you would be able to query most of the database, only the table or index that is described by the damaged row would fail, so it would be possible, if tedious, to export all the other tables, possibly export some columns of the damaged table depending on indexes and then recreate the database. "
A software to do this tedious work for us.
If I can do it, or you can do it, then a software can be written to do it for us (remember that scripts shouldn't work in a smooth way, as SQL Server will disconnect your session, situations considered critical... so it's really, really tedious... But writing routines to connect, try to copy, reconnect and continue is not that complex.)
Right now, I want to pay for it, if someone else already spent time on that.
"Every time I've seen errors around the size of the file, it's been that the metadata is correct and the file has been truncated on disk. "
Maybe everytime you've seen errors around size of the file, the metadata was correct, but sometimes it's the opposite...
But even if you're completely right the user or the customer may agree to loose everything that was allocated after that point and save everything else.
Again, if someone would like to help, please answer what I'm asking for, I will really appreciate.
I'm not looking for miracles, neither for hot discussions on what is possible or not, what is the best practice or not, I know what is possible and what is not possible and also the best-practices.
I want to just receive answers from people who have actually used 3rd party recovery tools and the pros and cons they have experienced with this or that specific tool.
Internet is already full of discussions on what should be made before the problem happens and about what the books on-line states.
Please don't polute this thread with this kind of useless discussion.
Hex-Editors are good (as a last of last, last, last... resort, ok! everyone agrees, thank you!), someone knows good plug-ins able to parse MDF, NDF and LDF files ?
That kind of last resort, unsupported situations happens, unfortunatelly they are real and we need to provide full or partial solutions.
Right now, I have another customer on that situation and I'm looking for alternatives.
Answers strictly related to my questions are welcome.
November 26, 2013 at 7:55 am
About fixing metadata, I'm not discussing about the complexity of parsing the files and checking each page's content and I really don't want to go this way. When I said simple logic, once you know the internal structures and have code to handle all that parsing, the high-level logic to accomplish that next step is simple.
Um, no. If it was simple, CheckDB would do it already. It's not simple, it's damned hard, it's not always possible. But since you're not interested in such useless pollution, I'll leave the thread to others
My experience with 3rd party recovery tools is that they don't work. I've tried a couple in cases where there were no backups and CheckDB failed and they retrieved nothing.
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
November 26, 2013 at 8:06 am
If you can share that experience with us, the specific tool name you have used, the exact problem you were trying to fix and the results after running it, yes your answer will be very useful for me and everyone else looking on the same topic.
Thanks again.
November 26, 2013 at 9:50 am
We had a similar issue at one of my previous engagements. The DB files got corrupt, along with the latest DB backup files. DBCC CHECKDB was not able to repair the corrupt metadata. We recovered the data by creating an empty DB with the same schema and exporting the data out of the corrupt DB into the new DB.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply