Share via

Take Backup of a Database without the Corrupted rows

Researcher 11 Reputation points
2022-06-10T06:38:00.753+00:00

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.
Previous month I could able to backup the Db "Backup without 'Checksum'" , but now even that doesn't work.

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?

SQL Server | Other
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2022-06-10T21:23:39.817+00:00

    I found out that moving the rows from corrupt database to new database is the only solution which is not feasible for me now.

    So you seriously think that continuing with a corrupted database which you cannot backup is better?

    Would it be feasible for you to lose the database entirely?

    Also, keep in mind that corruption does not occur at random, but the reason is that you have faulty hardware. So you should determine which component which is malfunctioning and replace it.

    1 person found this answer helpful.
    0 comments
  2. Olaf Helper 47,441 Reputation points
    2022-06-10T06:40:35.327+00:00

    any other method to backup without the corrupted data?

    No way, a database backup is always an exact 1:1 copy of the source database, you can't exclude anything.
    You have to try to repair the database.

    0 comments
  3. YufeiShao-msft 7,146 Reputation points
    2022-06-10T07:39:26.473+00:00

    Hi @Researcher ,

    You should fix your corrupted pages first, otherwise you have no way to make a backup.

    You can check your error log for additional information
    If possible, you can try to restore your database from the last successful backup, If no clean backup is available, you can try to execute DBCC CHECKDB, it will recommend a repair clause to use, and then execute DBCC CHECKDB with the appropriate repair clause to repair the corruption.

    -------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.