SqlServer迁移基础 --生成所迁移数据库所有表的BCP脚本

bcp 实用工具
https://docs.microsoft.com/zh-cn/sql/tools/bcp-utility
在SSMS配合BCP迁移SQL Server数据库上阿里云博文基础完善

改动的地方如下
1：tempdb数据库的排序规则与迁移数据库的排序规则不一致
2：如果表名不规范的情况需要添加>[] >处理

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 = 'localhost',   -- Source Instance Name
       @source_Database = 'AdventureWorks2012',       -- Source Database is current database.
       @source_User = 'sa',                             -- Source Instance Connect User Name
       @source_Passwd = N'123456',                    -- Source Instance User Password
       @destination_Instance = N'127.0.0.1,2433',       -- Destination Instance Name
       @destination_Database = N'AdventureWorks2012', -- Destination Database name: NULL/empty: Keep the same as source db
       @destination_User = 'sa',                        -- Destination Instance User Name
       @destination_Passwd = N'123456',                 -- 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 COLLATE Chinese_PRC_CI_AS FROM #tb_list
                     );

DROP TABLE #tb_list;

保存执行结果中BCP_OUT列所有内容到文件BCPOUT.bat
保存执行结果中BCP_IN列所有内容到文件BCPIN.bat
执行BCPOUT.bat文件
检查BCPOUT.bat执行的日志文件
BCPOUT.bat批处理文件执行后会生成一个日志文件，日志文件的命名格式是：BCPOUT_YYYYMMDDHHMMSS.txt，比如：BCPOUT_20171122233426.txt。
执行BCPIN.bat文件
检查BCPIN.bat执行的日志文件
BCPIN.bat批处理文件执行后会生成一个日志文件，日志文件的命名格式是：BCPIN_YYYYMMDDHHMMSS.txt，比如：BCPIN_20171122233426.txt。
删除BCP导出的中间临时文件