Copy SQL data between two database servers

Question

I have some SQL Server databases on my PC that use Windows security, and some databases on my web site using SQL Security. I have dba authority over both sets of databases. I can connect both database sets in an SSMS session, and I can run queries copying data from the different databases in the local set, and in the web site set. However I can't find a way of writing a query that will copy data from a database in the local set to/from the web set. Can somebody please point me at an article that shows me how to do this please.

Answer 1

One possibility is to set up a linked server on your local PC to the web site (which I assume is an SQL database at a hosting provider):

   EXEC sp_addlinkedserver MYWEBSERVER, '', 'MSOLEDBSQL', 'mywebserver.hoster.com'  
   EXEC sp_addlinkedsrvlogin MYWEBSERVER, 'false', 'YOURMACHINE\USER', 'sqllogin' ´, 'password'  

Now you can run things like:

   INSERT MYWEBSERVER.db.dbo.tbl(....)  
       SELECT ...   
       FROM   localtbl  

At least in theory. Linked servers often mean hassle, not the least if you try to insert data in this direction. It works a little better if you insert into a local table, reading from a remote.

I don't know the specifics in your case, but I suspect that you will not be able to set up a linked server on the SQL Server instance for your web set.

An alternative is to use something that connects to both databases, for instance an SSIS package. The export/import data options in SSMS may also work for you.

Answer 2

Hi @Robert Barnes ,

You can try to use:

Use the Copy Database Wizard

It can copy and move database and certain server objects from one instance of sql server to another instance, you should make sure that SQL Server agent is started on the destination server, and the data and log file directories on the source server can be reached from the destination server

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

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I think I've found the easiest answer: with SSMS, use Tasks/Import to import the data I want from my local database to a new database on the web server, and then use SQL Queries to copy data from the new database into the target database. I need the intermediate step of the new database because the Import doesn't work smoothly when I tried to import into the target database because of duplicate keys, so I need to use SQL to SELECT WHERE key NOT IN (SELECT ...). So far I've found it easy to run queries from one database to another within a database set sharing a connection, but not between databases that have different connections.