Click here to Skip to main content
15,867,835 members
Articles / Database Development / SQL Server
Article

Application to migrate data between different environments using the ADO.NET 2.0 SqlBulkCopy

Rate me:
Please Sign up or sign in to vote.
4.95/5 (26 votes)
30 Nov 20062 min read 151.3K   1.3K   89   55
This article explains how to migrate data between different SQL Server environments.

Introduction

Often need arises to migrate data between different environments for testing or audit purposes. For example, production data can be moved to test environment and this data can be used by testers. This gives a clear idea of how programs behave on actual data volumes. Using this utility, data can be migrated with ease of use. I used SqlBulkCopy of ADO.NET 2.0 for bulk copying data. This managed class provides similar functionality as the BCP utiltity. This program has been used successfully on large volumes of data. It took me approximately 1 hour for loading 8GB of data. More precisely, for loading 1.1 million records, it took 4 minutes and 30 seconds. It supports both SQL 2005 and SQL 2000 Servers. The available features are:

  • Specific tables can be selected for migrating data
  • Deletes existing data in selected tables<o:p>
  • Reports progress as it loads data<o:p>
  • Logs results of load or delete operations for every table<o:p>
  • Can be cancelled in the middle of a process; the application stops loading data after finishing the current table.

Data Migration Screen Shot

Solution walkthrough

This solution contains a C# Windows Forms project. This is a very simple project. A Windows Form does all the work. It uses the BackgroundWorker component to report progress. The source and destination connections are defined in the application configuration file. If any exception happens while deleting or loading data in a table, the application logs the exception and continues to process the next table.

The two main functions in this application are DeleteData and LoadData.

DeleteData

The following tasks are involved in this function:

  • Disable constraints on all tables.
  • Disable triggers on all tables.
  • Delete data from a table (DELETE or TRUNCATE statement used depending on the foreign key constraints on a table). 
  • Log results.
  • Enable constraints on all tables.
  • Enable triggers on all tables.
C#
private void DeleteData()
{
    using (SqlConnection conn = new SqlConnection(strDestConn))
    {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandTimeout = 0;
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            //Disable Constraints on all tables before deleting data
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                cmd.CommandText = "ALTER TABLE [" + 
                    dr["TABLE_SCHEMA"].ToString() + "].[" + 
                    dr["TABLE_NAME"].ToString() + 
                    "] NOCHECK CONSTRAINT ALL";
                cmd.CommandText += " ALTER TABLE [" + 
                    dr["TABLE_SCHEMA"].ToString() + "].[" + 
                    dr["TABLE_NAME"].ToString() + 
                    "] DISABLE TRIGGER ALL";
                cmd.ExecuteNonQuery();
            }

            //Delete data in selected tables and log results
            string strLogFile = "DeleteLog" + 
                   DateTime.Now.ToString("MMddyyyyHHmm") + ".txt";
            using (StreamWriter sw = new StreamWriter(strLogFile, false))
            {
                sw.AutoFlush = true;
                foreach (object dr in lstInclude.Items)
                {
                    try
                    {
                        cmd.CommandText = 
                         "SELECT OBJECTPROPERTY ( object_id('" + 
                         dr.ToString() + "'),'TableHasForeignRef')";
                        int intref = Convert.ToInt32(cmd.ExecuteScalar());
                        if (intref == 1)
                            cmd.CommandText = "DELETE FROM " + dr.ToString();
                        else
                            cmd.CommandText = "TRUNCATE TABLE " + 
                                              dr.ToString();
                        cmd.ExecuteNonQuery();

                        sw.WriteLine("Data deleted successfully from " + 
                                     dr.ToString() + " at " + 
                                     DateTime.Now.ToString());
                        sw.WriteLine();
                    }
                    catch (Exception ex)
                    {
                        sw.WriteLine("Error while deleting data" + 
                                     " in table " + dr.ToString() + 
                                     ".Error is " + ex.Message);
                        sw.WriteLine();
                    }
                }
            }

            //Enable Constraints on all tables
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                cmd.CommandText = "ALTER TABLE [" + 
                    dr["TABLE_SCHEMA"].ToString() + "].[" + 
                    dr["TABLE_NAME"].ToString() + 
                    "] CHECK CONSTRAINT ALL";
                cmd.CommandText += " ALTER TABLE [" + 
                    dr["TABLE_SCHEMA"].ToString() + "].[" + 
                    dr["TABLE_NAME"].ToString() + 
                    "] ENABLE TRIGGER ALL";
                cmd.ExecuteNonQuery();
            }
        }                
    }
}

LoadData

The following tasks are involved in this function:

  • Load data from the source table using a SQL data reader. 
  • Create SqlBulkCopy with KeepIdentity and TableLock options. A lock on the table makes the loading process quicker. By default, SqlBulkCopy disables constraints and triggers on a table while loading data.
  • Map source and table columns. This is necessary if the column ordinal positions are different between the two environments.
  • Eliminate computed columns while mapping columns.
  • Bulk insert data in a table by passing the datareader to SqlBulkCopy.
C#
private void LoadData(string pSourceConn,string pDestConn,string pTable)
{
    using (SqlConnection srcConn = new SqlConnection(pSourceConn))
    {
        string[] strTable = pTable.Split('.');
        //Get data from source table
        srcConn.Open();                
        SqlCommand srcCommand = new SqlCommand("SELECT * FROM " + 
                                pTable, srcConn);
        srcCommand.CommandTimeout = 0;
        SqlDataReader sqldr = srcCommand.ExecuteReader();
        SqlBulkCopy sqlbcp = new SqlBulkCopy(pDestConn,
                             SqlBulkCopyOptions.KeepIdentity | 
                             SqlBulkCopyOptions.TableLock);

        //Map Columns
        SqlConnection destConn = new SqlConnection(pDestConn);
        destConn.Open();
        SqlCommand cmd1 = new SqlCommand("SELECT COLUMN_NAME," + 
                         "COLUMNPROPERTY(OBJECT_ID('" + 
                         strTable[0] +"." +strTable[1]+
                         "'),COLUMN_NAME,'IsComputed')AS 'IsComputed' " + 
                         "FROM INFORMATION_SCHEMA.COLUMNS " + 
                         "WHERE TABLE_SCHEMA = '" + 
                         strTable[0] + "' AND TABLE_NAME = '" + 
                         strTable[1] + "'", destConn);
        SqlDataReader drcolumns = cmd1.ExecuteReader();
        while (drcolumns.Read())
        {
            if(drcolumns.GetInt32(1) != 1)
            sqlbcp.ColumnMappings.Add(drcolumns.GetString(0), 
                                      drcolumns.GetString(0));
        }
        drcolumns.Close();

        //Load Data
        sqlbcp.BulkCopyTimeout = 0;                
        sqlbcp.DestinationTableName = pTable;
        sqlbcp.WriteToServer(sqldr);
        sqldr.Close();
    }
 }

Conclusion

That's all there is to it. I hope this application helps for migrating your data. This can be customized easily if you want to load data from different data sources (e.g.: XML files, flat files...). Thanks for reading. Please provide me feedback about issues or ways to improve.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
United States United States
MCSD.NET Early Achiever,MCDBA SQL 2000,MCITP SQL 2005 Charter Member,MCPD Charter Member

Comments and Discussions

 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 5:54
professionalKanasz Robert24-Sep-12 5:54 
Generalcomputed column and sqlbulkcopy Pin
havejeet8-Aug-09 2:21
havejeet8-Aug-09 2:21 
GeneralRe: computed column and sqlbulkcopy Pin
xx5824-Sep-09 21:56
xx5824-Sep-09 21:56 
GeneralBulk Load Problem Pin
xx5814-Jul-09 22:44
xx5814-Jul-09 22:44 
Generaljust what i needed, and worked nicely. Pin
secretGeek_net11-Jan-09 13:25
secretGeek_net11-Jan-09 13:25 
GeneralGreat Application, Siva Pin
James Bolles (elpaso)11-Sep-08 16:23
James Bolles (elpaso)11-Sep-08 16:23 
GeneralEnforce Constraints [modified] Pin
spoekes9-Jul-08 23:08
spoekes9-Jul-08 23:08 
GeneralBackup and restore Pin
Ramsheer Pc1-Nov-07 20:01
Ramsheer Pc1-Nov-07 20:01 
GeneralNice Article Pin
Hemant.Kamalakar22-Oct-07 1:51
Hemant.Kamalakar22-Oct-07 1:51 
QuestionHow can I use SqlBulkCopy to copy data to the local temporary table which is dynamicly generated in a store procedure? Pin
dddd2181-Oct-07 5:13
dddd2181-Oct-07 5:13 
GeneralCollumn Exists in Dest but not Source. Pin
txghia581-Aug-07 9:46
txghia581-Aug-07 9:46 
GeneralRe: Collumn Exists in Dest but not Source. Pin
siva pinnaka5-Aug-07 5:30
siva pinnaka5-Aug-07 5:30 
GeneralLife Saver Pin
JoeIsTheMan30-Jan-07 12:36
JoeIsTheMan30-Jan-07 12:36 
GeneralRe: Life Saver Pin
siva pinnaka30-Jan-07 17:10
siva pinnaka30-Jan-07 17:10 
GeneralUpdate to article Pin
siva pinnaka1-Dec-06 8:38
siva pinnaka1-Dec-06 8:38 
GeneralRe: Update to article Pin
Jeff Firestone7-Dec-06 5:14
Jeff Firestone7-Dec-06 5:14 
GeneralRe: Update to article Pin
siva pinnaka7-Dec-06 6:52
siva pinnaka7-Dec-06 6:52 
QuestionAlso create tables at destination? Pin
jackmos21-Nov-06 2:50
professionaljackmos21-Nov-06 2:50 
AnswerRe: Also create tables at destination? Pin
siva pinnaka27-Nov-06 10:38
siva pinnaka27-Nov-06 10:38 
QuestionThreads? Pin
xae19-Nov-06 9:45
xae19-Nov-06 9:45 
AnswerRe: Threads? Pin
siva pinnaka20-Nov-06 8:08
siva pinnaka20-Nov-06 8:08 
GeneralVery good... Pin
vinSharp15-Nov-06 2:36
vinSharp15-Nov-06 2:36 
A simple and useful reference material.

There are two types of fools in this world: One who give advice and the others who do not take it...

GeneralRe: Very good... Pin
siva pinnaka15-Nov-06 3:43
siva pinnaka15-Nov-06 3:43 
GeneralGood... Pin
vinSharp15-Nov-06 2:35
vinSharp15-Nov-06 2:35 
GeneralHey Nice Pin
KimZander7-Nov-06 8:12
KimZander7-Nov-06 8:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.