SQL Toolkit 'Database Copy' c# example

Brian Donahue

Hi all,

A few people have asked for a v5-compatible version of the SQL Toolkit project to essentially create a copy of one database to another database, including the data. The example below should give you a good idea of how to implement this using SQL Compare and Data Compare engines. Note that this example does not use the Packager API like the previous one!

using System;
using RedGate.SQL.Shared; 
using RedGate.SQLDataCompare.Engine;
using RedGate.SQLCompare.Engine;
using System.IO; 
using System.Data.SqlClient;


namespace ConsoleApplication1
{
	/// <summary>
	/// Summary description for Class1.
	/// </summary>
	class Class1
	{
		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		/// 
		[STAThread]
		static void Main(string[] args)
		{
			string serverName="."; 
			string databaseName="WidgetProduction"; 
			string newDatabaseName="SQLToolkitTest"; //create this database and populate it.
				//get the execution block representing the schema 
			RedGate.SQLCompare.Engine.Database database=new RedGate.SQLCompare.Engine.Database(); 
			RedGate.SQLCompare.Engine.Database newDatabase=new RedGate.SQLCompare.Engine.Database();
			RedGate.SQLCompare.Engine.Differences schema=null;
				try
				{
					database.Register(new RedGate.SQLCompare.Engine.ConnectionProperties(serverName, databaseName), RedGate.SQLCompare.Engine.Options.Default); 
				}
				catch (SqlException sqe) 
				{
					Console.WriteLine("Cannot connect to "+databaseName+":"+sqe.Message);
					return;
				}
					schema=database.CompareWith(null, RedGate.SQLCompare.Engine.Options.Default); 
						//select all the differences 

				foreach (RedGate.SQLCompare.Engine.Difference difference in schema) 
                { 
					difference.Selected=true; 
				} 
				RedGate.SQLCompare.Engine.Work work=new RedGate.SQLCompare.Engine.Work(); 
                
					//get the script 
				work.BuildFromDifferences(schema, RedGate.SQLCompare.Engine.Options.Default, true); 
				ExecutionBlock schemaBlock=work.ExecutionBlock; 

					//Create new database
				SqlConnection conn= new SqlConnection("Server="+serverName+";Initial Catalog=master; Integrated Security=SSPI");
				SqlCommand scmd=new SqlCommand("CREATE DATABASE ["+newDatabaseName+"]", conn);
				conn.Open();
				scmd.ExecuteNonQuery();
				conn.Close();
			
					//run sql schema script on new database
					BlockExecutor be=new BlockExecutor();
					be.ExecuteBlock(schemaBlock,serverName,newDatabaseName);
						Console.WriteLine("================================\r\n schema migration script \r\n==============================\r\n");
						Console.WriteLine(schemaBlock.GetString());
					Console.ReadLine();

                     //now get the data script 

				database.RegisterForDataCompare(new RedGate.SQLCompare.Engine.ConnectionProperties(serverName, databaseName), RedGate.SQLCompare.Engine.Options.Default);
				newDatabase.RegisterForDataCompare(new RedGate.SQLCompare.Engine.ConnectionProperties(serverName, newDatabaseName), RedGate.SQLCompare.Engine.Options.Default);
					// Create the mappings between the two databases
				TableMappings mappings = new TableMappings();
				mappings.CreateMappings(database.Tables, newDatabase.Tables);
				using (ComparisonSession session=new ComparisonSession())
				{
				//
				// Remember to set up the session options
				//
				session.Options = mappings.Options;
				session.CompareDatabases(database, newDatabase, mappings);

				// now get the ExecutionBlock containing the SQL
				// we want to run this on WidgetLive so we pass on true as the second parameter
				SqlProvider provider=new SqlProvider();            
				//
				// Also rememeber to set up the provider options
				//
				provider.Options = session.Options;
				ExecutionBlock dataBlock;
				try
				{
					dataBlock = provider.GetMigrationSQL(session, true);
    
					Console.WriteLine("The synchronization SQL contains {0} lines in {1} batches", dataBlock.LineCount, dataBlock.BatchCount);
    
					// if the ExecutionBlock was very large this could cause memory problems, use block.GetFileStream instead
					Console.WriteLine("================================\r\n Data migration script \r\n==============================\r\n");
					Console.WriteLine(dataBlock.GetString());
					Console.ReadLine();
					be.ExecuteBlock(dataBlock, serverName, newDatabaseName);
    
				}
				finally
				{
					dataBlock = provider.Block;
					if (dataBlock != null)
					{
						dataBlock.Dispose();    // dispose of the objects to delete temporary files
					}
				}
			}
			database.Dispose();
			newDatabase.Dispose();
		}
	}
}