How to copy data from an SQL server to another SQL server, sqlserver

How to copy data from an SQL server to another SQL server, sqlserver

Platform Description: two SQL server2005 servers are installed with two identical databases.

Recently, due to project requirements, we need to copy the database data on one server to the database on another server. After a lot of google operations, we have finally completed the process. Here we will make a detailed record, hope to help you and yourself.

1. Step 1: add an SQL Server

First, we need to use SQL scripts to connect to another server,

Use mastergo/* Add server */EXEC sp_addmediaserver @ server = 'hang',/* you can use this name as needed */@ srvproduct = 'SQL', @ provider = 'sqloledb ', @ datasrc = '10. 130.26.x '/* Description: IP address of another server */go/* Login Server */EXEC sp_add1_srvlogin @ rmtsrvname = 'hang ', /* the same name as the preceding connection */@ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'sa ', @ rmtpassword = 'www'/* logon password */

The above code is the SQL connection SQL code, the connection between other databases can refer to: http://msdn.microsoft.com/zh-cn/library/ms190479.aspx

Step 2: write a script for testing

It should be noted that after we use the above Code to add the server, we will not need to connect again later. For example, I want to query:

Select * from hang. epark. dbo. parkHistory/* hang is the name of the server 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 in the query window to see if the code 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)

Here we use the copy statement of the Table: insert into () selcet, and not exists (). This not exists means to return the columns that exist in one table but not in another table. For example, because I need to regularly upload the parkHistory update data on one server to parkHistory on another server, the data I have uploaded will not be uploaded, not exists is such a feature.

After the code test is passed, we can create a scheduled task.

Step 3: Add a scheduled task

First, open the database and go to the Server Object> connect to the server,

Right-click hang (specifically, the database you connected to, which was previously connected using SQL statements)-> Properties

Originally, there was no nt authority \ SYSTEM account, which was added by myself. You also need to add nt authority \ SYSTEM to user ing, the remote user and Remote Password Are the passwords used to log on to another database. Generally, you can use sa or your own user name. Next, use the security context to establish a connection and enter the user name and password. The above operation is mainly to avoid "being executed as user nt authority/SYSTEM. User 'nt AUTHORITY/ANONYMOUS logon' Login Failed. [SQLSTATE 28000] (error 18456). This step fails ." Error

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

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

Note: handle errors

In SQL SERVER2005, start the SQL proxy service and start it normally. However, the SQL server proxy still shows that proxy xp has been disabled. In the query window, run the following code:

1. Sp_configure 'show advanced options', 1;
2. GO
3. Reconfigure with override; -- add WITH OVERRIDE
4. GO
5. Sp_configure 'agent XPs ', 1;
6. GO
7. Reconfigure with override -- add WITH OVERRIDE
8. GO

Q: How can I transfer the sqlserver2005 database from one server to another?

This is because the view name and table name are the same, so the data of the view is imported to the table with the same table name by default.

The above is helpful to you!
 
How can I import data from one SQL database to another SQL database on another server?

If two machines are connected, use the stored procedure to write
Create procedure copyab
As
Insert into
Select *
From [ip address of database B]. B. Name of the table to be copied
You can.