Home / server / Questions / 347193
In Process
Sabre
Asked: 2012-01-06 17:53:24 +0800 CST

Delete everything backed up in SQL

  • 772

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.

sql-server

1 Answers

  1. Take a snapshot of the DB, then back it up and then compare and contrast the snapshot and current DB.

    Having said that - without verifying the backup with a restore how can you guarantee that the back up worked.

    EDIT: Following comment:

    If you only have 6 tables, why not do a SELECT INTO BACKUP to a TEMPORARY DB - and back that up? Sort of roll your own snapshot.

    • 1