March 4, 2013 at 2:53 am
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
March 4, 2013 at 2:55 am
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
March 4, 2013 at 3:08 am
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..
August 21, 2013 at 10:29 am
run regular integrity checks 🙂
August 21, 2013 at 11:28 am
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.
August 21, 2013 at 10:22 pm
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
August 22, 2013 at 3:32 am
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
August 22, 2013 at 4:24 am
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.,
August 22, 2013 at 4:29 am
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
August 22, 2013 at 4:48 am
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?
August 22, 2013 at 4:54 am
Is once a month often enough? Do you retain all your backups (full and log for month)? If not, what do you plan to do if the CheckDB picks up corruption and you have no clean backup to restore?
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
August 22, 2013 at 5:05 am
GilaMonster (8/22/2013)
Is once a month often enough? Do you retain all your backups (full and log for month)? If not, what do you plan to do if the CheckDB picks up corruption and you have no clean backup to restore?
due to HD space issues, I have mention backup file retention period is last 4 days in local HD, but daily all backups goes to Tape drive after completion SQL Backups.. I can get old 30 days backup files from Tape.
August 22, 2013 at 6:26 am
Log backups as well?
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
August 22, 2013 at 8:55 am
Once a month is the bare minimum, and still gives you potential data loss of a longer period if you are only keeping 4 days. I hope you have tapes that go back longer.
I'd say practically you should take the performance hit and do this once a week, or even more often.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply