Data corruption is an invalid database state that can prevent users from reading required data. Abrupt shutdowns, faulty storage, and malicious activity can cause it, while leaving it undiscovered increases the chance of an operational incident and data loss. section 9.8
The Friday-night failure
At MagicChoc, a user encounters an error while accessing a mission-critical database on Friday night. Regular checks could have revealed the corruption before it disrupted the business. Detecting and addressing corruption early is easier and less risky than discovering it after prolonged damage. section 9.8 section 9.12
Use a cautious checking and testing routine:
- Check databases regularly with DBCC CHECKDB('MarketingArchive'). section 9.8
- Protect a database with a backup before testing failure scenarios or attempting recovery. section 9.8
- Never run DBCC WRITEPAGE in production: it is undocumented, unsupported, dangerous, and writes directly to a data page. section 9.8
Listing 9.12 Simulating data corruption
USE master ;
GO
BACKUP DATABASE MarketingArchive
TO DISK = 'D:\Backups\MarketingArchive.bak' ;
GO
ALTER DATABASE MarketingArchive SET SINGLE_USER WITH NO_WAIT ;
GO
DECLARE @SQL NVARCHAR(MAX) ;
SELECT @SQL = 'DBCC WRITEPAGE(' +
(
SELECT CAST(DB_ID('MarketingArchive') AS NVARCHAR)
) +
', ' +
(
SELECT TOP 1 CAST(file_id AS NVARCHAR)
FROM MarketingArchive.dbo.ImpressionsArchive
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
) +
', ' +
(
SELECT TOP 1 CAST(page_id AS NVARCHAR)
FROM MarketingArchive.dbo.ImpressionsArchive
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
) +
', 2000, 1, 0x61, 1)' ;
EXEC(@SQL) ;
ALTER DATABASE MarketingArchive SET MULTI_USER ;
GO
Review the controlled SQL Server script that backs up MarketingArchive, switches it to single-user mode, deliberately corrupts a page, and restores multi-user access; use it only for failure testing. section 9.8
