------------------------------------------------------------------------------------ --DBA Lounge demo (Database Corruption Chalenge Week 10 - finale) ------------------------------------------------------------------------------------ use [master] ------------------------------------------------------------------------------------ IF EXISTS (SELECT * FROM sys.databases WHERE [name] = 'CorruptionChallenge10') DROP DATABASE [CorruptionChallenge10] GO CREATE DATABASE [CorruptionChallenge10] ON (FILENAME='d:\dba-lounge-2015\demo\week-10\CorruptionChallenge10.mdf'), (FILENAME='d:\dba-lounge-2015\demo\week-10\CorruptionChallenge10_UserObjects.ndf'), (FILENAME='d:\dba-lounge-2015\demo\week-10\CorruptionChallenge10_log.ldf') FOR ATTACH GO --restore this challange database backup file RESTORE FILELISTONLY FROM DISK='d:\dba-lounge-2015\demo\week-10\CorruptionChallenge10Backup1.bak' GO RESTORE DATABASE [CorruptionChallenge10_Backup] FROM DISK='d:\dba-lounge-2015\demo\week-10\CorruptionChallenge10Backup1.bak' WITH MOVE 'CorruptionChallenge10' TO 'd:\dba-lounge-2015\demo\week-10\CorruptionChallenge10-backup.mdf', MOVE 'UserObjectsChallenge10' TO 'd:\dba-lounge-2015\demo\week-10\UserObjectsChallenge10-backup.ndf', MOVE 'CorruptionChallenge10_log' TO 'd:\dba-lounge-2015\demo\week-10\CorruptionChallenge10_log-backup.ldf', REPLACE, RECOVERY GO ------------------------------------------------------------------------------------ SELECT COUNT(*) FROM [CorruptionChallenge10_Backup].dbo.History --43020 SELECT COUNT(*) FROM [CorruptionChallenge10_Backup].dbo.Orders --59019 SELECT COUNT(*) FROM [CorruptionChallenge10_Backup].dbo.Revenue --3054 SELECT COUNT(*) FROM [CorruptionChallenge10].dbo.History --0 SELECT COUNT(*) FROM [CorruptionChallenge10].dbo.Orders --59019 SELECT COUNT(*) FROM [CorruptionChallenge10].dbo.Revenue --3054 ------------------------------------------------------------------------------------ -- Backup tail of the log BACKUP LOG [CorruptionChallenge10] TO DISK = 'd:\dba-lounge-2015\demo\week-10\CorruptionChallenge10_tail_log.trn' WITH NO_TRUNCATE ------------------------------------------------------------------------------------ --run consistency checks DBCC CHECKDB(CorruptionChallenge10) WITH ALL_ERRORMSGS, NO_INFOMSGS, DATA_PURITY GO /* Msg 8944, Level 16, State 12, Line 1 Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data), page (3:32), row 7. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 50 and 23. Msg 8976, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data). Page (3:40) was not seen in the scan although its parent (3:18) and previous (3:39) refer to it. Check any previous errors. Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data). Page (3:41) is missing a reference from previous page (3:40). Possible chain linkage problem. Msg 8944, Level 16, State 12, Line 1 Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data), page (3:32), row 7. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 50 and 23. Msg 8928, Level 16, State 1, Line 1 Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data): Page (3:32) could not be processed. See other errors for details. Msg 8941, Level 16, State 55, Line 1 Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data), page (3:40). Test (dbccAuditRecSucceeded == TRUE) failed. Slot 29, offset 0x2fb is invalid. Msg 8928, Level 16, State 1, Line 1 Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data): Page (3:40) could not be processed. See other errors for details. Msg 8980, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data). Index node page (3:18), slot 0 refers to child page (3:32) and previous child (0:0), but they were not encountered. Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data). Page (3:33) is missing a reference from previous page (3:32). Possible chain linkage problem. CHECKDB found 0 allocation errors and 9 consistency errors in table 'Revenue' (object ID 2073058421). Msg 8939, Level 16, State 5, Line 1 Table error: Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data), page (3:2603). Test (m_headerVersion == HEADER_7_0) failed. Values are 9 and 1. Msg 8928, Level 16, State 1, Line 1 Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data): Page (3:2603) could not be processed. See other errors for details. Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data). Page (3:2324) is missing a reference from previous page (3:2323). Possible chain linkage problem. Msg 8936, Level 16, State 1, Line 1 Table error: Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data). B-tree chain linkage mismatch. (3:2323)->next = (3:2322), but (3:2322)->Prev = (3:2321). Msg 8934, Level 16, State 3, Line 1 Table error: Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data). The high key value on page (3:2323) (level 0) is not less than the low key value in the parent (3:2320), slot 2 of the next page (3:2322). Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data). Page (3:2604) is missing a reference from previous page (3:2603). Possible chain linkage problem. Msg 8976, Level 16, State 1, Line 1 Table error: Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data). Page (3:2603) was not seen in the scan although its parent (3:2707) and previous (3:2602) refer to it. Check any previous errors. Msg 8935, Level 16, State 1, Line 1 Table error: Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data). The previous link (3:3059) on page (3:3058) does not match the previous page (3:3057) that the parent (3:3061), slot 206 expects for this page. Msg 8936, Level 16, State 1, Line 1 Table error: Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data). B-tree chain linkage mismatch. (3:3057)->next = (3:3058), but (3:3058)->Prev = (3:3059). CHECKDB found 0 allocation errors and 9 consistency errors in table 'Orders' (object ID 2121058592). CHECKDB found 0 allocation errors and 18 consistency errors in database 'CorruptionChallenge10'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptionChallenge10). */ ------------------------------------------------------------------------------------ USE [CorruptionChallenge10] GO ------------------------------------------------------------------------------------ --repair table [dbo].[Orders] ------------------------------------------------------------------------------------ SELECT * FROM dbo.Orders GO EXEC sp_helpindex 'dbo.Orders' GO SELECT COUNT(*) FROM dbo.Orders WITH(INDEX(ncOrders)) --59019 records on the table! DBCC TRACEON(3604, -1) GO ALTER DATABASE CorruptionChallenge10 SET SINGLE_USER GO --fix page 3:2603 header DBCC WRITEPAGE(CorruptionChallenge10, 3, 2603, 0, 1, 0x01, 1) GO --analyze BTree chain DBCC IND(CorruptionChallenge10, 'dbo.Orders', 1) /* page prev next 54 0 2321 2321 54 2322 2322 2321 2323 2323 2322 2322 ? --next page should be 2324 2324 2323 2325 3055 3054 3056 3056 3055 3057 3057 3056 3058 3058 3059 3059 ? --prev page should be 3057 3059 3058 3060 3060 3059 3062 3062 3060 3063 */ --fix chain linkage DBCC WRITEPAGE(CorruptionChallenge10, 3, 2323, 16, 4, 0x14090000, 1) --reversed byte order 2324 means 0x00000914 -> 0x14090000 GO DBCC WRITEPAGE(CorruptionChallenge10, 3, 3058, 8, 4, 0xF10B0000, 1) --reversed byte order 3057 means 0x00000BF1 -> 0xF10B0000 GO DBCC CHECKTABLE('dbo.Orders') GO SELECT COUNT(*) FROM dbo.Orders WITH (INDEX(PK_Revenue)) GO SELECT * FROM dbo.Orders --few checksums to be corrected GO --SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x99b0776e; actual: 0x99b3776e). It occurred during a read of page (3:2323) in database ID 7 at offset 0x00000001226000 in file 'd:\dba-lounge-2015\demo\week-10\CorruptionChallenge10_UserObjects.ndf'. 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. DBCC WRITEPAGE(CorruptionChallenge10, 3, 2323, 60, 4, 0x6E77B399, 1) --0x99b3776e GO --SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x8839067c; actual: 0x883d067c). It occurred during a read of page (3:2603) in database ID 7 at offset 0x00000001456000 in file 'd:\dba-lounge-2015\demo\week-10\CorruptionChallenge10_UserObjects.ndf'. 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. DBCC WRITEPAGE(CorruptionChallenge10, 3, 2603, 60, 4, 0x7C063D88, 1) --0x883d067c GO --SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xe967bf0b; actual: 0xe966bf0b). It occurred during a read of page (3:3058) in database ID 7 at offset 0x000000017e4000 in file 'd:\dba-lounge-2015\demo\week-10\CorruptionChallenge10_UserObjects.ndf'. 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. DBCC WRITEPAGE(CorruptionChallenge10, 3, 3058, 60, 4, 0x0BBF66E9, 1) --0xe966bf0b GO DBCC CHECKTABLE('dbo.Orders') /* Msg 8939, Level 16, State 17, Line 1 Table error: Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data), page (3:2603). Test (m_objId == SYSALLOCPG && m_indexId == 0 && m_pageId == pgId) failed. Values are 102 and 99. Msg 8976, Level 16, State 1, Line 1 Table error: Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data). Page (3:2603) was not seen in the scan although its parent (3:2707) and previous (3:2602) refer to it. Check any previous errors. Msg 8939, Level 16, State 18, Line 1 Table error: Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data), page (3:2603). Test (ISGAMEXTID (GetId (), DFLT_EXT_SIZE)) failed. Values are 3 and 3. Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data). Page (3:2604) is missing a reference from previous page (3:2603). Possible chain linkage problem. Msg 8939, Level 16, State 19, Line 1 Table error: Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data), page (3:2603). Test (m_slotCnt == 2) failed. Values are 79 and 2. Msg 8928, Level 16, State 1, Line 1 Object ID 2121058592, index ID 1, partition ID 72057594038648832, alloc unit ID 72057594044612608 (type In-row data): Page (3:2603) could not be processed. See other errors for details. DBCC results for 'Orders'. */ DBCC IND(CorruptionChallenge10, 'dbo.Orders', 1) /* page prev next type 2603 2602 2604 9 SGAM? should be data page, type 1 2604 2603 2605 1 */ --fix page type for 2603 DBCC WRITEPAGE(CorruptionChallenge10, 3, 2603, 1, 1, 0x01, 1) GO --SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x883d067c; actual: 0x8c3d067c). It occurred during a read of page (3:2603) in database ID 7 at offset 0x00000001456000 in file 'd:\dba-lounge-2015\demo\week-10\UserObjectsChallenge10-backup.ndf'. 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. DBCC WRITEPAGE(CorruptionChallenge10, 3, 2603, 60, 4, 0x7C063D8C, 1) --0x8c3d067c GO DBCC CHECKTABLE('dbo.Orders') --done /* DBCC results for 'Orders'. There are 59019 rows in 748 pages for object "Orders". DBCC execution completed. If DBCC printed error messages, contact your system administrator. */ ------------------------------------------------------------------------------------ --repair table [dbo].[Revenue] ------------------------------------------------------------------------------------ EXEC sp_helpindex 'dbo.Revenue' SELECT COUNT(*) FROM dbo.Revenue WITH(INDEX(ncDeptIdYear)) --3054 records on the table! DBCC TRACEON(3604, -1) GO ALTER DATABASE CorruptionChallenge10 SET SINGLE_USER GO DBCC CHECKTABLE('dbo.Revenue') WITH ALL_ERRORMSGS, NO_INFOMSGS, DATA_PURITY /* Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data), page (3:32), row 7. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 50 and 23. Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data), page (3:32), row 7. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 50 and 23. Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data): Page (3:32) could not be processed. See other errors for details. Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data), page (3:40). Test (dbccAuditRecSucceeded == TRUE) failed. Slot 29, offset 0x2fb is invalid. Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data): Page (3:40) could not be processed. See other errors for details. Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data). Index node page (3:18), slot 0 refers to child page (3:32) and previous child (0:0), but they were not encountered. Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data). Page (3:33) is missing a reference from previous page (3:32). Possible chain linkage problem. Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data). Page (3:40) was not seen in the scan although its parent (3:18) and previous (3:39) refer to it. Check any previous errors. Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data). Page (3:41) is missing a reference from previous page (3:40). Possible chain linkage problem. */ DBCC IND(CorruptionChallenge10, 'dbo.Revenue', 1) --look at the corrupted pages DBCC PAGE(CorruptionChallenge10, 3, 32, 2) GO DBCC PAGE(CorruptionChallenge10, 3, 40, 2) GO --look at a good page DBCC PAGE(CorruptionChallenge10, 3, 33, 3) /* --slot 0 0000000000000000: 10001400 44010000 01000000 2b270000 d0070000 ....D.......+'..Ð... 0000000000000014: 0700e1 ..á --slot 1 0000000000000000: 10001400 45010000 0a000000 b72d0000 d5070000 ....E.......·-..Õ... 0000000000000014: 0700e1 ..á --slot 29 0000000000000000: 10001400 61010000 04000000 81450000 da070000 ....a........E..Ú... 0000000000000014: 0700e1 ..á */ DBCC PAGE(CorruptionChallenge10, 3, 41, 3) /* --slot 29 0000000000000000: 10001400 790b0000 05000000 97010000 dd070000 ....y.......—...Ý... 0000000000000014: 0700e1 ..á */ USE master GO ALTER DATABASE CorruptionChallenge10 SET OFFLINE GO --Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data), page (3:40). Test (dbccAuditRecSucceeded == TRUE) failed. Slot 29, offset 0x2fb is invalid. --open a hex editor and go to page 3:40 plus 0x2fb = 502FB --slot hex data is 61 61 32 00. it should be 10 00 14 00. change values in hex editor, save and place database back online ALTER DATABASE CorruptionChallenge10 SET ONLINE GO USE CorruptionChallenge10 GO ALTER DATABASE CorruptionChallenge10 SET SINGLE_USER GO DBCC CHECKTABLE('dbo.Revenue') WITH ALL_ERRORMSGS, NO_INFOMSGS, DATA_PURITY GO SELECT * FROM dbo.Revenue ORDER BY id desc --SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x9000f33a; actual: 0x9998af7a). It occurred during a read of page (3:40) in database ID 7 at offset 0x00000000050000 in file 'd:\dba-lounge-2015\demo\week-10\UserObjectsChallenge10-backup.ndf'. 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. DBCC WRITEPAGE(CorruptionChallenge10, 3, 40, 60, 4, 0x7AAF9899, 1) --0x9998af7a GO --fixed page 3:40 --Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data), page (3:32), row 7. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 50 and 23. --open a hex editor and look at page 03:32. somewhere at row 7.. values may not correct (offset 0x40000) USE master GO ALTER DATABASE CorruptionChallenge10 SET OFFLINE GO --at offset 40101 found sequence 32323200, when it should be 10 00 14 00. change values in hex editor, save and place database back online ALTER DATABASE CorruptionChallenge10 SET ONLINE GO USE CorruptionChallenge10 GO ALTER DATABASE CorruptionChallenge10 SET SINGLE_USER GO DBCC CHECKTABLE('dbo.Revenue') WITH ALL_ERRORMSGS, NO_INFOMSGS, DATA_PURITY GO SELECT * FROM dbo.Revenue --SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x794a8a98; actual: 0x684a9981). It occurred during a read of page (3:32) in database ID 7 at offset 0x00000000040000 in file 'd:\dba-lounge-2015\demo\week-10\UserObjectsChallenge10-backup.ndf'. 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. DBCC WRITEPAGE(CorruptionChallenge10, 3, 32, 60, 4, 0x81994A68, 1) --0x684a9981 GO DBCC CHECKTABLE('dbo.Revenue') WITH ALL_ERRORMSGS, DATA_PURITY GO --There are 3054 rows in 10 pages for object "Revenue". done. ALTER DATABASE CorruptionChallenge10 SET MULTI_USER GO ------------------------------------------------------------------------------------ --repair table [dbo].[History] ------------------------------------------------------------------------------------ --http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/ --load current log file into a table; might need it USE CorruptionChallenge10 GO SELECT master.dbo.ufn_convertLSNToNumeric([Current LSN]) AS [LSN], * INTO log4DBCC10 FROM fn_dump_dblog ( NULL, NULL, N'DISK', 1, N'd:\dba-lounge-2015\demo\week-10\CorruptionChallenge10_tail_log.trn', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT); GO SELECT * FROM CorruptionChallenge10..log4DBCC10 WHERE [Transaction Name] = 'TRUNCATE TABLE' ORDER BY [LSN] GO /* 244000000111800001 256000000042300001 */ --table was truncated at LSN 256000000042300001, and all pages were marked as deallocated SELECT * FROM CorruptionChallenge10..log4DBCC10 WHERE [LSN] >=256000000042300001 -- AND [AllocUnitName] = 'dbo.History' ORDER BY [LSN] GO --since we have the tail backup, let's try to "rollback" just before the truncate RESTORE DATABASE CC10 FROM DISK='d:\dba-lounge-2015\demo\week-10\CorruptionChallenge10Backup1.bak' WITH MOVE 'CorruptionChallenge10' TO 'd:\dba-lounge-2015\demo\week-10\CC10-backup.mdf', MOVE 'UserObjectsChallenge10' TO 'd:\dba-lounge-2015\demo\week-10\CC10-backup.ndf', MOVE 'CorruptionChallenge10_log' TO 'd:\dba-lounge-2015\demo\week-10\CC10-backup.ldf', REPLACE, NORECOVERY GO RESTORE LOG CC10 FROM DISK='d:\dba-lounge-2015\demo\week-10\CorruptionChallenge10_tail_log.trn' WITH NORECOVERY, REPLACE, STATS=10 , STOPATMARK='lsn:256000000042300001' --moment when table was truncated RESTORE DATABASE [CC10] WITH RECOVERY GO SELECT * FROM [CC10].dbo.History --43349 records GO SELECT * FROM [dbo].[History] TRUNCATE TABLE [dbo].[History] --rebuild table dbo.History SET IDENTITY_INSERT [dbo].[History] ON INSERT INTO [dbo].[History]([id], [event], [when]) SELECT [id], [event], [when] FROM [CC10].[dbo].[History] SET IDENTITY_INSERT [dbo].[History] OFF GO DROP DATABASE [CC10] --cleanup GO SELECT * FROM dbo.History --43349 records GO ALTER DATABASE [CorruptionChallenge10] SET MULTI_USER GO ------------------------------------------------------------------------------------ DBCC CHECKDB(CorruptionChallenge10) WITH ALL_ERRORMSGS, NO_INFOMSGS, DATA_PURITY GO --all done BACKUP DATABASE [CorruptionChallenge10] TO DISK='d:\dba-lounge-2015\demo\week-10\CorruptionChallenge10_Recovered.BAK' WITH INIT, CHECKSUM, COMPRESSION GO