How to transfer data from local server to internet server?

[nguyentaiuyenchi]

- I am performing a live testing for my web application with SQL database. Please show me the way to transfer data from local server to internet server.

Thank in advance,

Uyen Chi

Uyen Chi
Software developer

 

[tlbroadbent]

You can detach the database and copy or FTP the files to the Internet Server running SQL Server. Then attach the database in the new server. Or you can Backup the database on the development server, copy the backup file to the Internet location and Restore on the new server. See the following for more information and some issues you will likely encounter.

INF: Moving SQL Server Databases to a New Location with Detach/Attach

http://support.microsoft.com/default.aspx?kbid=224071

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[nguyentaiuyenchi]

Hi Terry,

Thank you for your answer. However I have no privilege to copy database file to thie internet server. I only have username and password to access database via Enterprise Manager

Therefore I am trying to export data to INSERT INTO statement and I hope anyone have other solution for me?

Thanks,

Uyen Chi

Uyen Chi
Software developer

 

[ChainsawJoe]

Have you considered replication? I'ev only just started getting into it (since I kinda have to at my new job!), but they have a local SQL server and a Live SQL server - then by selecting "tools - replication - create and manage publications" from enterprise manager you can set it all up.

You may have to set up the internet SQL server as a remote server for your local SQL server (within enterprise manager, expand "security", and right click "remote servers" to add a new one - I haven't done this myself, so I can't really help you out there.. but I think it needs to be done.)

Replication would allow you to set up a scheduled job/one off that copies all the data from you local SQL server and pushes it to the live one.

I had a shedload of links to do with it, which I'll dig up and post on here if you think this is a direction you'd like to take?

--------------------------------------------------

http://www.chainsawjoe.com

- better than toast.
Penguins - better than

http://www.chainsawjoe.com

--------------------------------------------------

 

[nguyentaiuyenchi]

Hi ChainsawJoe,

That's a good idea, I heard replication function but I have never done. Please showe me the way to do that?

Cheer,


Uyen Chi
Software developer

 

[ChainsawJoe]

Well, I'm learning it myself right now, but I'd suggest the first thing you do (assuming you haven't already) is go get teh SQL Books Online for your version of SQL Server (which version do you have?) at;

http://www.microsoft.com/sql/techinfo/productdoc/70/default.asp

Download, install and search within it for "configuring replication". Unfortunately, it tends to just tell you to start a replication wizard...

But what I'm assuming needs to be done is this;
You need to either have access to the Live SQL server enterprise manager, and as such can do this;

Expand a server group.
Right-click a server; then click Properties.
Click the Connections tab.
Review, and if appropriate, change the values for these configuration options in Remote server connections:
Allow other SQL Servers to connect remotely to this SQL Server using RPC.
Query time-out (sec) specifies the number of seconds to wait before returning from processing a query. A value of 0 will allow an infinite wait. The default is 0.
Enforce distributed transactions (MTS).
When configuration options are set, click OK.
If you have changed a configuration option, you must stop and restart the server. If so, proceed to Step 5.

Right-click the server; then click Stop.
After the server has stopped, right-click the server, and then click Start.
Repeat Steps 1 through 7 on the other server of the remote server pair.

(aplogies for the cut n paste from sqlbol)

OR - run the sp "SQLDMOSrvOpt_RPC" on the live SQL server to allow it to accept Remote Procedure Calls.

Then you can link to it as a remote server from your local SQL server (expand security - remote servers - add new)

Once you've done that, try just following the replication wizard - you can specify what databases, what tables, what rows, what SPs etc are to be copied to which other server, how, how often, etc...

Dead complex.

But give the wizards a go, and shout here if that doesn't help - I'm sure either I can point you in the right direction, or someone with actual in depth knowledge of this thing can help us both out!!

good luck!

--------------------------------------------------

http://www.chainsawjoe.com

- better than toast.
Penguins - better than

http://www.chainsawjoe.com

--------------------------------------------------

 

[ChainsawJoe]

crap - sorry. "SQLDMOSrvOpt_RPC" isn't an SP at all is it? it's an option to be set via some other method.... sorry!

--------------------------------------------------

http://www.chainsawjoe.com

- better than toast.
Penguins - better than

http://www.chainsawjoe.com

--------------------------------------------------

 

[tlbroadbent]

If I understand correctly you have a database on local server. The same datbase already exists on the Internet server. You want to transfer data to the internet server. Replication is appropriate if you want to keep the two databases synchronized.

However, if you are only looking for a way to transfer data once, you can create a linked server on the local server, provided the local server can connect to the internet server. With a linked server, you can use INSERT INTO statments to move the data to the linked server.

Insert Into LinkedServer.DatabaseName.Owner.TableName
Select * From LocalDatabase.Owner.TableName

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm