1

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.

3
  • This seems like a great way to paint yourself into a fatal corner.
    – Jim B
    Jan 6, 2012 at 6:39
  • Could you give more details on your intention for this line? "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." Jan 6, 2012 at 8:26
  • Well, after testing the way that assuredly does what I want by restoring the backup temporarily. I have discovered a flaw in what I was trying to do to begin with. The database in question has values that are set in one table based on values in another table via trigger. The deletion of the backed up data makes these triggers fail, and is not the option I once thought it to be so I guess the question is now moot, thank you for the responses though.
    – Sabre
    Jan 6, 2012 at 16:44

1 Answer 1

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.

2
  • For clarification, servers range from MSDE 2000 to 2008 Express R2 (none with advanced services) I do not believe snapshots are an option.
    – Sabre
    Jan 6, 2012 at 2:09
  • If able to use enterprise or dev edition this is a pretty good option. Jan 6, 2012 at 8:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .