[web] Copy table and values from one SQL 2005 sever to another?
Author
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?
open SQL Server Management Studio
right click the database that has the data
select Tasks->Export data
read the wizard windows and do what they say
right click the database that has the data
select Tasks->Export data
read the wizard windows and do what they say
Author
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?!?
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?!?
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.
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 :)
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 :)
Author
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.
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.
Author
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.
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.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
