We have a monthly purge process that purges data from a few tables using a somewhat complex SSIS package.

Up until this time everything is running smooth and fine without any issues.

However, if the purge process does fail then my only resort would be to restore those tables back to the state they were in before the purge stated. We take backups of the affected tables before we start purging data, however my concern is these tables are in replication and in case if I have to restore the data back do I need to just use import/export or insert into and get done with it or do I need to remove the table out of replication, add the data and then add the table back and generate a snapshot for these articles only?

What if I want to restore the complete table. How am I suppose to do it when the tables are part of transactional replication?


I'm going to assume the table in question is a replication publisher, not a replication subscriber (since you shouldn't be changing data in table that has been replicated from another source).

While you can just delete and then re-insert all the fixed data on a replicated table, if it is large enough that's going to generate tons of commands to replicate over to the subscriber, which (in environments I've worked in) may take hours or days to sync across.

So it is usually much easier to:

  1. Remove the table from replication
  2. Fix the table data, via bulk inserts or updates or SSIS or however you want
  3. Re-add the table to replication
  4. Do not re-initialize the subscription (you just want to snapshot that one table, not the whole database)
  5. Run the snapshot job. It will push a snapshot of just the newly re-added table.
  6. Run the distribution job

One further note: if you have already clogged up your replication with 100 million pending delete/insert/update commands, you can still execute these same 6 steps. The moment you remove the table from replication, all those pending records for that table will disappear from the queue, this usually happens very quickly and will cause a bogged-down subscriber to spring back to life.

  • Does this imply for re-adding the data back if the purge failed? How would I know how many rows I need to insert back if it failed say in between. Is there a way to do a quick comparison between the purged backup table and the actual source table?
    – Feivel
    May 8 '17 at 22:08
  • Not sure what you're asking, @F.Farouqi. Comparing row counts from the replicated source to the replicated destination? Or the messed-up source table to a backup from disk?
    – BradC
    May 8 '17 at 22:28
  • I am not sure @BradC if you understood correctly. Our SSIS package would load a backup table with purged data before purging the actual source table. I am referring to comparing the messed-up source table to the backup table at this point if it fails in between.
    – Feivel
    May 9 '17 at 17:59
  • @F.Farouqi Ah, ok. Hard to say without knowing your schema and process, but you can probably start by validating row counts (bigtable has 1m rows, 100k are copied into a backup table for purge, bigtable should have 900k rows after delete, otherwise something is wrong). Personally, my go-to fix would be to figure out why the delete didn't work, but if you really want to full reset, you'll have to re-insert rows with a WHERE NOT IN clause, so you're only inserting the missing rows. Hope that makes sense, not much room in these comments.
    – BradC
    May 9 '17 at 18:27
  • But to go back to the overall question, yes, if you're ok with deleting 100k rows (for example) with replication enabled, then re-inserting 100k rows if something goes wrong should also be manageable.
    – BradC
    May 9 '17 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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