I have a table in a database on my development(local workstation) SQL Server 2008 Express that I have added some records to. Now, I would like to deploy this table to the production SQL Server 2000 located on a server. What is the best was to accomplish this? I want to retain the data from the development instance. Both tables have the exact same structure. Can I use the DTS Import/Export wizard from the production SQL Server 2000 through the SQL Server Enterprise Manager? Does it overwrite tables when importing? Will it handle the different versions of SQL Server?
+3
A:
One technique is to create a linked server. Check the msdn link Linked server - msdn
Then you can write a single query to do the Update or whatever that has access to tables on both servers.
Charles Bretana
2009-12-30 19:06:42
I would probably avoid creating a linked server between a dev and production box, at least one that has any kind of write access. Deployment from one environment to another should be scripted out.
Joe
2009-12-30 19:58:00
A:
If you have SqlServer Management Studio (not express). You can use the import and export options to quickly transfer data in and out. This assumes you can connect to the database from your local machine though.
Chris J
2009-12-30 19:13:19
+2
A:
You development work should result in scripts, not in bits in the database. There are many advantages in doing so. When you deploy, you run the developed scripts on the productions database. Now that you let the cat out of the bag, your best option is to use export the table and then import it, see FAQ: How do I import/export data with SQL Express. SQL's own import/export wizard requires SSIS (see Importing and Exporting Data by Using the SQL Server Import and Export Wizard), which is not part of Express editions.
If the table is reasonable small you can use a distributed query (ie. linked server), but that will require MSDTC because it will be an update, and you may need to configure it.
Remus Rusanu
2009-12-30 19:22:48
It's not difficult to generate the appropriate INSERT script out of the live data in the development database.
Joe
2009-12-30 19:58:42
Thanks for steering me in the right direction. As I read your answer the light came on as to how I have been doing this all wrong. I will need to learn a bit of T-SQL but it will be worth it.
DaveB
2009-12-30 21:24:03
+1
A:
Hi there,
You might want to give a try to Volpet's Table Diff:
You can try a fully-functional copy for 30 days.
Please let me know for anything.
Thank you,
Giammarco
Gia
2009-12-30 23:39:38