December 12, 2011 at 5:58 am
Please help or guide in this problem, never had this before.
While restoring all teh trasnaction logs, i came to a point on friday when the server had an unexpected shutdown.
Msg 824, Level 16, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x7d7c9874; actual: 0xb0e797f0). It occurred during a read of page (3:88440991) in database ID 7 at offset 0x0000a8b013e000 in file 'Y:\Data\media2.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail.
This is a severe error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
1) I Cant run a DBCC cos the DB is still in a nonrecovered state
2) Should I continue with the restores of the other transaction logs i have ? Will it be possible.
or do i need to start from scratch with my restore and stop at teh point prior to teh failure,
but then what about all teh data after the failure point, how do i restore this.
Thanx for any help, much appreciated.
Imtiaz Mohamed (DBA)
December 12, 2011 at 6:13 am
Could well be that the full backup you used is damaged. The log restore hits the corruption and breaks.
Can your restore just that full backup (restore it WITH RECOVERY) and run CheckDB on it. Post any errors.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Do you have an older backup? When was the last time that you know that the DB was intact (not corrupt)
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
December 12, 2011 at 6:18 am
Thanx for your reply
The failure doesnt happen on the restore of the full back. Full backup restores perfectly , (no recovery)
failure happenes when i am restoring a Transaction Log. i do not want to lose any data. i need to get new server up to latest point.
like it is on the production server as production server has an error that is causing it to shutdown.
December 12, 2011 at 6:21 am
Yes. I understood your explanation.
I suspect that the full backup has the corruption in it. Unless the backup was taken WITH CHECKSUM, corruption will not cause the backup to fail, nor will it cause the restore to fail. Then, when you restore the log backup, it hits that corruption (which was present in the full backup) and fails.
Can your restore just that full backup (restore it WITH RECOVERY) and run CheckDB on it. Post any errors.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Do you have an older backup? When was the last time that you know that the DB was intact (not corrupt)
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
December 12, 2011 at 6:41 am
We have a backup from the previous week.
But thats currently not the first option. cos then we need to get the system up so that production doesnt stay offline. (critical system needed to be up)
if i go to an older backup, how will i get updated data for the 2 weeks after onto my new server. TL backup and Differentials with broken chans due to full backups taken in between.
So my question:
1) Can i restore past the point of error in the transaction log file. ignoring the error)
2) Can i restore just before the point of error.
3) if the Transaction log restore failed, does it mean , the mdf and ldf files havent been updated with the failed TL. meaning if i try and bring the database online with recovery , i would then lose all transaction logs after the point of failure.
So all work done from friday 9/12 1:00 pm, until now i can discard.
Remember the production server is still operational, and TL are still being taken, was working from this monring, until we seen we have an error on the tl restore on teh new server, so we took production off , until we decide which route to take, idealy we do not want to lose any data.
December 12, 2011 at 6:44 am
Remember the production server is still operational, and TL are still being taken, was working from this monring, until we seen we have an error on the tl restore on teh new server, so we took production off , until we decide which route to take, idealy we do not want to lose any data.
Please take a FULL Backup immediately to minimize the data loss (if any).
December 12, 2011 at 6:51 am
imtiazm (12/12/2011)
So my question:1) Can i restore past the point of error in the transaction log file. ignoring the error)
2) Can i restore just before the point of error.
3) if the Transaction log restore failed, does it mean , the mdf and ldf files havent been updated with the failed TL. meaning if i try and bring the database online with recovery , i would then lose all transaction logs after the point of failure.
So all work done from friday 9/12 1:00 pm, until now i can discard.
Don't know, but probably not.
Don't know, but maybe. Depends where the corruption originated, this may still leave you with a corrupt DB.
Don't know, you may not even be able to bring it online.
I need the checkDB results to be able to help you. I'm not guessing here, or talking out of ignorance. To help you I need to know the severity and location of the corruption and to do that requires running CheckDB.
Please do the following:
Restore the full backup that you're talking about to a test/dev server and run CheckDB on it. Post the results.
Run checkDB on your production database. Post any results.
Also. What was the last good checkDB date on your production database? If you're running CheckDB regularly as a job you should know that, if not look in the SQL error logs for the last startup of the server, the date will be listed there.
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
December 12, 2011 at 7:07 am
imtiazm (12/12/2011)
So my question:1) Can i restore past the point of error in the transaction log file. ignoring the error)
2) Can i restore just before the point of error.
3) if the Transaction log restore failed, does it mean , the mdf and ldf files havent been updated with the failed TL. meaning if i try and bring the database online with recovery , i would then lose all transaction logs after the point of failure.
So all work done from friday 9/12 1:00 pm, until now i can discard.
1) CONTINUE_AFTER_ERROR -- You may lose data here... CAUTION
2) May not be applicable to your case but available options...
<point_in_time_WITH_options—RESTORE_LOG>::=
| {
STOPAT = { 'datetime'| @datetime_var }
| STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime']
| STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime']
}
3) May or May not be.
For More... Responding to SQL Server Restore Errors Caused by Damaged Backups
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply