December 1, 2009 at 10:47 am
I am a .net developer. dont know much about sql. Trying to learn SQL. We have one production server and test server. My task is that i need to copy data from production sql server to test server on regular basis. We have SQl server 2000. I use Data transformation services to export data from one server to another. I need to know is there any approach that i can construct some stored procedure or something else that instead of copying whole database from oner server to another , i can automate this process to just update or add data.
Thanks,
December 1, 2009 at 10:55 am
Yes, you can set up movement of data between different databases on same server or different servers.
You need to script the tables that you need (at source) and create the same at the destination. This is a one time process (but keeping scripts may be handy)
Once the destination tables are ready, you need to start writing code (basically insert into tables sort of scripts) that copies the data over to your destination.
You may also need to put conditions to filter only required columns (if needed) and to move the delta only, you need to have a mechanism of identifying the records that have been moved and not moved. An auto generated Integer value, which can Primary key might help in identifying this.
If on different servers you need to setup and use Linked Server.
You can then schedule these procedures as SQL Jobs
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 3, 2009 at 3:44 am
An alternative option may be to restore the database as a whole rather than exporting data from one server to another - maybe the existing DTS package does this already but if it clears the tables in the test database then repopulates them from the production db, it may make more sense to change it to restore the production db to the test server instead. Any schema/data changes undergoing test would be scripted so they can be re-applied if necessary.
This would reduce the potential maintenance burden of looking after the DTS package when tables are changed/added and would ensure that forgotten earlier test changes to other things like stored procedures are refreshed. I appreciate this solution may not be feasible for you due to other factors such a database size or slow connection between the servers.
Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.
December 3, 2009 at 5:36 am
Yes Colin, I agree with you. There are multiple alternatives to achieve this, but with a database backup, the entire database in the production is moved, which may not be required, if it is a VLDB and Test environment may not require the entire set also.
As always, it depends.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 4, 2009 at 7:31 pm
How much data are we talking about? Is there a reason you don't want to move the whole db?
Most people I know transfer data in bulk at specific intervals so that there isn't any inconsistency. They typically do some backup and restore of the database at the time. If you want to just move changes, replication is the easiest way to do this, though it requires some effort to set up. It can also cause issues when you try to test or change data in those environments and there are changes moving through at the same time.
DTS is probably the easiest way to move tables on a schedule, though I'm not sure it's actually easier than moving the entire db.
The other thing to be careful of is data security. Moving data without being careful or securing is becoming more of an issue. If you want to move it, you ought to think about changing names, dates, etc. so that it's not potentially being release. It's a good habit to get into.
If you can store multiple copies of the data, I've typically moved it through some staging database where I can obfuscate it. If you had SSIS it's easier, but in DTS it can be a pain. Usually you move the data and then you can write stored procedures that change things.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply