November 1, 2005 at 9:49 am
I have a development database and production database on two different servers. I have setup a DTS pacakge to refresh the development database with production data on weekly basis. It takes so long more than 6 hours for the DTS package..
Any ideas on this??
November 1, 2005 at 10:12 am
You may want to consider using the database dumps from the production database. By restoring your development database from the dumps, it should be quicker and more efficiently controlled than through dts packages.
November 1, 2005 at 10:48 am
Depending on the amount of data it might even be beneficial to look at taking a BACKUP of your production and RESTORING it on DEV and then running a script that corrects the orphans and adds/drops users and fixes permissions....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
November 1, 2005 at 11:58 am
You could use differential backups to reduce improve the performance of this. Take a full backup every week and restore it into development and then take nightly differentials and apply them. Depending on the spread of changed data this could be substantially quicker than doing nightly full restores.
November 1, 2005 at 4:59 pm
How much data (e.g. 10GB) and how many rows are you moving on a weekly basis? Across what type of network (100Mb/Sec, 1Gb/Sec?) Are you moving the complete contents of your production servers (including stored procedures, etc.) or just a specific set of data? I would suspect that you're only moving the contents of a few files rather than a complete image of your production databases - developers are working with the stored procedures, etc.?
Joe
November 2, 2005 at 8:53 am
I am moving about 16G data on a 1G network. I am trying to copy the tables and data.
November 2, 2005 at 9:20 am
What about stored procedures, etc? Do they need to be moved as well or left alone on the development box?
Joe
November 2, 2005 at 9:28 am
So, Why would you not just restore the data? Thats what we do. I'm sure it would save you time.
November 2, 2005 at 9:54 am
What tasks are you using in the DTS package?
Greg
November 7, 2005 at 7:13 am
Stored procedues should be left on the development box.
All I am moving is the tables and data.
November 7, 2005 at 1:08 pm
Which option did you use for the dts, Copy Tables and Views or Copy Objects and data? If the table structure hasn't changed, I would think that Copy Tables and View would be quicker because the tables and indexes wouldn't have to be re-created (I could be wrong).
Also, do you have to reload everything? If so, I would restore from backup like suggested but sometimes you don't want to loose the test data or wipe newly designed structures for tables until they are implemented. If there are just certain tables that need to be reloaded, that could cut down the time.
Just some thoughts, I hope this helps.
Steve
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply