This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a demonstration on how to damage a table of SQL Server.
Why I am sharing this because practice on data corruption is required for SQL DBA before actual data corruption happens.
You can find N number of different solutions for restoring corrupted database or table, but what about like if you want to test any of this solution.
In most of the cases, the table gets corrupt because of invalid data or junk data of data page. Using DBCC WRITEPAGE, you can modify the table page.
Please check the below demonstration, and share your commands/options via comments to restore this table.
Create a sample database:
|
1 2 3 4 |
CREATE DATABASE KillMe GO USE KillMe GO |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE tbl_bad ( ID INT IDENTITY(1,1) ,Name VARCHAR(10) DEFAULT 'dbrnd' ) GO CREATE CLUSTERED INDEX ix_ID ON tbl_bad(ID) GO INSERT INTO tbl_bad DEFAULT VALUES INSERT INTO tbl_bad DEFAULT VALUES INSERT INTO tbl_bad DEFAULT VALUES INSERT INTO tbl_bad DEFAULT VALUES INSERT INTO tbl_bad DEFAULT VALUES GO |
Find the page information of table:
|
1 2 |
DBCC IND (KillMe, 'tbl_bad', 1) GO |
Result:
|
1 2 3 4 |
PageFID PagePID IAMFID IAMPID ObjectID PageType ------- ----------- ------ ----------- ----------- -------- 1 320 NULL NULL 565577053 10 1 312 1 320 565577053 1 |
Change database to single user mode:
|
1 |
ALTER DATABASE KillMe SET SINGLE_USER WITH ROLLBACK IMMEDIATE |
Modify page number which page type is 1:
|
1 |
DBCC WRITEPAGE('KillMe', 1, 312, 60, 1, 0x00, 1) |
Now try to select your table data:
|
1 |
SELECT *FROM tbl_bad |
Error in the result:
|
1 2 3 4 5 |
Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xe777b300; actual: 0xe777b303). It occurred during a read of page (1:312) in database ID 10 at offset 0x00000000270000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER\MSSQL\DATA\KillMe.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. |
Check the suspect_pages:
|
1 |
SELECT * FROM [msdb].[dbo].[suspect_pages] |
Result:
|
1 2 3 |
database_id file_id page_id ----------- ----------- -------------------- 10 1 312 |
Leave a Reply