SQL 在两个服务器实例之间复制数据

Copy data between two server instances

提问by Mahdi Tahsildari

I want something like :

我想要类似的东西：

insert into server2.database1.table1 select * from server1.database1.table1

insert into server2.database1.table1 select * from server1.database1.table1

both tables are exactly the same.

两个表完全一样。

how can I Copy data between two server instances?

如何在两个服务器实例之间复制数据？

回答by Todd

SQL - Linked Server

SQL - 链接服务器

If both servers are SQL Server, you can set up Linked servers- I would suggest using an SQL account for security there.

如果两台服务器都是 SQL Server，您可以设置链接服务器- 我建议在那里使用 SQL 帐户以确保安全。

Then you can simply perform

然后你可以简单地执行

insert into server2.database1.dbo.table1 
select * from server1.database1.dbo.table1 where col1 = 'X'

If you run the query in SQL Management studio connected to server1, and current database set to database1, you won't need the prefix

如果在连接到 server1 的 SQL Management Studio 中运行查询，并且当前数据库设置为 database1，则不需要前缀

server1.database1.dbo.

Also, the linked server would be configured on server1, to connect to server2 (rather than the other way around).

此外，链接服务器将在 server1 上配置，以连接到 server2（而不是相反）。

If you have the correct OLE DB drivers, this method can also work between different types of RDBMS (ie. non-SQL Server ones).

如果您有正确的 OLE DB 驱动程序，此方法也可以在不同类型的 RDBMS（即非 SQL Server 的）之间工作。

Open Query

打开查询

Note: Beware not to rely on linked servers too much especially for filtering, and for joins across servers, as they require data to be read in full to the originating RDBMS before any conditions can be applied. Many complications can arise from Linked Servers, so read up before you embark, as even version differences might cause headaches.

注意：注意不要过分依赖链接服务器，尤其是对于过滤和跨服务器连接，因为它们需要在应用任何条件之前将数据完整读取到原始 RDBMS。链接服务器可能会引起许多并发症，因此请在开始之前仔细阅读，因为即使是版本差异也可能导致头痛。

I recommend you use the OPENQUERY command for SQL Servers to get around such limitations. Here's an example, but you should find help specific to your needs through further research:

我建议您对 SQL Server 使用 OPENQUERY 命令来绕过这些限制。这是一个示例，但您应该通过进一步研究找到针对您需求的帮助：

insert into server2.database1.dbo.table1 
select * from OPENQUERY(server1, 'select * from database1.dbo.table1 where col1 = ''X''');

The above code is more efficient, filtering the data on the source server (and using available indexes), before pumping the data through, saving bandwidth/time/resources of both the source and destination servers.

上面的代码效率更高，在将数据泵入之前过滤源服务器上的数据（并使用可用索引），从而节省源服务器和目标服务器的带宽/时间/资源。

(Also note the double quote '', is an escape sequence to produce a single quote.)

（另请注意双引号 '' 是产生单引号的转义序列。）

SQL - Temporarily on the same server

SQL - 暂时在同一台服务器上

Would enable (note the underscore):

将启用（注意下划线）：

insert into server2_database1.dbo.table1 
select * from database1.dbo.table1

Still within the SQL query domain. If you can temporarily move the database on server2 to server1, then you won't need the linked server. A rename of the database would appear to be required while co-locating on server1. Achieving such co-location could use various methods, I suggest shrinking database files before proceeding with either:

仍在 SQL 查询域内。如果您可以将 server2 上的数据库临时移动到 server1，那么您将不需要链接服务器。在服务器 1 上共同定位时，似乎需要重命名数据库。实现这种共存可以使用多种方法，我建议在继续之前缩小数据库文件：

1. Backup/Restore- Backup on server2, Restore on server1 (with different name) - perform insert as described above, but without the server1 or server2 prefixes. Then reverse - backup on server1, restore on server2/
2. Detach/Attach- Rename database, Detach on server2, (compress), copy files to server 1, (decompress), attach on server1, perform insert. Then reverse...

1. 备份/恢复- 在 server2 上备份，在 server1 上恢复（具有不同的名称）- 如上所述执行插入，但没有 server1 或 server2 前缀。然后反向 - 在 server1 上备份，在 server2/ 上恢复
2. 分离/附加- 重命名数据库，在 server2 上分离，（压缩），将文件复制到服务器 1，（解压缩），在 server1 上附加，执行插入。然后反...

In either case, SQL Server version could be a barrier. If server1 is of a lower SQL version, then both backup and detach/attach methods will likely fail. This can be worked around by moving the server1 database to server2, which may or may not be more suitible.

在任何一种情况下，SQL Server 版本都可能是一个障碍。如果 server1 的 SQL 版本较低，则备份和分离/附加方法都可能失败。这可以通过将 server1 数据库移动到 server2 来解决，这可能更合适，也可能不合适。

Other Methods

其他方法

May be suitable, non-SQL/TSQL method failing favorable environmental factors for previously mentioned methods. And if you have the correct access (OLE DB Drivers, etc..), this method can also work between different types of RDBMS (ie. non-SQL Server ones), and data-sources (such as XML, flatfiles, Excel Spreadsheets...)

可能是合适的非 SQL/TSQL 方法，但对前面提到的方法没有有利的环境因素。如果您有正确的访问权限（OLE DB 驱动程序等），此方法也可以在不同类型的 RDBMS（即非 SQL Server 的）和数据源（例如 XML、平面文件、Excel 电子表格）之间工作...）

• SSISExplicitly with Business Development Management Studio- direct datapump or using delimited file intermeditary.
• SSISImplicitly through SQL Management Studio, by right clicking the database1 on server1 > Tasks > Export, then completing the wizard. May work direct to server2, or using a flat-file intermeditary.
• .Net Programmingwith SqlBulkInsert(I believe the SSIS datapump uses such an object), I can go into more detail about this, if it interests you.

• SSIS明确地与业务开发管理工作室- 直接数据泵或使用分隔文件中间。
• SSIS隐式通过SQL Management Studio，通过右键单击 server1 上的 database1 > 任务 > 导出，然后完成向导。可以直接在 server2 上工作，也可以使用平面文件中介。
• .Net Programmingwith SqlBulkInsert（我相信 SSIS 数据泵使用这样的对象），如果您感兴趣，我可以更详细地介绍这一点。

Eg. of SQLBulkInsert (psedo-C# code)

例如。SQLBulkInsert（psedo-C# 代码）

SqlConnection c = new SqlConnection("connectionStringForServer1Database1Here");
SqlConnection c2 = new SqlConnection("connectionStringForServer2Database1Here");
c.Open();
SqlCommand cm = new SqlCommand(c);
cm.CommandText = "select * from table1;";
using (SqlDataReader reader = cm.ExecuteReader())
{
    using (SqlBulkInsert bc = new SqlBulkInsert(c))
    {
         c2.Open();
         bc.DestinationTable = "table1";
         bc.WriteToServer(reader);
    }
}

Pretty cool huh? If speed/efficiency is a concern - SqlBulkInsert based approaches (Such as SSIS) are the best.

很酷吧？如果速度/效率是一个问题 - 基于 SqlBulkInsert 的方法（如 SSIS）是最好的。

Update - Modifying the destination table

更新 - 修改目标表

If you need to update the destination table, I recommend that you:

如果您需要更新目标表，我建议您：

1. Write to a staging table on the destination database (a temporary table, or proper table which you truncate before and after process), the latter is preferable. The former may be your only choice if you don't have CREATE TABLE rights. You can perform the transfer using any one of the above options.
2. Run a MERGE INTO command as per your requirements from the staging table to the destination table. This can Insert, Update and Delete as required very efficiently.

1. 写入目标数据库上的临时表（临时表或在处理前后截断的正确表），后者更可取。如果您没有 CREATE TABLE 权限，前者可能是您唯一的选择。您可以使用上述任一选项执行传输。
2. 根据您的要求从临时表到目标表运行 MERGE INTO 命令。这可以非常有效地根据需要插入、更新和删除。

Such a whole process could be enhanced with a sliding window (changes since last checked), only taking recently changed rows in the source an applying to the destination, this complicates the process, so you should at least accomplish the simpler one first. After completing a sliding window version, you could run the full-update one periodically to ensure there are no errors in the sliding window.

可以通过滑动窗口（自上次检查以来的更改）来增强整个过程，仅将源中最近更改的行应用于目标，这会使过程复杂化，因此您至少应该先完成更简单的过程。完成一个滑动窗口版本后，您可以定期运行完整更新版本，以确保滑动窗口中没有错误。

回答by Mark Byers

To copy data between two different servers you have several options:

要在两个不同的服务器之间复制数据，您有多种选择：

• Use linked servers.
• Use the data import export wizard.
• Use a third party tool such as Red Gate SQL Data Compare.

• 使用链接服务器。
• 使用数据导入导出向导。
• 使用第三方工具，例如 Red Gate SQL Data Compare。

回答by Choco Smith

Similar to Todd C# SqlBulkCopy

类似于 Todd C# SqlBulkCopy

Generally this is easier than creating linked servers.

通常，这比创建链接服务器更容易。

Create a unit test and run the below, if you have triggers then be careful and you will need ALTER permissions.

创建一个单元测试并运行下面的，如果你有触发器那么要小心，你将需要 ALTER 权限。

    [Test]
    public void BulkCopy()
    {
        var fromConnectionString = @"fromConnectionString";
        var destinationConnectionString = @"destConnectionString2";

        using (var testConnection = new SqlConnection(fromConnectionString))
        {
            testConnection.Open();
            var command = new SqlCommand("select * from MyTable;", testConnection);
            using (var reader = command.ExecuteReader())
            {
                using (var destinationConnection = new SqlConnection(destinationConnectionString))
                {
                    using (var bc = new SqlBulkCopy(destinationConnection))
                    {
                        destinationConnection.Open();
                        bc.DestinationTableName = "dbo.MyTable";
                        bc.WriteToServer(reader);
                    }
                }
            }
        }
    }
}

回答by Stack User

The best way to do this would be to create a "linked server". And then you can use below statement into your insert statement in order to define your table

最好的方法是创建一个“链接服务器”。然后你可以在你的插入语句中使用下面的语句来定义你的表

 [linkedserver].databasename.dbo.tablename

回答by Fred

On Server A add a linked server (B)

在服务器 A 上添加链接服务器 (B)

http://msdn.microsoft.com/en-us/library/ms188279.aspx

http://msdn.microsoft.com/en-us/library/ms188279.aspx

Then you can transfer data between the two.

然后就可以在两者之间传输数据了。

Export table data from one SQL Server to another

将表数据从一个 SQL Server 导出到另一个

HTH

HTH

回答by sophia korhonen

First You need to add the server Eg. Server 1 and Server 2

首先您需要添加服务器 例如。服务器 1 和服务器 2

sp_addlinkedserver 'Server-2'

sp_addlinkedserver 'Server-2'

then copy your data from that server to your server by using following query

然后使用以下查询将您的数据从该服务器复制到您的服务器

In Server-1 Write

在 Server-1 中写入

select *   INTO  Employee_Master_bkp
FROM [Server-2].[DB_Live].[dbo].[Employee_Master]

回答by Chris Amelinckx

If you need an alternative without using Linked Servers, my favorite option is use the command line BCP utility. With this bulk copy tool, you can export the data to a flat file, copy the file across the network and import it (load it) onto the target server.

如果您需要不使用链接服务器的替代方案，我最喜欢的选项是使用命令行 BCP 实用程序。使用此批量复制工具，您可以将数据导出到平面文件、通过网络复制文件并将其导入（加载）到目标服务器上。

https://docs.microsoft.com/en-us/sql/tools/bcp-utility

https://docs.microsoft.com/en-us/sql/tools/bcp-utility