How to repair backup of .mdf file database?

Question

Post reply

How to repair backup of .mdf file database?

jordan26henderson

SSC Rookie

Points: 35
More actions
June 30, 2014 at 1:03 pm

#288028

Hi folks,
One of our clients gets the following error when trying to backup a database:
quote:
________________________________________
System.Data.SqlClient.SqlException: SQL Server detected a logical consistency-based I/O error:
incorrect checksum (expected: 0x121db60d; actual: 0x521db60d).
It occurred during a read of page (1:1632) in database ID 4 at offset 0x00000000cc0000 in file
'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.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.
Could not insert a backup or restore history/detail record in the msdb database.
This may indicate a problem with the msdb database.
The backup/restore operation was still successful.
________________________________________
I tried to perform checkdb, checkdb repair_rebuild as well as checkdb REPAIR_ALLOW_DATA_LOSS but none of these corrected the issues is the MSDB database.
My question is: can I just grab a clean MSDB database from another instance to recover corrupt one or do I really have to uninstall/reinstall SQL Server?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

before you do anything, run the followign command, and post the full results here for some peer review:

DBCC CHECKDB('msdb') WITH NO_INFOMSGS, ALL_ERRORMSGS

DO NOT reboot the server, take the database offline, detach it or otherwise fiddle with it; there's a good chance it can be repaired, but you can do things that prevent you from fixing it, so start witht eh checkdb command.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Do you have a clean backup of the MSDB database?

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

jasonkabe22 (7/1/2014)
Data from corrupt mdf file can be recovered by following methods:
Repair MDF file with DBCC CHECKDB: Microsoft offers a command that is known as DBCC CHECKDB, when you run this then it try to detect & fix all the possible error message. If it fails to fix error message then it suggest repair clause. Re-run the command with suggest repair clause. Your problem might be solved. But, you may loss some amount of data after this.
Repair MDF file with SQL Server Fix Toolbox: If you don't want to loss any data then you may try mdf repair tool.
http://www.sqlserver.fixtoolbox.com/[/url]
It repairs corrupt mdf file created in SQL server 2012, 2008 R2, 2008, 2005, 2000 & 7.0

The op already stated that he tried DBCC CHECKDB in virtually every mode known to man including the dreaded "REPAIR_ALLOW_DATA_LOSS ".

Gail's question is leading in the right direction because it sounds like the MSDB database is toast and it's going to take a restore to get most of everything back.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Jeff, would you mind removing the quote of the spam post (that's all those are)?

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