Possible Page error

[psperry2]

Our software vendor which wrote our processing system in Visual Basic using SQL server backend reported to us that we had possible data corruption.

We have an activity table which is used to log transactions like sales and various edits to key tables. We were told that this table had page errors and that some dbcc command (which command exactly the vendor has not specified) corrected the problem except that we may have lost some data in the fix.

We have a raid 5 SQL server and there are no errors in any activity log or SQL log.

Our vendor said that our backing and restoring that database to another database name was copying that error.

This whole explanation from the vendor just doesn't seem possible. Am I wrong?

 

[psperry2]

after restoring a copy of the database from last week, and running SELECT * from Activity, I get this error:

Server: Msg 7987, Level 22, State 3, Line 1
A possible database consistency problem has been detected on database 'OspreyRecovery'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database 'OspreyRecovery'.

Connection Broken

 

[mrdenny]

Your vendor is correct. When you backup a database which has a corrupt data page the corrupt data page is backed up as well. When the database is restored the corrupt page is restore to the new database still corrupt.

When backing up and restoring SQL Server doesn't check the valitity of the data within the backup. It only verifys that the backup was created correctly.

These things happen some times. Use the DBCC CHECKDB to identify the page with the problem, as well as the repair level which is needed to correct the problem. Correcting the problem will need to be done during your maintenance window as it will require as outage as the database must be in single user mode.

Unforunitely these things do happen some times. It could be from a hard disk going bad, or a RAID card going bad. It could have been some piece of software trying to write to the same physical page as the SQL Server (not very likly), or a bug within SQL Server caused the bad write (even less likly). These things can usually be traced to hardware which is a little flaky or getting ready to die.

In addition to doing the DBCC CHECKDB you should also run diagnostistics on your harddrives and raid cards.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog