不同版本的SQL Server之间数据导出导入的方法及性能比较

wss1051

　　工作中有段时间常常涉及到不同版本的数据库间导出导入数据的问题，索性整理一下，并简单比较下性能，有所遗漏的方法也欢迎讨论、补充。
　　00.建立测试环境
　　01.使用SQL Server Import and Export Tool
　　02.使用Generate Scripts
　　03.使用BCP
　　04.使用SqlBulkCopy
　　05.使用Linked Server进行数据迁移
　　06.使用RedGate的SQL Data Compare
　　07.结果对比
　　可以先看下测试的结果

00.建立测试环境
　　建立一个测试的环境，一个数据源数据库，版本为SQL Server 2008，一个目标数据库，版本为SQL Server 2000。
　　实验环境如下图所示，源数据库使用语句生成了100万的测试数据。

　　


建立测试表并生成100万的测试数据

1 IF OBJECT_ID('DEMOTABLE') IS NOT NULL
2     DROP TABLE DEMOTABLE
3 GO
4 CREATE TABLE DEMOTABLE
5     (
6       COL1 VARCHAR(50) ,
7       COL2 VARCHAR(50) ,
8       COL3 VARCHAR(50)
9     )
10 INSERT  INTO DEMOTABLE
11         SELECT TOP 1000000
12                 NEWID() ,
13                 NEWID() ,
14                 NEWID()
15         FROM    MASTER..SPT_VALUES T1
16                 INNER JOIN MASTER..SPT_VALUES T2 ON 1 = 1
17                 INNER JOIN MASTER..SPT_VALUES T3 ON 1 = 1 01.使用SQL Server Import and Export Tool
　　使用SQL Server Import and Export Tool进行数据的导出，也可以在目标数据库端使用Import进行导入，这部分套件也是SSIS的一部分。
　　在源数据库上右键，选择Task -> Export Data

　　分别填写源数据库和目标数据库的连接信息。

　　

　　
　　选择“copy data from one or more tables or views”
　　选择需要导数据的表，并且可以编辑列的Mapping关系。

　　可以选择立即执行或者存储为SSIS的包，用于执行计划等其他用途。
　　这里我们选择立即执行。

　　注意导入的时候如果遇到如下的错误
　　Error 0xc02020f4: Data Flow Task: The column "Tel" cannot be processed because more than one code page (936 and 1252) are specified for it.
(SQL Server Import and Export Wizard)
　　是因为两边的数据库的Collation设置不一样造成的，需要设置同样的Collation。

• 用时约1分30秒
  

02.使用Generate Scripts生成脚本
　　在源数据库上右键，选择Task -> Geneate Scripts...
　　
　　配置相关信息，注意选择数据库的版本并将Script Data设置成True。
　　
　　这里需要注意，因为有100万的数据，所以导出的SQL文件就有400多M，所以用SQL Server Management Studio是打不开的。
　　所以只能使用sqlcmd执行。

sqlcmd语句

1 C:\>sqlcmd -i export.sql -d ExportDataDemo_Destination -s 192.168.21.165 -U sa -P 1234567890

• 用时约28分钟
  

03.使用BCP进行导出导入
　　在尝试了前面两个效率低下的工具之后，我们终于开始尝试下SQL Server中专门用于导数据的工具：BCP。
　　关于BCP的详细用法可以参见MSDN的帮助文档。
　　我们先使用BCP导出数据。

　　-U和-P后面分别为数据库的用户名和密码。

　　我们可以看到100万的数据导出仅用了1.8秒。
　　现在我们再使用BCP进行导入。

　　执行后发现，导入数据使用了20.8秒，还是很快的。

• 用时1.872秒+20.810秒=22.682秒
  

04.使用SqlBulkCopy
　　.NET Framework 2.0中增加的SqlBulkCopy类可以进行高效的数据迁移动作，这也为代码实现数据迁移提供了接口。
　　并且SqlBulkCopy类提供了修改字段Mapping关系的方法ColumnMappings。

使用SqlBulkCopy类进行数据迁移

1 using System;
2 using System.Data;
3 using System.Data.SqlClient;
4
5 namespace BulkInsert
6 {
7     static class Program
8     {
9         static void Main()
10         {
11             DateTime dateTimeStart = DateTime.Now;
12             Console.WriteLine("Start Insert:" + dateTimeStart.ToString("HH:mm:ss fff"));
13             //导入导出的数据库连接
14             SqlConnection connectionDestination = new SqlConnection("Server =.; User ID=datascan; Password=DTSbsd7188228; Initial CataLog=ExportDataDemo_Destination;");
15             SqlConnection connectionSource = new SqlConnection("Server =.; User ID=datascan; Password=DTSbsd7188228; Initial CataLog=ExportDataDemo_Source;");
16
17             //实例化一个SqlBulkCopy
18             var bulker = new SqlBulkCopy(connectionDestination) { DestinationTableName = "DEMOTABLE", BulkCopyTimeout = 600 };
19
20             //获取源数据库的数据
21             SqlCommand sqlcmd = new SqlCommand("SELECT * FROM DEMOTABLE", connectionSource);
22             SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlcmd);
23             DataTable dataTableSource = new DataTable();
24             sqlDataAdapter.Fill(dataTableSource);
25
26             //可以重新定义字段的Mapping关系
27             //SqlBulkCopyColumnMapping sqlBulkCopyColumnMapping = new SqlBulkCopyColumnMapping("COL1", "NEW_COL1");
28             //bulker.ColumnMappings.Add(sqlBulkCopyColumnMapping);
29             connectionDestination.Open();
30             bulker.WriteToServer(dataTableSource);
31             bulker.Close();
32             DateTime dateTimeEnd = DateTime.Now;
33             Console.WriteLine("Insert Ending:" + dateTimeEnd.ToString("HH:mm:ss fff"));
34         }
35     }
36 }　　执行后
　　

• 用时14.8秒
  

05.使用Linked Server进行数据迁移
　　先在源数据库上对目标数据库建立Linked Server，或者反过来也行。

建立Linked Server

1 EXEC sp_addlinkedserver @server = 'LinkedServerToDemo',
2     @srvproduct = 'Export Data Testing', @provider = 'MSDASQL',
3     @provstr = 'DRIVER={SQL Server};SERVER=192.168.21.165;UID=sa;PWD=password;'　　

是用INSERT INTO...SELECT...进行导入

1 DECLARE @begin_date DATETIME
2 DECLARE @end_date DATETIME
3 SELECT  @begin_date = GETDATE()
4
5 INSERT  INTO LinkedServerToDemo.ExportDataDemo_Destination.dbo.DEMOTABLE
6         SELECT  *
7         FROM    ExportDataDemo_Source.dbo.DEMOTABLE
8         
9 SELECT  @end_date = GETDATE()
10 SELECT  DATEDIFF(ms, @begin_date, @end_date) AS '用时/毫秒' 　　执行用时

• 　　用时7.97分钟
  

06.使用RedGate的SQL Data Compare进行数据迁移
　　第三方的工具，有数据库结构比较的工具SQL Compare和数据比较工具SQL Data Compare。

　　执行

　　因为也是生成INSERT的SQL执行的，所以就不做过多比较了，上面已经测试过了。
07.结果对比
　　因为这里测试的环境有网络和表结构的特殊情况，不能说明所有情况下效能的差异，但是也可作为参考之用。
　　下面给出比较结果。