My system is importing data from a number of external sources on a nightly basis into staging tables in my local DB. The process backs up the relevant table (by copying all data to a replica table with a special name to indicate it's a backup table), clears the table, then imports data from the sources. If the import job fails, it just restores the data from the backup table.
The reason for backing up the staging tables is because if the job fails, we would rather see data that is a day or so older than having no data until the reason for the failure is fixed. Only one backup table exists per critical staging table, and with the next backup, the previous backup is wiped.
The problem with this approach is that the number of "backup tables" are growing quite significantly as the system expands. I've been thinking of deleting the backup table after the job succeeded, but is this good practice? (deleting and recreating every night)
Another reason for keeping the backup table would be, if the job succeeded, but the data got misaligned somehow, something that will only be picked up once the business day starts the next morning, the backup table can give you the opportunity to quickly revert to the version before the corrupted import.
This whole approach just doesn't feel quite right, and I am wondering if there is better approaches to this design.
The system is using MS SQL Server 2012, but we are afforded very little in terms of server admin tools and tasks.