I would like to run a backup of an SQL database (MSSQL) and then delete everything that was included in that backup, essentially a purge of the backed up data, the intent being to archive it at a point in time.
The database structure is simple, only six tables, with only implied FKs. Original thought was to perhaps grab the max PK from all the tables into variables, backup and then delete where PK <= the pre backup capture.
This of course fails to do what I want if an insert occurs between grabbing and deleting (backing up in the middle)
And since I cannot perform a backup in a transaction….
The only way I have been able to come up with reliably is to back it up, temporarily restore as a different name, set my variables from the backed up data, and then go back to the original db to delete. Sure seems like a long way to get to what would seem like a simple task.