Today, I was trying to introduce a couple of computed columns to a legacy application’s schema. During this time, I was trying several approaches creating, deleting & re-creating columns using the same name with different definitions and persistence options. When I tried to query data in this table from the application I got the following exception:

Error 582 : SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:8904; actual 1:560702). It occurred during a read of page (1:8904) in database ID 5 at offset 0x00000004590000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.GCIMSDEV\MSSQL\DATA\xxxx.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. 

Sounds pretty scary right? I had never seen this exception before and decided instead of trying a full database restore, I’d try repairing the database. It’s just a development database, so no pressure 🙂

First, I needed to find the table(s) that were corrupted with the DBCC CHECKDB in SQL Server Management Studio

DBCC checkdb('DB_NAME');

Which resulted in the following error in the resulting output listing

DBCC results for ‘tblSAPSynchPayment’.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 180104328, index ID 1, partition ID 293278293950464, alloc unit ID 11803317239808 (type In-row data). Page (1:560690) is missing a reference from previous page (1:8904). Possible chain linkage problem.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 180104328, index ID 1, partition ID 293278293950464, alloc unit ID 293278293950464 (type In-row data), page ID (1:8904) contains an incorrect page ID in its page header. The PageId in the page header = (1:560702).
Msg 8928, Level 16, State 1, Line 1
Object ID 180104328, index ID 1, partition ID 293278293950464, alloc unit ID 293278293950464 (type In-row data): Page (1:8904) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 180104328, index ID 1, partition ID 293278293950464, alloc unit ID 293278293950464 (type In-row data). Page (1:8904) was not seen in the scan although its parent (1:7060) and previous (1:560689) refer to it. Check any previous errors.

All good, now I know which table needs to be repaired. Luckily this was the only table affected and it just happened to be the table I was modifying.

To repair a table using DBCC CHECKTABLE, the database must first be put into single user mode

ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Then, we can repair the affected table. Note the REPAIR_ALLOW_DATA loss option

DBCC CheckTable ('[DB_NAME].[dbo].[tblSAPSynchPayment]',
REPAIR_ALLOW_DATA_LOSS);

Then switch the DB back to multi-user mode

ALTER DATABASE DB_NAME SET MULTI_USER;

Finally, re-check the entire database for errors

DBCC checkdb('DB_NAME');

I think because I had created a persisted computed column and then re-created it with a different formula and non-persisted likely resulted in it becoming corrupted. Of course, your mileage may vary.

Reference

How to troubleshoot Msg 824 in SQL Server
http://support.microsoft.com/default.aspx?kbid=2015756

TechNet – CHECKDB
http://technet.microsoft.com/en-us/library/aa258278(v=sql.80).aspx

TechNet – CHECKTABLE
http://technet.microsoft.com/en-us/library/aa258646(v=sql.80).aspx

Performing backup/restore operations are extremely simple in SQL Server. In most cases, it’s as simple as right-clicking on the database in Management Studio and selecting Tasks->Backup/Restore.

However, if you have ever tried to restore a backup to a database other than the one it came from (such as, same machine/instance with another database name) you may see the following message.

The solution to this problem is to use the Move option on the Restore operation. This can be done in both Management Studio or via T-SQL. However, when trying to use Management Studio, you might ask, “Where is the Move option?” All the other options are labelled beside their appropriate check boxes (all upper case) in the Options page of the Restore Database task

The Move option in Management Studio is actually set when you select different file paths in the Restore the database files as section in the middle of the screen. What you need to do is select a new file location for each file (mdf & log). What I usually do is reset the old paths with the new file paths set up as part of the new database.

Also select the WITH REPLACE option at the top of the screen

In T-SQL you can do the following

USE master;
GO

-- Get number and names of the files in the backup
RESTORE FILELISTONLY
  FROM DISK = 'C:\BAK\OriginalDB.bak';

Gives us information about the files contained in the backup that we can use in the next step

USE master;
GO

-- Do the restore
RESTORE DATABASE [Copy_OriginalDB]
  FROM DISK = 'C:\BAK\OriginalDB.bak'
  WITH
    RECOVERY,
  MOVE 'OriginalDB'
    TO 'C:\Data\Copy_OriginalDB.mdf',
  MOVE 'OriginalDB_Log'
    TO 'C:\Data\Copy_OriginalDB_Log.ldf';
GO

See the following MSDN documents for more info
http://msdn.microsoft.com/en-us/library/ms190447.aspx
http://msdn.microsoft.com/en-us/library/ms173778.aspx