Migrate a SQL Server database using SSMS and BCP-ApsaraDB RDS(RDS)-阿里云帮助中心

This topic describes how to migrate data using SQL Server Management Studio (SSMS) and the bulk copy program (BCP) utility. A full migration from an on-premises SQL Server database to an Alibaba Cloud ApsaraDB RDS for SQL Server 2012 instance is used as an example.

Note

We recommend that you use Alibaba Cloud Data Transmission Service (DTS) to migrate SQL Server databases. For more information, see Data migration solutions overview and SQL Server migration guide.

Prerequisite

The destination database host must have enough storage space for the imported data and the growth of the log file. If the database is in the Full recovery model, the total space required is about two to three times the size of the source database. If the destination database is in a self-managed on-premises environment, make sure that the host has enough storage space. If the destination database is an Alibaba Cloud ApsaraDB for SQL Server instance, make sure that you have purchased enough storage space.

Background information

SSMS is an integrated environment that you can use to manage your SQL Server infrastructure. SSMS provides tools to configure, monitor, and manage SQL Server instances. SSMS also provides tools to deploy, monitor, and upgrade data-tier components, such as databases and data warehouses that are used by applications, and to generate queries and scripts.

The BCP utility copies data in bulk between a SQL Server instance and a data file in a user-specified format. You can use the BCP utility to import many new rows into SQL Server tables or export data from tables into data files.

This topic describes how to use SSMS to generate a script that creates the database schema. You can run this script on the destination database. Then, you can use the BCP command line to export and import the data for a full migration. The example migrates the AdventureWorks 2012 database from an on-premises SQL Server 2012 instance to an ApsaraDB RDS for SQL Server 2012 instance.

Scenarios

Schema migration for SQL Server databases.
Full data migration. Incremental data migration is not supported.
Full data migration between on-premises databases, from an on-premises database to an ApsaraDB for SQL Server instance, or between ApsaraDB for SQL Server instances.

Notes

When you create the destination database, make sure that its collation is the same as the source database. Otherwise, the full data migration may fail.
To prevent errors and improve the efficiency of data import during the full data migration, you must disable foreign keys, indexes, and triggers in the destination database before the import. After the import is complete, you must enable the foreign keys, indexes, and triggers.
When the BCP utility imports data into computed columns or timestamp columns, it ignores the values for these columns. SQL Server automatically assigns values to these columns.

Procedure

Open the SSMS client.
Connect to the source database AdventureWorks2012 and the ApsaraDB RDS for SQL Server instance.
Run the following statement to create a user with read and write permissions in the source database.
In the code, testdbo specifies the username and XXXXXXXX specifies the logon password for the user. Before you run the code, replace the parameters with your username and password. If a user with read and write permissions already exists in your database, you can skip this step.
```
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
```
Disable the TCP/IP protocol to disconnect all clients from the source database. This ensures data consistency before and after the migration.
Restart the SQL Server service.

Important
After you disable the TCP/IP protocol, remote applications cannot access the local instance through the TCP/IP port. You must run the BCP utility on the physical server where the instance is located to export data.
Run the following statement to create a database on the ApsaraDB RDS for SQL Server 2012 instance.
```
create database db01 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
```

Run the following code to check the collations of the source and target databases.

-- Check Collation name
SELECT name,collation_name 
FROM sys.databases
WHERE name = 'adventureworks2012'

If the collations are different, run the following statement in the destination database. Replace SQL_Latin1_General_CP1_CI_AS with the collation of the source database. If the collations are the same, you can skip this step.
```
-- change the collate if needed.
USE master;  
GO  
ALTER DATABASE adventureworks2012  
COLLATE SQL_Latin1_General_CP1_CI_AS;
GO
```

Run the following statement in the destination database to create the same user that you created in Step 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

Generate a script from the source database to create the database objects. To do so, perform the following steps:
1. In the source database, click Databases.
2. Right-click AdventureWorks2012.
3. Choose Tasks > Generate Scripts.
4. On the Generate Scripts page, click Next.
5. Select Select specific database objects. Select all objects except Users, and then click Next, as shown in the following figure.
6. On the Set Scripting Options page, click Advanced to configure the following settings:
  - Set Script for Server Version to the version of the destination database, such as SQL Server 2012.
  - Set Script for the database engine edition to Microsoft SQL Server Enterprise Edition.
  - Set Script Object-Level Permissions, Script Owner, and Script USE DATABASE to True.
  - Set Types of data to script to Schema only.
    
    Important
    Do not set this option to Schema and data. If you select this option, a schema file and an INSERT statement file are generated. This method is inefficient.
  - Set all options in Table/View Options to True, as shown in the following figure.
7. Click OK to save the exported script file to the path specified in the File name field.
8. Click Next, click Next again, and click Finish to generate the script.
Run the script file that you created in the previous step on the destination database.

After the script that is used to create the database objects is run, run the following statement. Set the @is_disable BIT parameter to 1 to disable foreign key constraints, indexes, and triggers.

Note

Foreign key constraints can cause the data import to fail. Indexes and triggers can reduce the efficiency of data import. For these reasons, you must disable them.

 USE [adventureworks2012]
 GO
 -- Public variables: need to be initialized by the user.
 DECLARE
     @is_disable BIT = 1    -- 1: Disable 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

Run the following statement on the source and destination databases to query the summary of database objects.

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

After the query returns the results, compare the object information of the source and destination databases. If the results are identical, all database objects are migrated from the source database to the destination database.
Important
- You can use a comparison tool to compare the object summary information. This method makes the comparison easier and helps avoid human error. In this topic, Araxis Merge 2001 v6.0 Professional is used as an example.
- ApsaraDB RDS for SQL Server database names support only lowercase letters, but the source database name may contain uppercase letters. Therefore, you must configure your comparison tool to ignore case differences. For example, in Araxis Merge 2001 v6.0 Professional, go to View > Options and select Ignore differences in character case.

Run the following script on the source database. Before you run the script, modify the following parameters as needed:

source_User: The logon username for the source database.
source_Password: The password for the source database username.
destination_Instance: Replace XXXX with the instance name of the destination database.
destination_Database: The name of the destination database. If you leave this parameter empty, the name of the destination database is the same as the name of the source database.
destination_User: The logon username for the destination database. This username is the same as the username for the source database.
destination_Password: The password for the destination database username.

USE AdventureWorks2012
GO
-- Declare public variables. These need to be initialized by the 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)
;
-- Initialize public variables.
SELECT
  @source_Instance = @@SERVERNAME                -- Source instance name.
  , @source_Database = DB_NAME()                    -- The source database is the current database.
  , @source_User = 'XXX'                            -- The username to connect to the source instance.
  , @source_Passwd = N'XXX'                -- The password for the source instance user.
  , @destination_Instance = N'XXXX.sqlserver.rds.aliyuncs.com,3433'    -- The destination instance name.
  , @destination_Database = N''                        -- The destination database name. If NULL or empty, it is the same as the source database.
  , @destination_User = 'XXX'                        -- The username for the destination instance.
  , @destination_Passwd = N'XXX'            -- The password for the destination instance user.
  , @transfer_table_list = N''                                -- If NULL or empty, all tables are transferred.
  , @batch_Size = 50000                                    -- The batch size for BCP IN. The default is 50000. The value must be between 1 and 50000.
;
-- Private variables. No initialization is needed.
DECLARE
  @transfer_table_list_xml xml
  , @timestamp char(14)
  ;
-- Correct the variables initialized by the 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)

Save the generated BCP_OUT and BCP_IN files to your local computer.
Run the BCP_OUT.bat file that is saved to your computer to generate the data export file for the source database.
After the BCP_OUT.bat file finishes running, double-click the log file BCPOUT_YYYYMMDDHHMMSS.txt to verify that the data was exported successfully. YYYYMMDDHHMMSS represents the timestamp when the file was generated.
Run the BCP_IN.bat file that is saved to your computer to import the data from the source database export file to the remote destination database.

Note
The destination database is on Alibaba Cloud. The BCP_IN.bat file takes longer to run than the BCP_OUT.bat file due to network latency.
After the BCP_IN.bat file finishes running, double-click its log file BCPOUT_YYYYMMDDHHMMSS.txt to verify that the data import was successful.
After you confirm that all data is imported from the source database to the destination database, delete the intermediate temporary files that are exported by the BCP utility in Step 17, as shown in the following figure.

Run the following statement on the source and destination databases to query the total number of records in the tables.

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

Use a comparison tool to compare the total number of records in the source and destination databases. If the data is consistent, all data from the source database is imported into the destination database.

Run the following statement and set the @is_disable BIT parameter to 0 to enable foreign key constraints, indexes, and triggers. This completes the database migration.

 USE [adventureworks2012]
 GO
 -- Public variables: need to be initialized by the user.
 DECLARE
     @is_disable BIT = 0    -- 1: Disable 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