<p>I have a set of tables that are used to track bills. These tables are loaded from an SSIS process that runs weekly.</p>

<p>I am in the process of creating a second set of tables to track adjustments to the bills that are made via the web. Some of our clients hand key their bills and all of those entries need to be backed up on a more regular schedule (the SSIS fed data can always be imported again so it isn't backed up).</p>

<p>Is there a best practice for this type of behavior? I'm looking at implementing a DDL trigger that will parse the ALTER TABLE call and change the table being called. This is somewhat painful, and I'm curious if there is a better way.</p>

I personally would have the SSIS-fed tables in one database (set to simple recovery mode) and the other tables in a separate database on the same server which is set to full recovery mode,. Then I would set up backups on the second datbase on a regular schedule. A typical backup schedule would be full backup once a week, differntials nightly  and transaction backups every 15-30 minutes depending on how much data is being input.) Be sure to periodically test recovering the backups, learning how to do that when the customer is screaming becasue the datbase is down isn;t a good thing.