Teach you how to copy data from one SQL Server server to another SQL Server server

Platform description: two sql server2005 servers with two identical databases installed

Recently, due to the needs of the project, it is necessary to copy the data of the database on one server to the database on the other server. After a lot of google, it is finally completed. I will make a detailed record here. I hope it can help everyone and Yourself.

1. The first step: sql add server

First, we need to connect to another server with sql script,

use master
go
/ * Add server * /
EXEC sp_addlinkedserver
@server = 'hang', / * This name can be taken anywhere * /
@srvproduct = 'SQL',
@provider = 'SQLOLEDB',
@datasrc = '10 .130.26.x '/ * Description: IP of another server * /
go
/ * Login to the server * /
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'hang', / * need to be the same name as before * /
@useself = 'false',
@locallogin = 'sa',
@rmtuser = 'sa',
@rmtpassword = 'wlw' / * Login password * /
The above code is the code for sql to connect to sql. For connections between other databases, please refer to: http://msdn.microsoft.com/zh-cn/library/ms190479.aspx
Second, the second step: write script test

What needs to be explained here is that after we add the server with the above code, we will not need to connect again in the future, for example, I want to query:


select * from hang.epark.dbo.parkHistory
/ * hang is the server name I just created, epark is the database on the server, and dbo.parkHistory is the specific table * /
Then we can write the code we need to test first in the query window, whether it can be successfully executed


insert hang.epark.dbo.parkHistory (userNo, cardID, parkingNo_longquan)
select userNo, cardID, parkingNo
from epark.dbo.parkHistory where not exists (select * from hang.epark.dbo.parkHistory where parkingNo_longquan = epark.dbo.parkHistory.parkingNo)
The table's copy statement is used here: insert into () selcet, and not exists (). This not exists is to return the columns that are in one table but not in the other table. For example, my statement, because I need to regularly upload the data updated by parkHistory on one server to the parkHistory on another server, so the data I have uploaded will not be uploaded again, not exists is such a function.
After the code test passes, we can establish a timed task.

Third, the third step: add a scheduled task

 First we open the database in the server object-> connect to the server,


Then right click hang (specifically the database you are connected to, this is what I used to connect with sql statement)-> properties




Originally, there was no NT AUTHORITY \ SYSTEM account. This was added by myself. You also need to add NT AUTHORITY \ SYSTEM to the user mapping. The remote user and remote password are the passwords for another database login, usually sa, or Your own username. Then choose to use this security context to establish a connection below and enter the user name and password; the above operation is mainly to avoid the "already performed as the user NT AUTHORITY / SYSTEM. The user 'NT AUTHORITY / ANONYMOUS LOGON' login failed. [SQLSTATE 28000] (Error 18456). This step failed. "Error

Next, you need to open the sql server agent service: start->>> run->>> enter "services.msc"->>> enter the service, start the SQL Server Agent service, and set to automatic

Next, start a task: http://www.cnblogs.com/IPrograming/archive/2012/03/08/2384776.html

Note: Error handling

In SQL SERVER2005, start the SQL agent service, start normally, but the agent is still disabled in the sql server agent
 xp, execute the following code in the query window:


sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE WITH OVERRIDE;-plus WITH OVERRIDE
GO
sp_configure ‘Agent XPs’, 1;
GO
RECONFIGURE WITH OVERRIDE-plus WITH OVERRIDE
GO
Teach you how to copy data from one sql server server to another sql server server