January 9, 2008 at 12:44 pm
I had a problem where a table was corrupt due to disk io issues and i could not repair it so it was decided it be best to restore the dbase to a specific point in time.
The problem is my boss felt we should be able to Restore the dbase which would fix the table corruption then restore all transactions to current?? ( see below timeline)
Anyway I disagreed and restored to right before the disk io problem that caused the corruption in the table and all was ok.
In test I tried recreating the issue and it would not let me put it out of load state until I tried restoring prior to the disk io issue. so I think I proved I did the right thing.
Question did I do the right thing. In test I proved I believe I did. My boss sees it as a deficiency in sql. I disagree
thoughts ?
Timeline
11pm backup
log dumps
622am disk problems which caused corruption
last log dump 915am
Boss expected restore logs to 915am. I restored logs prior to 622am
January 9, 2008 at 12:55 pm
Unless it was a log record that was corrupt, you should be able to roll all the logs forward, right up to the last. If you backed up the tail of the log, you should be able to restore right to the last transaction done
The log contains the details of the transactions done, not the state of the disk, so the corruption (if it was in the data file) shouldn't reoccur if you replay the transactions.
That said, I've never tried this. The only time I've had corruption, it was a log record that was corrupted. Hence we were unable to roll forward past that point.
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
January 9, 2008 at 1:11 pm
You could at try to roll forward all the way to the end of the last log file, and then run DBCC CHECKDB to see if if there is any problem with the database.
If there is no problem, you're good; if there is a problem, you could fall back to your original plan.
January 9, 2008 at 4:04 pm
Yup - should be no reason why a full restore of all the log shouldn't work UNLESS the corrupt data was somehow picked up and used (thus causing second order corruptions - e.g. a corrupt column value being used to compute an answer that's persisted in another table).
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
May 20, 2008 at 10:39 pm
Hi,
Maybe you can try some tools to recover the dbase to the desire point. I have used a tool called Advanced DBF Repair to repair my DBF database and it worked well. If necessary, you can have a try.
Hope this helps.
May 20, 2008 at 10:57 pm
What is a DBF 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
May 24, 2008 at 6:27 am
sorry, I mean dbf file.
May 24, 2008 at 10:58 am
ok - that acronym still doesn't mean anything as far as SQL Server is concerned. MDF, NDF, LDF are the acronyms people use for the primary file, secondary files, and log files - respectively.
What does dbf stand for? Is it a SQL Server file/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
May 24, 2008 at 2:10 pm
The collective wisdom of google seems to indicate it's a dBase file format, also used in Foxpro and a few other apps
http://en.wikipedia.org/wiki/DBASE#File_formats
Not particularly relevant to a thread on corrupt SQL Server databases.
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply