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?