{"id":58,"date":"2015-10-10T18:06:44","date_gmt":"2015-10-10T17:06:44","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=58"},"modified":"2015-10-20T21:08:39","modified_gmt":"2015-10-20T20:08:39","slug":"steve-stedmans-dbcc-challenge-week-7-winning-solution","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2015\/10\/10\/steve-stedmans-dbcc-challenge-week-7-winning-solution\/","title":{"rendered":"Steve Stedman\u2019s DBCC Challenge, Week-7 Winning Solution"},"content":{"rendered":"<p>Week 7 of the Database Corruption Challenge (DBCC), winning solution by @SQLDoubleG&nbsp;<em>*First published @ <a href=\"http:\/\/stevestedman.com\/2015\/05\/week-6-the-winning-solution-database-corruption-challenge\/\">Steve Stedman&#8217;s blog<\/a> (2015\/06\/07)<\/em><\/p>\n<p><a href=\"http:\/\/stevestedman.com\/server-health\/database-corruption-challenge\/week-7-database-corruption-challenge\/week-7-challenge-details\/\">The seventh week<\/a> of ten in the <a href=\"http:\/\/stevestedman.com\/server-health\/database-corruption-challenge\/\">Database Corruption Challenge<\/a> went well.\u00a0There were a few new participants, and many of those who have competed week over week. The first to submit a correct answer this week was\u00a0Raul Gonzalez.<\/p>\n<p>What was interesting about this challenge was that it was not your typical corruption where you could just run DBCC CheckDB and find the problem. To understand exactly what happened, solving this required reading through the transaction log that had not yet been backed up and finding the specific problem.<\/p>\n<p>The original post describing the challenge stated the following:<\/p>\n<blockquote><p>Your goal if you choose to accept it is to get restore all the data in the [OPEN_NFIRS].[Record1000] table prior to its disappearance. Note: there may have been multiple DBA\u2019s working on this system in several different time zones across the world.<\/p><\/blockquote>\n<p>Which contained some valid information, and some distracting information. First the distracting part, the fact that the DBAs working on the solution were from multiple time zones across the world. This had nothing to do with anything, and some people asked about it. That statement were purely there to distract. The key items to take away from this comment were:<\/p>\n<p>Multiple DBAs (not necessarily at the same location).<br \/>\nThe table contents just disappeared.<br \/>\nThere was another key clue in the original post\u2026<\/p>\n<blockquote><p>Not all missing data is caused by hardware failures or issues, sometimes it is caused by people. \u2013 Unknown DBA<\/p><\/blockquote>\n<p>Where I was going with this was that the table appeared to be \u201ccorrupt\u201d, but after initial investigation it could be discovered that someone who was logged in as the user \u2018sa\u2019 ran a delete command that possibly forgot a where clause, and dumped the entire [OPEN_NFIRS].[Record1000] table. There was an extra point offered for those who figured that part out.<\/p>\n<p>So once you figured that part out all you had to do was the following:<\/p>\n<ul>\n<li>Take a tail of the log backup from the 3 database files included in the challenge.<\/li>\n<li>Figure out the specific time or LSN of the delete statement from the log.<\/li>\n<li>Restore the provided full backup.<\/li>\n<li>Restore the provided log backups.<\/li>\n<li>Restore the tail of the log backup just up to the point in time before the DELETE statement was run.<\/li>\n<\/ul>\n<p>&nbsp;<br \/>\nThe following solution, all the code from Raul Gonzalez, and the commentary and screen shots are from me. I changed a couple of paths to match my configuration when testing this:<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\n-- try to attach the database files\r\nCREATE DATABASE [CorruptionChallenge7] ON\r\n    ( FILENAME='C:\\SQL_DATA\\CorruptionChallenge7.mdf' )\r\n    , ( FILENAME='C:\\SQL_DATA\\CorruptionChallenge7_log.ldf' )\r\n    , ( FILENAME='C:\\SQL_DATA\\UserObjects.ndf' ) FOR ATTACH\r\nGO\r\n<\/pre>\n<p>The database attaches just fine, and upgrades the files to the current version of SQL Server.<\/p>\n<p><a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step1.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step1.png\" alt=\"Week7_Corruption_Step1\" width=\"808\" height=\"311\" class=\"aligncenter size-full wp-image-59\" srcset=\"https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step1.png 808w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step1-300x115.png 300w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step1-150x58.png 150w\" sizes=\"(max-width: 808px) 100vw, 808px\" \/><\/a><\/p>\n<p>Next take a look to see what the recovery model is on the database. This will determine if the tail of the log backup may be available.<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\nSELECT recovery_model_desc, name FROM sys.databases\r\nWHERE name = 'CorruptionChallenge7'\r\n--recovery_model_desc   name\r\n--FULL  CorruptionChallenge7\r\n<\/pre>\n<p>I like how Raul included his results as comments in the code. This allows me to confirm that I see the same thing that he did going through the process.<\/p>\n<p><a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step2.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step2.png\" alt=\"Week7_Corruption_Step2\" width=\"361\" height=\"84\" class=\"aligncenter size-full wp-image-60\" srcset=\"https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step2.png 361w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step2-300x70.png 300w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step2-150x35.png 150w\" sizes=\"(max-width: 361px) 100vw, 361px\" \/><\/a><\/p>\n<p>So yes, the database CorruptionChallenge7 is in FULL recovery model. Lets take a look at the transaction log.<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\nUSE [CorruptionChallenge7]\r\nGO\r\n \r\nSELECT COUNT(*)\r\n    FROM fn_dblog (NULL, NULL)\r\n-- loads of things there\r\n<\/pre>\n<p>Yes, loads of things in the transaction log, 56591 to be exact.<br \/>\n<a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step3.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step3.png\" alt=\"Week7_Corruption_Step3\" width=\"280\" height=\"106\" class=\"aligncenter size-full wp-image-61\" srcset=\"https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step3.png 280w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step3-150x57.png 150w\" sizes=\"(max-width: 280px) 100vw, 280px\" \/><\/a><\/p>\n<p>So now its time to create a tail of the log backup.<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\n-- Backup tail of the log\r\nBACKUP LOG [CorruptionChallenge7] TO DISK = 'C:\\DBBackups\\CorruptionChallenge7\\CorruptionChallenge7_tail_log.trn'\r\nWITH NO_TRUNCATE\r\n<\/pre>\n<p><a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step4.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step4.png\" alt=\"Week7_Corruption_Step4\" width=\"808\" height=\"311\" class=\"aligncenter size-full wp-image-62\" srcset=\"https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step4.png 808w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step4-300x115.png 300w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step4-150x58.png 150w\" sizes=\"(max-width: 808px) 100vw, 808px\" \/><\/a><\/p>\n<p>At this point we at least know that using backups we can restore to the exact point in time that we are at now. We also know that we can restore to any point along the way if we know where to stop.<\/p>\n<p>So next we run the final check query that needs to be submitted with the solution to see what the table looks like, and we select everything out of the [Record1000] table.<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\nUSE [CorruptionChallenge7]\r\nGO\r\nSELECT COUNT([Record1000Id]) as check1\r\n      ,CHECKSUM_AGG(CHECKSUM([FireDeptID])) as check2\r\n      ,CHECKSUM_AGG(CHECKSUM([FireDeptState])) as check3\r\n      ,CHECKSUM_AGG(CHECKSUM([AlarmDate])) as check4\r\n      ,CHECKSUM_AGG(CHECKSUM([IncidentNumber])) as check5\r\n      ,CHECKSUM_AGG(CHECKSUM([ExposureNumberZeroBased])) as check6\r\n      ,CHECKSUM_AGG(CHECKSUM([RecordType])) as check7\r\n      ,CHECKSUM_AGG(CHECKSUM([TransactionType])) as check8\r\n      ,CHECKSUM_AGG(CHECKSUM([FireDepartmentStation])) as check9\r\nFROM [OPEN_NFIRS].[Record1000];\r\n \r\nSELECT * FROM [OPEN_NFIRS].[Record1000]\r\n-- Empty\r\n<\/pre>\n<p>Which shows there are no rows in the table.<a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step5.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step5.png\" alt=\"Week7_Corruption_Step5\" width=\"732\" height=\"311\" class=\"aligncenter size-full wp-image-63\" srcset=\"https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step5.png 732w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step5-300x127.png 300w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step5-150x64.png 150w\" sizes=\"(max-width: 732px) 100vw, 732px\" \/><\/a><\/p>\n<p>So where did the rows go?<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\n-- Restore From backup to the latest\r\nUSE master\r\n \r\nALTER DATABASE CorruptionChallenge7 SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\n \r\nRESTORE DATABASE [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\CorruptionChallenge7_1.bak'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n, MOVE 'CorruptionChallenge7' TO 'C:\\SQL_DATA\\CorruptionChallenge7.mdf'\r\n, MOVE 'UserObjects' TO 'C:\\SQL_DATA\\UserObjects.ndf'\r\n, MOVE 'CorruptionChallenge7_log' TO 'C:\\SQL_DATA\\CorruptionChallenge7_log.ldf'\r\n \r\nRESTORE LOG [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_0.trn'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n \r\nRESTORE LOG [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_1.trn'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n \r\nRESTORE LOG [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_2.trn'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n \r\nRESTORE LOG [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_3.trn'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n \r\nRESTORE LOG [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_4.trn'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n \r\nRESTORE LOG [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_5.trn'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n \r\nRESTORE DATABASE [CorruptionChallenge7] WITH RECOVERY\r\n<\/pre>\n<p><a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step6.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step6.png\" alt=\"Week7_Corruption_Step6\" width=\"732\" height=\"311\" class=\"aligncenter size-full wp-image-64\" srcset=\"https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step6.png 732w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step6-300x127.png 300w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step6-150x64.png 150w\" sizes=\"(max-width: 732px) 100vw, 732px\" \/><\/a><\/p>\n<p>Now lets take a look at how things look.<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\nDBCC CHECKDB('CorruptionChallenge7') WITH NO_INFOMSGS\r\n-- No corruption this week..\r\n<\/pre>\n<p><a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step7.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step7.png\" alt=\"Week7_Corruption_Step7\" width=\"617\" height=\"311\" class=\"aligncenter size-full wp-image-65\" srcset=\"https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step7.png 617w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step7-300x151.png 300w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step7-150x76.png 150w\" sizes=\"(max-width: 617px) 100vw, 617px\" \/><\/a><\/p>\n<p>No corruption remaining, so lets look at the data.<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\nUSE [CorruptionChallenge7]\r\nGO\r\nSELECT COUNT([Record1000Id]) as check1\r\n      ,CHECKSUM_AGG(CHECKSUM([FireDeptID])) as check2\r\n      ,CHECKSUM_AGG(CHECKSUM([FireDeptState])) as check3\r\n      ,CHECKSUM_AGG(CHECKSUM([AlarmDate])) as check4\r\n      ,CHECKSUM_AGG(CHECKSUM([IncidentNumber])) as check5\r\n      ,CHECKSUM_AGG(CHECKSUM([ExposureNumberZeroBased])) as check6\r\n      ,CHECKSUM_AGG(CHECKSUM([RecordType])) as check7\r\n      ,CHECKSUM_AGG(CHECKSUM([TransactionType])) as check8\r\n      ,CHECKSUM_AGG(CHECKSUM([FireDepartmentStation])) as check9\r\nFROM [OPEN_NFIRS].[Record1000];\r\n-- Check done to the latest transaction log backup\r\n-- check1   check2  check3  check4  check5  check6  check7  check8  check9\r\n-- 1010     835     0       131902  2099290 0       0       0       67\r\n<\/pre>\n<p><a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step8.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step8.png\" alt=\"Week7_Corruption_Step8\" width=\"594\" height=\"311\" class=\"aligncenter size-full wp-image-66\" srcset=\"https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step8.png 594w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step8-300x157.png 300w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step8-150x79.png 150w\" sizes=\"(max-width: 594px) 100vw, 594px\" \/><\/a><\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\n-- let's see what the tail log looks like\r\nDECLARE @LogFile varchar(max);\r\n \r\nSET @LogFile = 'C:\\DBBackups\\Challenge7Tail.bak'\r\n \r\nSELECT *\r\nINTO #dump_dblog\r\nFROM fn_dump_dblog( DEFAULT, DEFAULT,DEFAULT, DEFAULT, @LogFile, DEFAULT,DEFAULT,\r\n                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT )\r\n \r\nSELECT * FROM #dump_dblog\r\nWHERE [transaction Name] IS NOT NULL\r\n-- Interested in this one\r\n--Beginlog Status   Xact Type   Begin Time      Transaction Name    Transaction SID\r\n--0x01      1       2015\/05\/31  17:57:56:287    DELETE              0x01\r\n<\/pre>\n<p>Using the fn_dup_dblog function to scan the tail of the log transaction log backup to find the interesting DELETE statement, which occurred at 17:57:56:278.<\/p>\n<p><a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step9.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step9.png\" alt=\"Week7_Corruption_Step9\" width=\"594\" height=\"311\" class=\"aligncenter size-full wp-image-67\" srcset=\"https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step9.png 594w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step9-300x157.png 300w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step9-150x79.png 150w\" sizes=\"(max-width: 594px) 100vw, 594px\" \/><\/a><\/p>\n<p>So lets look at what happened after that DELETE statement.<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\nSELECT * FROM #dump_dblog\r\nWHERE [Begin Time] &gt;= '2015\/05\/31 17:57:56:287'\r\n-- Seems like the last transaction on that table..\r\n<\/pre>\n<p><a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step10.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step10.png\" alt=\"Week7_Corruption_Step10\" width=\"594\" height=\"311\" class=\"aligncenter size-full wp-image-68\" srcset=\"https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step10.png 594w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step10-300x157.png 300w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step10-150x79.png 150w\" sizes=\"(max-width: 594px) 100vw, 594px\" \/><\/a><\/p>\n<p>Scrolling through the list after the DELETE, no other commands, just SQL Server doing its thing to cleanup after the DELETE statement. So now we know everything that is needed to restore to the exact point in time (or just before it). Run the same list of transaction log backups we did before, but add in the tail of the log backup and tell it to stop at the DELETE statement.<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\n-- Restore the database and STOPAT the time of the last\r\nUSE master\r\n \r\nALTER DATABASE CorruptionChallenge7 SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\n \r\nRESTORE DATABASE [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\CorruptionChallenge7_1.bak'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n, MOVE 'CorruptionChallenge7' TO 'C:\\SQL_DATA\\CorruptionChallenge7.mdf'\r\n, MOVE 'UserObjects' TO 'C:\\SQL_DATA\\UserObjects.ndf'\r\n, MOVE 'CorruptionChallenge7_log' TO 'C:\\SQL_DATA\\CorruptionChallenge7_log.ldf'\r\n \r\nRESTORE LOG [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_0.trn'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n \r\nRESTORE LOG [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_1.trn'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n \r\nRESTORE LOG [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_2.trn'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n \r\nRESTORE LOG [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_3.trn'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n \r\nRESTORE LOG [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_4.trn'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n \r\nRESTORE LOG [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_5.trn'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n \r\n--==================================== IMPORTANT BIT ==================================\r\nRESTORE LOG [CorruptionChallenge7]\r\nFROM DISK='C:\\DBBackups\\Challenge7Tail.bak'\r\nWITH NORECOVERY, REPLACE, STATS=10\r\n, STOPAT='2015\/05\/31 17:57:56:287' -- date time of the what I believe is the last transaction on the table\r\n \r\nRESTORE DATABASE [CorruptionChallenge7] WITH RECOVERY\r\n<\/pre>\n<p>The tail of the log backup is restored to just before the DELETE statement.<\/p>\n<p><a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step11.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step11.png\" alt=\"Week7_Corruption_Step11\" width=\"594\" height=\"311\" class=\"aligncenter size-full wp-image-69\" srcset=\"https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step11.png 594w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step11-300x157.png 300w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step11-150x79.png 150w\" sizes=\"(max-width: 594px) 100vw, 594px\" \/><\/a><\/p>\n<p>So now run the final checksum query that needed to be submitted as proof of completion.<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\nUSE [CorruptionChallenge7]\r\nGO\r\nSELECT COUNT([Record1000Id]) as check1\r\n      ,CHECKSUM_AGG(CHECKSUM([FireDeptID])) as check2\r\n      ,CHECKSUM_AGG(CHECKSUM([FireDeptState])) as check3\r\n      ,CHECKSUM_AGG(CHECKSUM([AlarmDate])) as check4\r\n      ,CHECKSUM_AGG(CHECKSUM([IncidentNumber])) as check5\r\n      ,CHECKSUM_AGG(CHECKSUM([ExposureNumberZeroBased])) as check6\r\n      ,CHECKSUM_AGG(CHECKSUM([RecordType])) as check7\r\n      ,CHECKSUM_AGG(CHECKSUM([TransactionType])) as check8\r\n      ,CHECKSUM_AGG(CHECKSUM([FireDepartmentStation])) as check9\r\nFROM [OPEN_NFIRS].[Record1000];\r\n-- Current\r\n--check1    check2  check3  check4  check5      check6  check7  check8  check9\r\n--3010      2346    0       1643050 26216226    0       0       0       246\r\n \r\n-- Previous\r\n-- check1   check2  check3  check4  check5  check6  check7  check8  check9\r\n-- 1010     835     0       131902  2099290 0       0       0       67\r\n \r\n-- Seems like we've got much more rows..\r\n<\/pre>\n<p><a href=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step12.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step12.png\" alt=\"Week7_Corruption_Step12\" width=\"594\" height=\"311\" class=\"aligncenter size-full wp-image-70\" srcset=\"https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step12.png 594w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step12-300x157.png 300w, https:\/\/www.sqldoubleg.com\/wp-content\/uploads\/2015\/10\/Week7_Corruption_Step12-150x79.png 150w\" sizes=\"(max-width: 594px) 100vw, 594px\" \/><\/a><\/p>\n<p>And that\u2019s how Database Corruption Challenge #7 was won by Raul Gonzalez.<\/p>\n<p>Not all \u201ccorruption\u201d is what you traditionally think of as corruption.<\/p>\n<p>Raul received 4 points this week:<\/p>\n<ul>\n<li>One point for being first to complete the challenge correction.<\/li>\n<li>Two points for solving it before any additional clues where given.<\/li>\n<li>One point for providing that the DELETE was performed by the user \u2018sa\u2019.<\/li>\n<\/ul>\n<p>&nbsp;<br \/>\nAlmost all of the winning solutions this week were very similar to this, however some used the LSN on the final restore, rather than the timestamp. Those solutions looked something like this:<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\nRESTORE DATABASE [CorruptionChallenge7]\r\n   FROM DISK = N'C:\\DBBackups\\CorruptionChallenge7\\CorruptionChallenge7_1.bak'\r\n   WITH FILE = 1,\r\n        MOVE N'CorruptionChallenge7' TO N'C:\\SQL_DATA\\CorruptionChallenge7.mdf',\r\n        MOVE N'UserObjects' TO N'C:\\SQL_DATA\\CorruptionChallenge7UserObjects.ndf',\r\n        MOVE N'CorruptionChallenge7_log' TO N'C:\\SQL_DATA\\CorruptionChallenge7_log.ldf',\r\n        NORECOVERY, REPLACE, STATS=10;\r\n \r\n-- apply the log files that we have\r\nRESTORE LOG [CorruptionChallenge7]\r\n    FROM DISK = 'C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_0.trn' WITH NORECOVERY;\r\nRESTORE LOG [CorruptionChallenge7]\r\n    FROM DISK = 'C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_1.trn' WITH NORECOVERY;\r\nRESTORE LOG [CorruptionChallenge7]\r\n    FROM DISK = 'C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_2.trn' WITH NORECOVERY;\r\nRESTORE LOG [CorruptionChallenge7]\r\n    FROM DISK = 'C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_3.trn' WITH NORECOVERY;\r\nRESTORE LOG [CorruptionChallenge7]\r\n    FROM DISK = 'C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_4.trn' WITH NORECOVERY;\r\nRESTORE LOG [CorruptionChallenge7]\r\n    FROM DISK = 'C:\\DBBackups\\CorruptionChallenge7\\TransLog_CorruptionChallenge7_5.trn' WITH NORECOVERY;\r\nRESTORE LOG [CorruptionChallenge7]\r\n    FROM DISK = 'C:\\DBBackups\\Challenge7Tail.bak' WITH NORECOVERY,\r\nSTOPATMARK = 'lsn:52000000032800002';\r\n \r\nRESTORE DATABASE [CorruptionChallenge7] WITH RECOVERY;\r\n<\/pre>\n<p>The solutions using the LSN required converting it from hex to decimal, which isn\u2019t exactly obvious. I created a function to do the LSN conversion, and included it in the extra clue this week. Another participant <a href=\"http:\/\/www.rentadba.eu\/2015\/06\/03\/convert-lsn-to-numeric\/\">Dan Andrei Stefan provided an alternate function<\/a> to do the same thing. His function was the inspiration to create my own function to do the conversion. Here is my function.<\/p>\n<pre class=\"brush: tsql; title: ; notranslate\" title=\"\">\r\nCREATE FUNCTION [dbo].[dbhCrackLSN](@currentLSN AS VARCHAR(25))\r\nRETURNS VARCHAR(25)\r\nAS\r\nBEGIN\r\n    DECLARE @returnLSN as [numeric](25);\r\n \r\n    ;WITH lsnCte\r\n    AS\r\n    (\r\n        SELECT CONVERT(INT, CONVERT(VARBINARY, '0x' + LEFT(@currentLSN, 8), 1)) AS Part1,\r\n        CONVERT(INT, CONVERT(VARBINARY, '0x' + SUBSTRING(@currentLSN, 10, 8) , 1)) AS Part2,\r\n        CONVERT(INT, CONVERT(VARBINARY, '0x' + RIGHT(@currentLSN, 4) , 1)) AS Part3\r\n    )\r\n    SELECT @returnLSN = CAST(Part1 as VARCHAR(10)) + RIGHT('0000000000' + CAST(Part2 as VARCHAR(10)), 10) + RIGHT('00000' + CAST(Part3 as VARCHAR(5)), 5)\r\n      FROM lsnCte;\r\n    RETURN @returnLSN;\r\nEND\r\nGO\r\n<\/pre>\n<p>There was one solution provided by Patrick Flynn which restored all of the data without using any of the backups. He only used the 3 database files (MDF, LDF, NDF). I will post this solution tomorrow, so check back to see how he did it.<\/p>\n<p>So that wraps up Database Corruption Challenge #7. I hope everyone enjoyed this weeks competition.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Week 7 of the Database Corruption Challenge (DBCC), winning solution by @SQLDoubleG&nbsp;*First published @ Steve Stedman&#8217;s blog (2015\/06\/07) The seventh week of ten in the Database Corruption Challenge went well.\u00a0There were a&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[11,13,12],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/58"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=58"}],"version-history":[{"count":0,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/58\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=58"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=58"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=58"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}