SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


database page level corruptions?


database page level corruptions?

Author
Message
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6583 Visits: 3501
Hi,

Database backup failed due to server four HD failure and RAID 5 configuration also OS crashed...not able to reboot the server.. in this case, change the new four HD and install fresh OS then restored database from last day good backup from Tape.. so 1 day data was lost..



I try to restore the failed backup file but it won't allowed ...

Checked by VERIFYONLY command as below errors..

restore verifyonly from disk = 'D:\emsevent\emsevents_backup_2013_03_02_000002_3310132.bak' 
with checksum

Msg 3189, Level 16, State 1, Line 3
Damage to the backup set was detected.
Msg 3013, Level 16, State 1, Line 3
VERIFY DATABASE is terminating abnormally.



restore by CONTINUE_AFTER_ERROR; this command will not help




Processed 528 pages for database 'emsevents', file 'EMSEvents_dat' on file 1.
Processed 0 pages for database 'emsevents', file 'EMSEventsIndex_dat' on file 1.
Processed 16 pages for database 'emsevents', file 'EMSTimeAndLocalTimeIdx_dat' on file 1.
Processed 16 pages for database 'emsevents', file 'EMSLocalDateAndTimeIdx_dat' on file 1.
Processed 16 pages for database 'emsevents', file 'EMSLocalDateAndTimeAndLocalTimeIdx_dat' on file 1.
Processed 2 pages for database 'emsevents', file 'EMSEvents_log' on file 1.
Msg 3283, Level 16, State 1, Line 1
The file "EMSEventsIndex_dat" failed to initialize correctly. Examine the error logs for more details.
Msg 3283, Level 16, State 1, Line 1
The file "EMSTimeAndLocalTimeIdx_dat" failed to initialize correctly. Examine the error logs for more details.
Msg 3283, Level 16, State 1, Line 1
The file "EMSLocalDateAndTimeIdx_dat" failed to initialize correctly. Examine the error logs for more details.
Msg 3283, Level 16, State 1, Line 1
The file "EMSLocalDateAndTimeAndLocalTimeIdx_dat" failed to initialize correctly. Examine the error logs for more details.
Msg 3283, Level 16, State 1, Line 1
The file "EMSEvents_log" failed to initialize correctly. Examine the error logs for more details.
The database cannot be recovered because the log was not restored.
This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.
Damage to the backup set was detected.
RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE successfully processed 578 pages in 0.479 seconds (9.427 MB/sec).

Error log Message

RESTORE detected an error on page (16640:1409297664) in database "emsevents" as read from the backup set.



If any alternative ways how to resolved failed backup set?

thanks
ananda
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217537 Visits: 46278
I think you're going to have to restore an older backup. That looks too damaged to be useful.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6583 Visits: 3501
sorry for mention above errores
I have tested failed backup files, and encounterd errors were from damaged backup file set on 02-03-2013

Successfully restored last day at 01-03-2012 good backup set and database working & fine..
SQLAssAS
SQLAssAS
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1671 Visits: 1285
run regular integrity checks Smile
Steve Jones
Steve Jones
SSC Guru
SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)

Group: Administrators
Points: 142746 Visits: 19424
SQLSteve (8/21/2013)
run regular integrity checks Smile


DBCC CHECKDB on every database at least every month if you can't do it every week or every day.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6583 Visits: 3501
Hi Steve Jones, I have doubt CHECKDB and CHECKSUM.

DBCC CHECKDB - it is allocate the huge resource for checking database Integrity & consistancy operation.

CHECKSUM - it is verify page level corruptions during the database backups as well as new page creation also no resource operations.

Can we kept CHECKSUM option in database Instead checking every week DBCC CHECKDB?

Thanks
ananda
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217537 Visits: 46278
ananda.murugesan (8/21/2013)
Can we kept CHECKSUM option in database Instead checking every week DBCC CHECKDB?


No.

Well, you can do it, but it will give you absolutely no knowledge about whether the database is corrupt or not, so doing so could easily result in a case where you find out you have corruption and find out that all backups back the last 3 month are also corrupted and that there's no way to recover without data loss.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6583 Visits: 3501
I want to know one things,

Enable - Checksum option

if corruptions happend that database by automatically Database backup also failed, so we comes know and checking in error log any page level error or not etc.,
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217537 Visits: 46278
Backups do not automatically fail. You have to explicitly add the option to the backup statement to check the checksums. Maintenance plans don't allow that option and most people don't add that option to their backups.

Plus backup with checksum is in no way a replacement for checkDB. It's better than nothing, but you need to do regular integrity checks if you want to detect corruption early enough to do something about it.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6583 Visits: 3501
Thanks for clarification,

I am going to configure DBCC CHCKDB for all database t monthly once.. could you suggestion me? shloud I go for thru maintenance plan or Mr.ola-allengren coustmize script?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search