Corrupted database. How to restore it from an old backup and add newer changes after?



  • I have a corrupted database with the following error

    Msg 8952, Level 16, State 1, Line 1
    Table error: table 'sys.syscolpars' (ID 41). Index row of index 'nc' (ID 2) does not match any row of data. Additional or invalid keys for:
    Msg 8956, Level 16, State 1, Line 1
    Index row (1:96551:30) with values (id = 1059235274 and name = 'binary_message_body' and number = 0 and colid = 15) pointing to the row of data identified by (id = 1059235274 and number = 0 and colon = 15).
    Msg 8952, Level 16, State 1, Line 1
    Table error: table 'sys.syscolpars' (ID 41). Index row of index 'nc' (ID 2) does not match any row of data. Additional or invalid keys for:
    Msg 8956, Level 16, State 1, Line 1
    Index row (1:96551:32) with values (id=1059235274 and name='conversation_handle' and number=0 and colid=5) pointing to data row identified by (id=1059235274 and number=0 and package = 5). 
    

    I've been advised to restore from a non-corrupted backup. The problem is that the most recent one dates several weeks back. There are some later full backups, but they already have that consistency error. Could I somehow get the differential backup between the stable backup and my latest version? I have tried to do it using Microsoft SQL Server Management Studio on a Windows Server 2019, but I think it contains the difference with to the last created backup (which already contains corrupted data) and I don't know how to find the difference between the stable backup and the data that I currently have.

    Is there a way to delete specific backups so it considers the stable backup as the latest one?

    I'm using this version:

    Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64)   Jan 12 2022 22:30:08   Copyright (C) 2019 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0  (Build 17763: ) (Hypervisor) 
    


  • Table error: table 'sys.syscolpars' (ID 41). Index row of index 'nc' (ID 2)

    Is this the complete error message ?. The index ID is 2 is non-clustered index as per the error message you have posted. Try rebuilding the index it "may" fix the corruption, but since system tables are involved its most unlikely.

    Is there a way to delete specific backups so it considers the stable backup as the latest one?

    No that would not be possible. The restore always follow below sequence Full backup--Diff backup--Log backup. or Full backup--All log backups in sequence. If you have Last full backup which is not corrupted and then "ALL" log backups post that you would be able to get the database restored as near as possible to the corrupt one.

    Other Options:

    Could you try taking backup of current DB with continue_after_error backup database db_name to disk='Drive\folder\db.bak' with continue_after_error. Now try restoring it with continue_after_error and then run checkdb with repair_allow_data_loss to see how much data you loose.

    Meanwhile also try to see how much data you loose with current stable backup, by restoring it. The amount of work needed is tedious but that is the way out here as I see.

    Could I somehow get the differential backup between the stable backup and my latest version?

    The diff backup will be linked to full backup after which it was taken so unless you restore that full backup, assuming it is stable and consistent, it would be of no use.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2