Sign in to follow this  

[web] Copy table and values from one SQL 2005 sever to another?

This topic is 3732 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi Everybody! I have a MS SQL Server 2005 running on my developing computer, and I want to copy some new tables I created on my application database to my main production server that's hosted at a data center. I know this procedure is used a lot with application development to move tables values from one server to another. I can copy the table structire, but I will have to manually recreate the table values and I don't want to do that. Any way to use SQL server to copy the values for me? Or what's the best way of doing that?

Share this post


Link to post
Share on other sites
Hi Capn.

Unfortunetely it doesn't have that option. I right click on the DB, and go to Tasks... and I get Detatch, Schrink, Back Up, Restore, and Generate Scripts.

No Export. The closes thing is Generate Scripts, but that creates scripts that only create the tables. It doesn't have an option to add INSERT INTO values of the table.

I have Microsoft SQL Server Management Studio Express so I don't know if that will make any difference.

Anyone know how to export all of the data in an MS SQL server table into a script or XML file or anything?!?

Share this post


Link to post
Share on other sites
i never used the express version before (i believe that is what you have), but i can think of a handful of methods which may or may not be available to you (in order of preference):

1) backup dev and restore to production. this is probably the best way.

2) replication snapshot. if you can manage to set up replication over the internet, it will handle the job. you will still have to manually create all the tables and such on the production server with your scripts, and there might be a few sticky spots depending on which features you use.

3) SSIS. i don't think this comes with express, but if you do have another version this ought to do the trick.

4) if you have a copy of SQLServer 2000, or access to Enterprise Manager, you can create a simple DTS package to migrate the data (the wizard should be able to handle this so you don't actually have to learn how to do it). DTS is what SSIS replaced, but it still works with 2005.

5) create a linked server to dev on the production server, and copy it with T-SQL. linked servers are not very fast and it might be a pain to set up through the internet. seriously, just backup and restore :)

Share this post


Link to post
Share on other sites
Krez, I do have access to Enterprise Manager. I will try that.

The problem with backing up --- I don't want to replace certain tables on the source database because they hold account data that is not present on my development machine.

Share this post


Link to post
Share on other sites
Found SQL reporter by microsoft!

http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en



This tool generates a SQL script to recreate objects in a database. It’s made by MS and it works for me.



I thought you might want to share this with someone else who might benefit.

Share this post


Link to post
Share on other sites

This topic is 3732 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this