We have a rather big database (about 400'000'000 records) in roughly 100 tables. The foreign keys in these tables are defined, so that No Action is taken, when deleting data from a table.

We now have to archive some of these data since only the last year is of any use (from the 9 years of data available).

We decided on a strategy to create a backup database with all the data inside and delete all data except the last year from the production database.

We're asking ourselves what is the best approach to this.

  • Should we change the existing foreign keys so that they cascade delete?
  • Are there any risks involved with this?
  • Should we delete table per table?
  • Is there a simple way to change all existing foreign keys so that they cascade delete?
  • Is there another even better option?

Thanks in advance

  • 1
    Personally I would be very reluctant to do this, especially if it is only for this single, potentially very large operation. I've never really trusted the cascade options in SQL Server, and would opt to write the logic myself (if I have to manually think about all of the tables which need the cascade option set, it isn't that much harder to simply think about the tables as a set of things I need to delete from in a specific order). – Aaron Bertrand Nov 11 '15 at 15:04
  • 1
    All you really need to do is: delete child tables without output into archive tables, delete parent tables with output into archive tables, then perhaps add foreign keys to the archive tables. – Aaron Bertrand Nov 11 '15 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.