Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 6 '12 at 2:24

This question came from our site for professional and enthusiast programmers.

    
This seems like a great way to paint yourself into a fatal corner. –  Jim B Jan 6 '12 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." –  Jason Cumberland Jan 6 '12 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 '12 at 16:44
add comment

1 Answer 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.

share|improve this answer
    
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 '12 at 2:09
    
If able to use enterprise or dev edition this is a pretty good option. –  Jason Cumberland Jan 6 '12 at 8:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.