Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

database page level corruptions? Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 2:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 5:56 AM
Points: 1,068, Visits: 3,033
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
Post #1426147
Posted Monday, March 4, 2013 2:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 40,210, Visits: 36,619
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 2008, MVP
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

Post #1426148
Posted Monday, March 4, 2013 3:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 5:56 AM
Points: 1,068, Visits: 3,033
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..
Post #1426151
Posted Wednesday, August 21, 2013 10:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:50 AM
Points: 284, Visits: 864
run regular integrity checks :)
Post #1486802
Posted Wednesday, August 21, 2013 11:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:00 PM
Points: 31,181, Visits: 15,627
SQLSteve (8/21/2013)
run regular integrity checks :)


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
Post #1486821
Posted Wednesday, August 21, 2013 10:22 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 5:56 AM
Points: 1,068, Visits: 3,033
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
Post #1487042
Posted Thursday, August 22, 2013 3:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 40,210, Visits: 36,619
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 2008, MVP
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

Post #1487136
Posted Thursday, August 22, 2013 4:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 5:56 AM
Points: 1,068, Visits: 3,033
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.,

Post #1487157
Posted Thursday, August 22, 2013 4:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 40,210, Visits: 36,619
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 2008, MVP
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

Post #1487160
Posted Thursday, August 22, 2013 4:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 5:56 AM
Points: 1,068, Visits: 3,033
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?
Post #1487171
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse