Jump to content
  • Advertisement
Sign in to follow this  
andreib

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

This topic is 3927 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
Advertisement
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
Couldn't you use the Backup option and restore it on the other server? I'm used to doing something like this with MySQL, so I plead ignorance with SQL Server.

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
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!