We have thousands of campaign tables on production. When a campaign is no longer active we want to archive the data off-site. I came up with this strategy, please critique.
Create a new filegroup, create a new table on this filegroup of the campaign being archived (currently 1 campaign = 1 table). Backup the filegroup using compression (either native SQL 2008 or a product like Red-Gate). Move the backup file offsite, restore the filegroup, drop the filegroup from production, drop the original table from production.
A couple of questions:
Q1) Is Red-gate much better than native SQL 2008 compression backups?
Q2) Is there a way to easily move a table from PRIMARY to a new temporary filegroup or is the method I described our only option?
Q3) Do you think I am creating more work for myself or should we just create the table remotely and copy the data over the network? Offsite is geographically distant from production.
Q4) Do we need to worry about the additional load on production due to the backups running (I assume not)?
I know that this wont be much help but i will comment on Red Gate tools or Red gate SQL backup Pro 6 and up. The Tool his awsome the compression ratio his much greater that native compression their backup utility uses multitreading and his much faster. for the price you cant beat it. Sorry i know that it does not answer your question but i had to comment on this.
i was going to post the same tread guess you beat me to it. in all honesty i love Red Gate i love their support. The tools are solid does not cost much a cost that i can easily justify. Its just great but again like the tread that you have seen depends really on your feeling and your testing and what you feel comfortable with. But Red Gate his realy easy to use and friendly. never had issues with with and i manage multiple database's over 1 Terra with log shipping believe me if you choose this tool you will not be dissapointed. Cheers