Reply to post: Re: I've mentioned it before...

A cautionary tale of virtual floppies and all too real credentials

veti Silver badge

Re: I've mentioned it before...

If you've got time to get through all that, it's not a "live" database.

If I tried that at my old job, before I was halfway through making the backup, users would be on the line demanding to know why the system wasn't responding. (Because the table was locked by my transaction.)

Use SELECT * FROM table WHERE condition to identify the data you want. Then draft another SELECT for a test sample to include both some of the records you want to delete, and some of those you don't.

Once you're satisfied with both of those:

BEGIN TRAN

SELECT * INTO backup_table FROM table WHERE condition (backup only the records you want to delete)

DELETE t FROM table t JOIN backup_table bt on t.id_column = bt.id_column (converse of previous step - delete only records that have been backed up)

SELECT * FROM table WHERE test_sample_condition

ROLLBACK

Check the results returned by the test sample, and take a look at the backup_table as well. When you're satisfied with those, change the ROLLBACK to COMMIT and run it for real.

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon