0

I have DB of size 170GB. One of the table has 339500 rows out of which around 2000 rows are corrupted. When I tried backing up the database I get "Backup with 'Checksum' gave error"

SQL Server version: 2008 R2
Edition : Express edition

Whenever DBCC Checktable is executed I will get below error "Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors."

When the following query was run ,

SELECT * FROM [msdb].[dbo].[suspect_pages]

we found that few pages in a table was corrupted.

But presently I don't have latest good(non corrupted) backup. DBCC repair with allow data loss is also not working.

I have found the rows in the table which are corrupted but is there any way to get rid of these corrupted rows? After searching for solution in the internet, I found out that moving the rows from corrupt database to new database is the only solution which is not feasible for me now. Other than using a 3rd party tool for repair is there any other method to backup without the corrupted data?

Edited:

Previous month I could able to backup the Db "Backup without 'Checksum'" , but now even that doesn't work.

DBCC CheckDB result given for reference in this link

3
  • 1
    when you run DBCC CheckDB (without repair options), what exactly error it throws ? can you post it to the question Jun 9, 2022 at 15:07
  • also if these corrupted pages are, for example, in a clustered index of a table, and if these columns/rows are also part of some nonclustered index and they are not corrupted in this NC index, you can copy values of these rows from nonclustered index to clustered or vice versa... you get the idea Jun 9, 2022 at 15:09
  • Also, that error message is worrying. It seems you have such a severe corruption so even CHECKDB refuses to run. Copy whatever can be salvaged to a new database is likely the remedy. I'd get help (MS, or some consultant) if that is too steep for you, diving deeper into the corruption issue and try something based on that, or restoring from a healthy backup isn't desirable. Jun 10, 2022 at 8:42

2 Answers 2

1

You cannot exclude specific rows when performing a SQL backup.

You should take a full backup (without the checksum option) to have a rollback option or to restore it on another server from where you will start working on fixing the corruption.

You can try to give more information here (like the result of the DBCC CHECKDB) and hope someone will be able to help or, if it's an important database and you need to get it fix (fast), you can open a ticket with Microsoft and they will help you fix it (you may end up loosing data if you did not have the required backups)

0

In this situation, Usally I do not prefer any third party tools but here I can go with another option as you wrote in your questions. You will get rid off from this tough challenge in a few minutes.

I found two best tools for you.

  1. Stellar Repair for MySQL / MS SQL
  2. Kernel for SQL Recovery

I hope it will help you to fix it.

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .