May 26, 2011 at 12:36 pm
Hello everyone,
Here is my problem/question:
I need to import the data that is in our production system into our test for a couple of databases...At first I was like ok, that should not be a problem...However when I went to create the SSIS package, I noticed that you have to use set up a data flow task for each table that is in the database...And I dont mind doing that for a couple of our db's that have just a few tables BUT there is one database that has over 1500 tables in it...therefore I would have to set up a data flow task for each one...Is there an easier way to do that?
Also there are foreign key constraints and primary key constraints...My supervisor said he doesnt mind to delete/truncate the tables on test before importing the new data from production...When I tried just going through the Import/Export wizard, I got errors saying that it was violation of some of the constraints...I tried disabling the constraints, and importing them in but then I received a primary key constraint error...I tried disabling the constraints and then deleting the records from the tables before going through the wizard and it still gave me errors concerning constraints...I dont know how I need to proceed, I figured SSIS was the way to go but I will still get those errors when I try to run the package...Can someone give me some pointers or some type of direction on how to go about this thing?
Basically I just need to know the best way to get the production data to the test tables and how to deal with the constraint issue...
Any advice will be helpful...Thank you in advance!
May 26, 2011 at 12:48 pm
Have you considered a straightforward backup/restore?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 26, 2011 at 12:56 pm
I have done that however, he doesnt want to copy over the sp's and other objects, he just wants the data...also our backups are only on tape...
May 26, 2011 at 1:06 pm
asm1212 (5/26/2011)
I have done that however, he doesnt want to copy over the sp's and other objects, he just wants the data...also our backups are only on tape...
sounds like the DEV server has changes they want to keep;
I'd use something like Redgate SQL Compare to script out the schema/object differences from Say, DEV, restore a backup of production(as a DEV2 to avoid anyone panicking) , then apply the scripted schema changes I got from SQL Compare ;
much easier, faster, and would actually ewmulate what would happen when the server on production gets the newly updated objects...you'/d see if anything breaks.
Lowell
May 26, 2011 at 1:16 pm
I see what you are saying but right now I dont have Redgate on my PC and plus Im not worried about the schema...The schema's are pretty much the same on both servers...there is just dummy data in test and he wants the real data from production in test so can test more efficiently with actual data...
When using SSIS, is there a way to connect to all the tables in one task like in the Import/Export wizard...Or do I have to create a task for each table?
May 26, 2011 at 1:29 pm
asm1212 (5/26/2011)
I have done that however, he doesnt want to copy over the sp's and other objects, he just wants the data...also our backups are only on tape...
It would be easier to do the backup / restore and then script out the deletion of unwanted objects.
Surely there is nothing preventing you from creating additional backups on disk?
Here's the basic process - SSIS can do it all:
1) Backup production to disk.
2) Use a file system task or a script task to move the backup from prod to test server (if necessary)
3) Force restore backup over test db.
4) Run an execute SQL task to drop unwanted objects from the restored database.
5) Job done.
The good thing - you don't have 500 dataflow tasks to maintain. And if there are additional tables or schema changes in future, no problem.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 26, 2011 at 1:40 pm
Phil Parkin (5/26/2011)
asm1212 (5/26/2011)
I have done that however, he doesnt want to copy over the sp's and other objects, he just wants the data...also our backups are only on tape...It would be easier to do the backup / restore and then script out the deletion of unwanted objects.
Surely there is nothing preventing you from creating additional backups on disk?
Here's the basic process - SSIS can do it all:
1) Backup production to disk.
2) Use a file system task or a script task to move the backup from prod to test server (if necessary)
3) Force restore backup over test db.
4) Run an execute SQL task to drop unwanted objects from the restored database.
5) Job done.
The good thing - you don't have 500 dataflow tasks to maintain. And if there are additional tables or schema changes in future, no problem.
I hear you and trust me I am not trying to be difficult...But if he just wants the data, why do I need to backup/restore the whole database which will include data, tables, schema, stored procedures, etc...I thought this was going to be a piece of cake when he first gave me the task b/c the first thing that came to mind is Import/Export - but then I started running into the constraints issue...And that has thrown me for a loop...Surely there is a way in SSIS that i can mimmick the Import/Export wizard logic...Take each table in production and copy it in test...That would solve the SSIS issue as to having a bunch of data flow tasks...But still doesnt solve the constraints issue, do either of you have any input where that is concerned?
May 26, 2011 at 2:03 pm
asm1212 (5/26/2011)
Im not worried about the schema...The schema's are pretty much the same on both servers...there is just dummy data in test and he wants the real data from production in test so can test more efficiently with actual data...?
if it doesn't matter if there are schema changes, the backup restore is absolutely the fastest and best way to go; doa COPY_ONLY restore to disk and go for it!
if theree is a business reason to save stored proc differences, that is different...copyying data in a specific manor that someone else tells you to do gets into "My realm" vs "Your Realm"...
what if he told you he needs relevant data, but wants you to hand type the differences? you'd say something like, no, there's an easier way than retyping....
here it's the same thing...
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply