Here is the scenario - I have a Database that is suffering from Error 824, dbcc checkdb repair_allow_loss has not rectified it and unfortunately the Backups are also corrupt.

However, I am able to query the individual tables and it spits out Data.

I do have a Schema only backup of the Database - however when I try to insert into the new DB using a select query from the old DB, it errors because some of the tables have auto-increment rows.

Ideally, I'd just use SSMS to generate scripts to re-create the Database - but all those functions currently fail because the DB isn't healthy.

The main thing I care about is getting the Data out of the Tables of the broken DB and into one that is working.

If someone can advise how to do this for a single Table, then I can loop through all the tables and fix it.

  • 2
    Does this answer your question? How to set identity_insert to on in SQL Server
    – AMtwo
    Apr 11, 2022 at 3:25
  • I may be able to use that, I'm looking for a solution that is more auto-magic, kinda like the equivalent of using -force in powershell. If I have to go through each column on each table and turn it off, that may work - but I'm looking for something less manual. Apr 11, 2022 at 3:42
  • Is importing the data using the import/export wizard an option for you? I think in your situation it would be close to the auto-magic solution you seek. You would need to restore the empty schema and use the wizard afterward.
    – Ronaldo
    Apr 11, 2022 at 11:25
  • 1
    @TheDemonLord That's the advise for how to do it for a single table, which was your original question. You don't really need to "go through each column on each table" -- you can just indiscriminately SET IDENTITY INSERT ON/OFF as you loop through tables.
    – AMtwo
    Apr 11, 2022 at 13:11
  • I did some additional testing on how many tables were out of sync, in the end - it was only 2 tables (my bad - I thought it was going to be much worse than that) - so I used that solution in the end- apologies for my previous comment - had wasted a whole day on it and wasn't feeling very chipper. Apr 12, 2022 at 6:06

1 Answer 1


To close this out @amtwo 's solution SET IDENTITY_INSERT tab1 ON/OFF was the solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.