question

Nash9991 avatar image
Nash9991 asked

How to extract ALL data from a corrupt DB without any recent good known Backups?

Good Day, I recently came across a corrupt database in my Prod environment. According to the msdb..suspect_pages table/view, suspect pages for this DB started around 2016-06-12 so no good known recent backup will work. I was also told that DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS did not work as well. Is there any way to extract all the data from this DB or if possible resolve the corruption issues? SQL Version: SQL Server 2008 R2 SP3 Below is the results of DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS: Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors. Msg 8998, Level 16, State 2, Line 1 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (1:2151408) to (1:2159495). See other errors for cause. Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:2248464) contains an incorrect page ID in its page header. The PageId in the page header = (0:0). Msg 8998, Level 16, State 2, Line 1 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (1:2248464) to (1:2256551). See other errors for cause. Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:3380784) contains an incorrect page ID in its page header. The PageId in the page header = (0:0). Msg 8998, Level 16, State 2, Line 1 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (1:3380784) to (1:3388871). See other errors for cause. Msg 8998, Level 16, State 2, Line 1 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (1:3413136) to (1:3421223). See other errors for cause. Msg 8998, Level 16, State 2, Line 1 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (1:3599160) to (1:3607247). See other errors for cause. Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:3696216) contains an incorrect page ID in its page header. The PageId in the page header = (0:0). Msg 8998, Level 16, State 2, Line 1 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (1:3696216) to (1:3704303). See other errors for cause. Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:4456488) contains an incorrect page ID in its page header. The PageId in the page header = (0:0). Msg 8998, Level 16, State 2, Line 1 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (1:4456488) to (1:4464575). See other errors for cause. Msg 8998, Level 16, State 2, Line 1 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (1:4488840) to (1:4496927). See other errors for cause. Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:4666776) contains an incorrect page ID in its page header. The PageId in the page header = (0:0). Msg 8998, Level 16, State 2, Line 1 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (1:4666776) to (1:4674863). See other errors for cause. Msg 8998, Level 16, State 2, Line 1 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (1:4699128) to (1:4707215). See other errors for cause. Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594038255616 (type Unknown), page ID (1:3413136) contains an incorrect page ID in its page header. The PageId in the page header = (0:160). Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594050707456 (type Unknown), page ID (1:4699128) contains an incorrect page ID in its page header. The PageId in the page header = (0:2016). CHECKDB found 17 allocation errors and 0 consistency errors not associated with any single object. Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 5, index ID 1, partition ID 327680, alloc unit ID 327680 (type In-row data), page ID (1:2151408) contains an incorrect page ID in its page header. The PageId in the page header = (0:2016). CHECKDB found 1 allocation errors and 0 consistency errors in table 'sys.sysrowsets' (object ID 5). Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 1829581556, index ID 0, partition ID 72057594048413696, alloc unit ID 72057594050969600 (type In-row data), page ID (1:3599160) contains an incorrect page ID in its page header. The PageId in the page header = (0:96). Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 1829581556, index ID 0, partition ID 72057594048413696, alloc unit ID 72057594050969600 (type In-row data), page ID (1:4488840) contains an incorrect page ID in its page header. The PageId in the page header = (0:32). CHECKDB found 2 allocation errors and 0 consistency errors in table 'OutboundQueueConfig' (object ID 1829581556). CHECKDB found 20 allocation errors and 0 consistency errors in database '**'.
corruption
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered
Whatever you do, don't reboot the server or bounce the SQL Service until you've recovered the data. The only way that I can think of to do this is to enable xp_CmdShell and have SQL Server create a BCP "QUERY OUT" command for each table and then execute each command. Personally, I'd use TAB as a delimiter but that's up to you. You should also right click on the database in the Explorer window and generate the scripts for all tables and code.,
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jason_clark03 avatar image
jason_clark03 answered
Using Repair_Allow_Data_Loss must not be used unless data-loss not matters to you. As it delete the while repairing database. Once data is loss nothing you can do. Now, If you have any recent healthy backup available you may try to restore it from there. And if not having any recent healthy backup, You try to recover your database using [SQL Server Database Recovery Tool][1]. You may try its free demo version which will repair all your corrupted data and to extract you need full version. [1]: https://www.systoolsgroup.com/sql-recovery.html
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Re: DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS - Too late, I fear. It sounds as though they already tried that. :-(
0 Likes 0 ·
Mrs_Fatherjack avatar image
Mrs_Fatherjack answered
If you have the redgate tools (or use the trial version) could you create an empty database, run SQL Compare to get the schema and then data compare to copy the data across. Just a thought, I did this when I was in the same situation a few years ago in week 2 of a new job when I had a corrupt database and no backups.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DazyParker avatar image
DazyParker answered

@jason_clark03 , data always matters for any business. If the backup is not available then, repairing database from DBCC CHECKDB command. DBCC CHECKDB indicates the repair level that is needed to repair the specific errors.

The DBCC offers two repair modes:

  • REPAIR_REBUILD: This performs a repair that does not lead to any data loss.
  • REPAIR_ALLOW_DATA_LOSS: This performs a repair and fixes to the corrupted database structures, and also results in data loss.

If DBCC CHECKDB asks you to use REPAIR_ALLOW_DATA_LOSS, then take a full database backup first and then run DBCC CHECKDB with the repair option in a user transaction.

If you don't want to use this command then, you can try SQL recovery software to repair corrupt MS SQL database. I will suggest to download the demo version of Stellar Repair for MS SQL. More information is available here: https://www.stellarinfo.com/sql-recovery.php

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.