November 6, 2008 at 2:24 pm
I have a production database from which I refresh a development database periodically. The two are on different servers on different networks. I use Remote Desktop to access both. I've tried backing up the production DB, getting a copy put on CD (the backups are too large to download), and restoring in on development. This takes forever! So basically I need to chop down the size of the production database (by removing all data older than a year - this is not needed in development) and transform some data (make emails anonymous, etc.) to a manageable file size so I can do the whole process without it taking 2 days.
I've looked in Integration Services for some ideas, but nothing's coming to me. Any suggestions?
November 7, 2008 at 7:18 am
These things always turn into a custom solution. You (hopefully) have foreign key relationships that you will need to make sure are still valid in the copy of the database so you probably cannot remove ALL data that is beyond a certain age.
To have a manageable solution, look at slimming down the large tables and include everything from smaller tables. Try to only move data that actually changes - if you have lookup tables that never have anything changed in them, don't repeatedly copy data.
You may also want to consider looking into replication - you can filter articles so the copy of the database is not as large as the original.
November 7, 2008 at 7:56 am
You are getting into the "Datawarehouse" concept
and yes I agree, usually needs custom solutions (either via SSIS, BCP, etc...)
or is it possible, just take DIFFERENTIAL backups on Production
then restore them on DEV servers (which has the latest FULL backup restored)
since anything older wouldn't change, there is no need to backup-and-restore them all the time
Another helper is, if you can afford to, buy some Backup Compression software (Red Gate, LiteSpeed etc...)
depends on how big your DB is.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply