tags:

views:

84

answers:

3

I have an application and I want to move some of the tables from the production database to the development database to refresh the data on development. I don't need to move all of the tables.

This application is still running on SQL2000 and planned to be upgraded next year. I have SQL2008 installed on my workstation. I was thinking about using SSIS for this, but not sure that it will work. Is this the best way or is there something easier? I would like this to be automated so I can do it when needed.

Thanks in advance,

Randy

+4  A: 

Check out RedGate's Sql Compare and Data Compare. These tools do exactly what you need to do and they do it well. There are others like Red Gate out there btw, but i've found RG to be the most solid.

Paul Sasik
+1 yes, indeed - excellent stuff!
marc_s
I have Red Gate and will take a look at this. I didn't think about this, because I wanted to be able to automate it.Thanks,Randy
Iceman
Not 100% sure but i think you can actually automate w/ Red Gate.
Paul Sasik
I was able to add the Schema, now I'm trying to figure out what tool to use to move the data. Red Gate worked great to move the Schema and thought is was going to move the data. I have the full suite of tools and looking for the tool to move the data.
Iceman
You now need to use the Data Compare application. Not sure why they split it into two apps, probably marketing, but they did.
Paul Sasik
Yeah, figuring it out. Works pretty slick and I can save both formats off. I made a copy from Development to my local desktop, now I need to set it up for Production to Development.Thanks,Randy
Iceman
If you own the 'pro' versions of SQL Compare/SQL Data Compare you can automate using the command line. Or use the SQL Comparison SDK if you own this.
David Atkinson
+1  A: 

You can do this using SSIS pretty easily (and it comes bundled with SQL Server provided that you have it installed).

You can schedule a package to run periodically using SQL Server Agent.

You will have to drop-create the tables and work on the indices and relationships as well.

Raj More
I just created a dataflow to copy one of the tables. Need to figure out how to have it process a list of tables.Thanks
Iceman
You can use the `Transfer SQL Server Objects Task` for this to handle multiple tables.
Raj More
+1  A: 

There are a slew of options.. You could use SSIS, it should work fine, and is probably the easiest to set up. There's a wizard that will guide you through it.

You could also use a linked server on one end that pointsto the other end. and just run a SQL Insert query from the linked server to the destination server. If you did this you could use SQL Agent to schedule it.

   Insert LinkedServer.Schema.TableName 
   Select * From LocalTableName

There are some third party tools that will do this too. Red Gate has a tool called SQL Compare and Data Compare, that not only detect fifferences between two databases, but can generate the sql scripts to modify the database to make them identical.

Charles Bretana
Thanks for the quick response. I will need to check with the DBA on this when he gets here, but this is also a good option.Thanks
Iceman