云数据库 RDS 版
使用SSMS和BCP迁移SQL Server数据库
迁移SQL Server数据库建议您使用阿里云数据传输服务DTS，详情请参见DTS数据迁移方案概览。
除了DTS，您也可以使用其他工具迁移数据库，本文以本地SQL Server数据库到阿里云云数据库SQL Server 2012的数据全量迁移为例，介绍了如何通过使用SQL Server Management Studio（SSMS）和大容量复制程序实用工具（BCP）来迁移SQL Server数据库。
适用场景
SQL Server数据库的结构迁移。
数据的全量迁移，不支持数据的增量迁移。
本地数据库到本地数据库、本地数据库到阿里云云数据库SQL Server和阿里云云数据库SQL Server间的数据全量迁移。
背景信息
SSMS 是用于管理SQL Server基础架构的集成环境，提供用于配置、监视和管理SQL Server实例的工具。 此外，它还提供了用于部署、监视和升级数据层组件（如应用程序使用的数据库和数据仓库）的工具以生成查询和脚本。
BCP 可以在SQL Server实例和用户指定格式的数据文件间大容量复制数据，您可以通过BCP实用工具将大量新行导入SQL Server表，或将表数据导出到数据文件。
所以，本文直接使用SSMS的功能来生成源端数据库对象结构的创建脚本，然后在目标数据库中去执行，进行数据库结构的迁移；再配合使用BCP命令行来进行数据库数据的导出和导入操作，进行数据的全量迁移。下面将介绍如何将本地SQL Server 2012数据库AdventureWorks2012的数据全量迁移到阿里云云数据库SQL Server 2012。
前提条件
目标数据库主机需要有充足的存储空间来存放导入的数据和因此而带来的日志文件增长，两者加起来的空间增长大概是源端数据库大小的2-3倍（如果数据库是Full模式）。如果目标数据库是在本地自建环境，请确保宿主机有足够的存储空间；如果是阿里云云数据库SQL Server，请确保已经购买了充足的存储空间。
注意事项
在创建目标数据库时，要确保目标数据库和源端数据库排序规则的一致性，否则很可能会导致全量数据迁移失败。
为防止数据全量迁移过程报错，需要在目标数据库中禁用外键、索引和触发器，然后再启用，以此来避免错误发生和提高数据导入效率。
BCP导入计算列或时间戳（timestamp）列时，会忽略它们的列值，SQL Server将自动分配该列的值。
操作步骤
视频介绍
文本介绍
打开SSMS客户端。
分别连接源数据库AdventureWorks2012和阿里云云数据库SQL Server。
执行如下代码，在源数据库创建具有读写权限的用户。代码中的testdbo是指用户名称，XXXXXXXX是该用户的登录密码，在执行代码前请将这两个参数改成您想要的用户名和密码。若您的数据库中已存在具有读写权限的用户，请跳过此步骤。
- USE MASTER
- GO
- CREATE LOGIN testdbo
- WITH PASSWORD = N'XXXXXXXX',CHECK_POLICY = OFF
- GO
- USE AdventureWorks2012
- GO
- CREATE USER testdbo FOR LOGIN testdbo;
- EXEC sys.sp_addrolemember 'db_owner','testdbo'
- GO
禁用掉TCP/IP协议，以断开源数据库的所有客户端连接，以确保源端数据迁移前后的一致性。
重启SQL Server服务。
注意：禁用掉TCP/IP协议后，远端应用程序将无法通过TCP/IP端口来访问本地实例，在使用BCP进行数据导出时，必须在该实例所在的物理机上进行。
执行如下命令，在阿里云云数据库SQL Server 2012的实例上创建数据库。
- create database db01 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
执行如下代码，分别查看源端数据库和目标数据库的排序规则。
- -- Check Collation name
- SELECT name,collation_name
- FROM sys.databases
- WHERE name = 'adventureworks2012'
若结果显示的排序规则不同，在目标数据库执行如下代码，将SQL_Latin1_General_CP1_CI_AS替换成跟跟源端数据库一致的排序规则。若排序规则一致，请跳过此步骤。
- -- change the collate if need.
- USE master;
- GO
- ALTER DATABASE adventureworks2012
- COLLATE SQL_Latin1_General_CP1_CI_AS;
- GO
在目标数据库中执行如下代码，创建与步骤3中一致的源端数据库用户。
- USE MASTER
- GO
- CREATE LOGIN testdbo
- WITH PASSWORD = N'XXXXXXXX',CHECK_POLICY = OFF
- GO
- USE AdventureWorks2012
- GO
- CREATE USER testdbo FOR LOGIN testdbo;
- EXEC sys.sp_addrolemember 'db_owner','testdbo'
- GO
在源端数据库生成对象信息创建脚本。操作步骤如下：
单击源数据库中的Databases。
右击AdventureWorks2012。
选择Tasks > Generate Scripts，如下图所示。
进入生成脚本的界面后，单击 Next。
选中Select specific database objects，选择除Users以外所有的迁移对象，然后单击Next。如下图所示。
在Set Scripting Options页面，单击Advanced，然后进行如下设置：
单击Script for Server Version，将其值设成与目标数据库一致的数据库版本，本示例中使用的是SQL Server 2012.
单击Script for the database engine edition，将其值设成 Microsoft SQL Server Enterprise Edition。
将Script Object-Level Permissions、Script Owner 和 Script USE DATAABASE的值设成True。
单击Types of data to script，将其值设成Schema only。
注意：该选项不能设成Schema and data，否则会生成schema文件和INSERT语句文件，造成效率低下。
建议将Table/View Options中的所有选项值都设置成True。
详情如下图所示：
单击OK。导出的脚本文件会放在页面上显示的File name中所示的位置。
单击Next、Next、Finish，完成脚本生成工作。
在目标数据库中执行在上一步中创建的脚本文件。
当创建对象信息的脚本文件执行完毕后，执行如下代码，并将参数@is_disable BIT的值设成1，以禁用外键约束、索引和触发器。
说明：表外键约束的存在会导致数据导入失败，而表索引和触发器的存在会导致数据导入效率降低，所以需要将其禁用。
- USE [adventureworks2012]
- GO
- --public variables: need init by users.
- DECLARE
- @is_disable BIT = 1 -- 1: disalbe indexes, foreign keys and triggers;
- -- 0: enable indexes, foreign keys and triggers;
- ;
- --================ Private variables
- DECLARE
- @sql NVARCHAR(MAX)
- , @sql_index NVARCHAR(MAX)
- , @tb_schema SYSNAME
- , @tb_object_name SYSNAME
- , @tr_schema SYSNAME
- , @tr_object_name SYSNAME
- , @ix_name SYSNAME
- ;
- --================= Disable/Enable indexes on all tables
- DECLARE
- cur_indexes CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
- FOR
- SELECT
- ix_name = ix.name
- , tb_schema = SCHEMA_NAME(obj.schema_id)
- , tb_object_name = obj.name
- FROM sys.indexes as ix
- INNER JOIN sys.objects as obj
- ON ix.object_id = obj.object_id
- WHERE ix.type >= 2
- AND obj.is_ms_shipped = 0
- AND ix.is_disabled = (1 - @is_disable)
- OPEN cur_indexes;
- FETCH NEXT FROM cur_indexes INTO @ix_name, @tb_schema, @tb_object_name;
- WHILE @@FETCH_STATUS = 0
- BEGIN
- SET
- @sql_index = N'ALTER INDEX ' + QUOTENAME(@ix_name)
- + N' ON ' + QUOTENAME(@tb_schema) + N'.' + QUOTENAME(@tb_object_name)
- + CASE @is_disable
- WHEN 1 THEN N' DISABLE;'
- WHEN 0 THEN N' REBUILD; '
- ELSE N''
- END;
- RAISERROR(N'%s', 10, 1, @sql_index) WITH NOWAIT;
- EXEC sys.sp_executesql @sql_index
- FETCH NEXT FROM cur_indexes INTO @ix_name, @tb_schema, @tb_object_name;
- END
- CLOSE cur_indexes;
- DEALLOCATE cur_indexes;
- --================= Disable/Enable foreign keys on all tables
- --disable
- IF @is_disable = 1
- BEGIN
- SELECT
- @sql = N'
- RAISERROR(N''ALTER TABLE ? NOCHECK CONSTRAINT ALL;'', 10, 1) WITH NOWAIT
- ALTER TABLE ? NOCHECK CONSTRAINT ALL;'
- ;
- END
- ELSE --enable
- BEGIN
- SELECT
- @sql = N'
- RAISERROR(N''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL;'', 10, 1) WITH NOWAIT
- ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL;'
- ;
- END
- EXEC sys.sp_MSforeachtable @sql
- --================= Disable/Enable triggers on all tables
- DECLARE
- cur_triggers CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
- FOR
- SELECT
- tb_schema = SCHEMA_NAME(tb.schema_id)
- ,tb_object_name = tb.name
- ,tr_schema = SCHEMA_NAME(obj.schema_id)
- ,tr_object_name = obj.name
- FROM sys.objects as obj
- INNER JOIN sys.tables as tb
- ON obj.parent_object_id = tb.object_id
- INNER JOIN sys.triggers as tr
- ON obj.object_id = tr.object_id
- WHERE obj.type = 'TR'
- AND obj.is_ms_shipped = 0
- AND tr.is_disabled = (1 - @is_disable)
- ORDER BY tb_schema, tb_object_name
- OPEN cur_triggers;
- FETCH NEXT FROM cur_triggers INTO @tb_schema, @tb_object_name, @tr_schema, @tr_object_name;
- WHILE @@FETCH_STATUS = 0
- BEGIN
- SET @sql = CASE @is_disable
- WHEN 1 THEN N'DISABLE TRIGGER '
- WHEN 0 THEN N'ENABLE TRIGGER '
- ELSE N''
- END
- + QUOTENAME(@tr_schema) + N'.' + QUOTENAME(@tr_object_name)
- + N' ON '
- + QUOTENAME(@tb_schema) + N'.' + QUOTENAME(@tb_object_name)
- ;
- RAISERROR(N'%s', 10, 1, @sql) WITH NOWAIT;
- EXEC sys.sp_executesql @sql;
- FETCH NEXT FROM cur_triggers INTO @tb_schema, @tb_object_name, @tr_schema, @tr_object_name;
- END
- CLOSE cur_triggers;
- DEALLOCATE cur_triggers;
- GO
在源端数据库和目标数据库中分别执行如下代码，查询数据库的对象汇总信息。
- USE AdventureWorks2012
- GO
- ;WITH objs
- AS(
- -- check objects
- SELECT
- database_name = LOWER(DB_NAME())
- , object_type = type
- , objet_desc = type_desc
- , object_count = COUNT(1)
- FROM sys.all_objects WITH(NOLOCK)
- WHERE is_ms_shipped = 0
- GROUP BY type,type_desc
- UNION ALL
- --check indexes
- SELECT
- database_name = LOWER(DB_NAME())
- , object_type = CAST(ix.type AS VARCHAR)
- , objet_desc = ix.type_desc
- , object_count = COUNT(1)
- FROM sys.indexes as ix
- INNER JOIN sys.objects as obj
- ON ix.object_id = obj.object_id
- WHERE obj.is_ms_shipped = 0
- GROUP BY ix.type,ix.type_desc
- )
- SELECT * FROM objs
- ORDER BY object_type
查询结果返回后，对比源端数据库和目标数据库的对象信息。若结果显示一致，则说明所有对象信息已经从源数据库迁移到了目标数据库。
注意：
为方便对比和避免人眼观察带来的人为错误，建议您使用对比工具来对比对象汇总信息。本文推荐您使用Araxis Merge 2001 v6.0 Professional。
阿里云云数据库SQL Server数据库的名称仅支持小写字母，而源数据库名称可能含有大写字母，在使用工具进行对比时，请将其设置为忽略字母大小写的检查。若您使用的是Araxis Merge 2001 v6.0 Professional，可以通过选择View > Options，然后再选中Ignore differences in character case来设置。
在源端数据库上执行如下脚本，执行前请按实际情况修改如下参数：
source_User：源数据库中的登录用户名。
source_Password：登录源数据库的用户名所对应的密码。
destination_Instance：将 XXXX 改成目标数据库的实例名称。
destination_Database：目标端的数据库名称。若为空，则表示与源端数据库保持一致。
destination_User：目标数据库中登录的用户名，与源端数据库一致。
destination_Password：登录目标数据库中的用户名所对应的密码。
- USE AdventureWorks2012
- GO
- -- declare public variables, need to init by user
- DECLARE
- @source_Instance sysname
- , @source_Database sysname
- , @source_User sysname
- , @source_Passwd sysname
- , @destination_Instance sysname
- , @destination_Database sysname
- , @destination_User sysname
- , @destination_Passwd sysname
- , @batch_Size int
- , @transfer_table_list nvarchar(max)
- ;
- -- Public variables init.
- SELECT
- @source_Instance = @@SERVERNAME -- Source Instance Name
- , @source_Database = DB_NAME() -- Source Database is current database.
- , @source_User = 'XXX' -- Source Instance Connect User Name
- , @source_Passwd = N'XXX' -- Source Instance User Password
- , @destination_Instance = N'XXXX.sqlserver.rds.aliyuncs.com,3433' -- Destination Instance Name
- , @destination_Database = N'' -- Destination Database name: NULL/empty: Keep the same as source db
- , @destination_User = 'XXX' -- Destination Instance User Name
- , @destination_Passwd = N'XXX' -- Destination Instance User Password
- , @transfer_table_list = N'' --NULL/empty: ALL Tables are needed to be transfered.
- , @batch_Size = 50000 -- BCP IN Batch Size, by default, it is 50000. Must between 1 and 50000.
- ;
- -- Private variables, there is no need to init.
- DECLARE
- @transfer_table_list_xml xml
- , @timestamp char(14)
- ;
- -- correct the variables init by user.
- SELECT
- @source_Instance = RTRIM( LTRIM(@source_Instance) )
- , @source_User = RTRIM( LTRIM( @source_User ) )
- , @source_Passwd = RTRIM( LTRIM( @source_Passwd ) )
- , @destination_Instance = RTRIM( LTRIM( @destination_Instance ) )
- , @destination_Database = CASE
- WHEN ISNULL(@destination_Database, N'') = N'' THEN @source_Database
- ELSE @destination_Database
- END
- , @destination_User = RTRIM( LTRIM( @destination_User ) )
- , @destination_Passwd = RTRIM( LTRIM( @destination_Passwd ) )
- , @batch_Size = CASE
- WHEN (@batch_Size>0 AND @batch_Size<=50000) THEN @batch_Size
- ELSE 50000
- END
- , @transfer_table_list_xml = '<V><![CDATA[' + REPLACE(
- REPLACE(
- REPLACE(
- @transfer_table_list,CHAR(10),']]></V><V><![CDATA['
- ),',',']]></V><V><![CDATA['
- ),CHAR(13),']]></V><V><![CDATA['
- ) + ']]></V>'
- , @timestamp =
- REPLACE(
- REPLACE(
- REPLACE(
- CONVERT(CHAR(19), GETDATE(), 120), N'-', '')
- , N':', N'')
- , CHAR(32), N'')
- ;
- IF OBJECT_ID('tempdb..#tb_list', 'U') IS NOT NULL
- DROP TABLE #tb_list
- CREATE TABLE #tb_list(
- RowID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
- ,Table_name SYSNAME NOT NULL
- )
- IF ISNULL(@transfer_table_list, '') = ''
- BEGIN
- INSERT INTO #tb_list
- SELECT name
- FROM sys.tables AS tb
- WHERE tb.is_ms_shipped = 0
- END
- ELSE
- BEGIN
- INSERT INTO #tb_list
- SELECT table_name = T.C.value('(./text())[1]','sysname')
- FROM @transfer_table_list_xml.nodes('./V') AS T(C)
- WHERE T.C.value('(./text())[1]','sysname') IS NOT NULL
- END
- ;
- SELECT
- BCP_OUT = N'BCP ' + @source_Database + '.' + sch.name + '.' + tb.name
- + N' Out '
- + QUOTENAME( REPLACE(@source_Instance, N'\', N'_')+ '.' + @source_Database + '.' + sch.name + '.' + tb.name, '"')
- + N' /N /U ' + @source_User +N' /P ' + @source_Passwd +N' /S ' + @source_Instance
- + N' >> BCPOUT_' + @timestamp +N'.txt'
- ,BCP_IN = N'BCP ' + @destination_Database + '.' + sch.name + '.' + tb.name
- + N' In '
- + QUOTENAME( REPLACE(@source_Instance, N'\', N'_')+ '.' + @source_Database + '.' + sch.name + '.' + tb.name, '"')
- + N' /N /E /q /k /U ' + @destination_User + N' /P ' + @destination_Passwd + N' /b '
- + CAST(@batch_Size as varchar) + N' /S ' + @destination_Instance
- + N' >> BCPIN_' + @timestamp + N'.txt'
- --,*
- FROM sys.tables as tb
- LEFT JOIN sys.schemas as sch
- ON tb.schema_id = sch.schema_id
- WHERE tb.is_ms_shipped = 0
- AND tb.name IN (SELECT Table_name FROM #tb_list)
分别将生成的BCP_OUT和BCP_IN文件保存至本地。
运行本地保存的BCP_OUT.bat文件，生成源数据库的数据导出文件。
BCP_OUT.bat文件运行结束后，双击其日志文件BCPOUT_YYYYMMDDHHMMSS.txt（YYYYMMDDHHMMSS为文件生成时间），查看数据导出过程是否全部成功。
运行本地保存的BCP_IN.bat文件，将从源数据库中导出的文件从本地导入到远端目标数据库中。
说明：由于目标数据库是在阿里云上，由于网络原因，BCP_IN.bat文件的运行时间会比BCP_OUT.bat文件的运行时间长。
BCP_IN.bat文件运行结束后，双击其日志文件BCPOUT_YYYYMMDDHHMMSS.txt，查看数据导入过程是否全部成功。
若确保数据已经全部从源端数据库导入到目标数据库中，删除磁盘上BCP在步骤 17中导出的中间临时文件，如下图所示。
分别在源端数据库和目标数据库中执行如下代码，查询源端数据库和目标数据库中的表记录总数。
- USE AdventureWorks2012
- GO
- SELECT
- schema_name = SCHEMA_NAME(tb.schema_id)
- ,table_name = OBJECT_NAME(tb.object_id)
- ,row_count = SUM(CASE WHEN ps.index_id < 2 THEN ps.row_count ELSE 0 END)
- FROM sys.dm_db_partition_stats as ps WITH(NOLOCK)
- INNER JOIN sys.tables as tb WITH(NOLOCK)
- ON ps.object_id = tb.object_id
- WHERE tb.is_ms_shipped = 0
- GROUP BY tb.object_id,tb.schema_id
- ORDER BY schema_name,table_name
使用对比工具对比源端数据库和目标数据库中的表记录总数。若数据一致，则说明源数据库的所有数据已经全部导入目标数据库中。
执行如下代码，并将参数@is_disable BIT的值设成0，启用外键约束、索引和触发器，完成数据库迁移。
- USE [adventureworks2012]
- GO
- --public variables: need init by users.
- DECLARE
- @is_disable BIT = 0 -- 1: disalbe indexes, foreign keys and triggers;
- -- 0: enable indexes, foreign keys and triggers;
- ;
- --================ Private variables
- DECLARE
- @sql NVARCHAR(MAX)
- , @sql_index NVARCHAR(MAX)
- , @tb_schema SYSNAME
- , @tb_object_name SYSNAME
- , @tr_schema SYSNAME
- , @tr_object_name SYSNAME
- , @ix_name SYSNAME
- ;
- --================= Disable/Enable indexes on all tables
- DECLARE
- cur_indexes CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
- FOR
- SELECT
- ix_name = ix.name
- , tb_schema = SCHEMA_NAME(obj.schema_id)
- , tb_object_name = obj.name
- FROM sys.indexes as ix
- INNER JOIN sys.objects as obj
- ON ix.object_id = obj.object_id
- WHERE ix.type >= 2
- AND obj.is_ms_shipped = 0
- AND ix.is_disabled = (1 - @is_disable)
- OPEN cur_indexes;
- FETCH NEXT FROM cur_indexes INTO @ix_name, @tb_schema, @tb_object_name;
- WHILE @@FETCH_STATUS = 0
- BEGIN
- SET
- @sql_index = N'ALTER INDEX ' + QUOTENAME(@ix_name)
- + N' ON ' + QUOTENAME(@tb_schema) + N'.' + QUOTENAME(@tb_object_name)
- + CASE @is_disable
- WHEN 1 THEN N' DISABLE;'
- WHEN 0 THEN N' REBUILD; '
- ELSE N''
- END;
- RAISERROR(N'%s', 10, 1, @sql_index) WITH NOWAIT;
- EXEC sys.sp_executesql @sql_index
- FETCH NEXT FROM cur_indexes INTO @ix_name, @tb_schema, @tb_object_name;
- END
- CLOSE cur_indexes;
- DEALLOCATE cur_indexes;
- --================= Disable/Enable foreign keys on all tables
- --disable
- IF @is_disable = 1
- BEGIN
- SELECT
- @sql = N'
- RAISERROR(N''ALTER TABLE ? NOCHECK CONSTRAINT ALL;'', 10, 1) WITH NOWAIT
- ALTER TABLE ? NOCHECK CONSTRAINT ALL;'
- ;
- END
- ELSE --enable
- BEGIN
- SELECT
- @sql = N'
- RAISERROR(N''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL;'', 10, 1) WITH NOWAIT
- ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL;'
- ;
- END
- EXEC sys.sp_MSforeachtable @sql
- --================= Disable/Enable triggers on all tables
- DECLARE
- cur_triggers CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
- FOR
- SELECT
- tb_schema = SCHEMA_NAME(tb.schema_id)
- ,tb_object_name = tb.name
- ,tr_schema = SCHEMA_NAME(obj.schema_id)
- ,tr_object_name = obj.name
- FROM sys.objects as obj
- INNER JOIN sys.tables as tb
- ON obj.parent_object_id = tb.object_id
- INNER JOIN sys.triggers as tr
- ON obj.object_id = tr.object_id
- WHERE obj.type = 'TR'
- AND obj.is_ms_shipped = 0
- AND tr.is_disabled = (1 - @is_disable)
- ORDER BY tb_schema, tb_object_name
- OPEN cur_triggers;
- FETCH NEXT FROM cur_triggers INTO @tb_schema, @tb_object_name, @tr_schema, @tr_object_name;
- WHILE @@FETCH_STATUS = 0
- BEGIN
- SET @sql = CASE @is_disable
- WHEN 1 THEN N'DISABLE TRIGGER '
- WHEN 0 THEN N'ENABLE TRIGGER '
- ELSE N''
- END
- + QUOTENAME(@tr_schema) + N'.' + QUOTENAME(@tr_object_name)
- + N' ON '
- + QUOTENAME(@tb_schema) + N'.' + QUOTENAME(@tb_object_name)
- ;
- RAISERROR(N'%s', 10, 1, @sql) WITH NOWAIT;
- EXEC sys.sp_executesql @sql;
- FETCH NEXT FROM cur_triggers INTO @tb_schema, @tb_object_name, @tr_schema, @tr_object_name;
- END
- CLOSE cur_triggers;
- DEALLOCATE cur_triggers;
- GO
