0 ratings0% found this document useful (0 votes) 87 views14 pagesSQL Server Database Cloning Tool Using C#
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content,
claim it here.
Available Formats
Download as PDF or read online on Scribd
SQL Server Database Cloning Tool using C#
Il codeproject com/Ariles/994806/SO1-Server-Database-Cloning-Toobusing-Csharp
25 May 2015
This tool helps in cloning/copying the database from a souree environment/server to a target server
(usually developers can use it for setting up local DB with production code)
Introduction
Thave worked on a couple of big projects that deal with SQL Server as a backend system. As a
developer I know the importance of local database. We will have full privileges to play with, run
debuggers, profilers and tuning advisers on it, During the initial days of development, it is very easy to
take a backup from any of the higher environments and restore it in local. But once it goes to
production the size of database becomes huge and it is difficult to restore it in local (our local machines
doesn't have terabytes of hard disk right -))
So how to deal with this situation?” How to syne our local database with latest production code base
without struggling with size constraint
There are a couple of tools that help us achieve this, but I thought of creating my own tool. After
googling for some time, I found a way to copy the database using SMO (SQL Server Management
Objects) API
Let's see how this tool works and it's implementation,
Using the Tool
Using this tool is so simple, Once you install this tool using attached click once deployment setup file
you will see "SqlDbCloner" short cut on you desktop. Double click on it to run the tool. The first
window will facilitate the end user to provide the source and target DB connection strings.
Then select the type of activity:
1. Copy Schema
2. Copy DataHi SqlDbCloner
‘Scurce Connection Sting
a ea ea eerie Satie”
Destinator Connection Stang
1. Copy Schema
Click on Next.
Then it will pull the list of SQL objects from source connection and provides the end user to select the
required objects to copy to target DB.
Bottom of the same window, there are two options
1. Copy keys and Indexes: Allows to copy all primary keys, indexes, foreign keys and check
constraints from source to target.
2. Drop if exists: This overrites the target object if exists
BA Sq1D8Cloner
Selec the at of SQL objects to ceoy
(v Table
EeW StoredProcedure
| Gop) keysardindoes
| ©] Dopit exists
Click on Next. You will be shown a preview of all selected objectBa solDbCloner
Clk on Copy’ butionto tat copying bolew ited SCL cbjecte
‘Status Nawe Type.
Comments abe
Concome Table
ContactDetais Tabe
EnpAccourts Tabe
Employees Table
EnpRokMarpirg Tabe
KeyParams, Table
Click on Copy to start copying. If any error while copying specific object, then the error message will
be shown in respective row and Error column
BQ SaIDbClcner
Copying in progress
‘Salus Name
Concome
(ContactDetais
EmpAccounts|
Employese
[EmpRoleMapsing
KeyParems
ajo sein ais
2. Copy Data
Click on Next.
We will get an editable grid to enter a list of table names to copy the data from source to target DB. If
‘we want all records to be copied, then leave SqlCommand value as empty. If we want specific records
to be copied, then provide SELECT querye with appropriate WHERE condition.
This part of tool is developed to help developers to copy master tables / configuration tables data from
production to local DB.Ba Sa\DeCloner
ick on Copy’ button to tan copying below Isted SCL objents deta
=r Setamerd
> Eigblty Coverone select from Eiaity_Cov.
|ACH_Run_Log |
| aCHProeoteVakdatonEimor
‘Aaenoy
"Agp_Fle_Nerson
oe
| |Auto_Aroly Fules
[Bonk Acct Table
Once ready, click on Copy to start copying the data. If any error while copying the data (like failed to
insert due to primary key constraint violation or object not found) we can see that in respective objects
Error field.
Hd SalDbCloner
Copying fr progress.
Suite Tibi
Bbity.Coverage [select “fon liyCv.. | vai bec rane "a
[ACH Run_lop SELECT“ FROMACHLR.. lnvald objet name AC. |
[ACHProoeseVaidatonSior | SELECT” FROMACHPro..Invaid abjct name VCH
aeney "| SELECT= FROM Aoena. [vai obi rae oer.
[App_Fle_Veion | SELECT~ FROM Apo Fi. |Invald object name ‘App... |
Asked_e Moroes SELECT FROM Aske... | Iai bjctrame ‘ko
‘Auto_Arply_ Rules SELECT" FROM Ato A... | Invaid abject name “Auto
So simple right :-)
Using the code
There are two important classes and two DTO classes that place key role in this tool
‘They are
1, SqlTranfer
2, DataTransfer3, SqlObject
4, DataObject
1am not going to go in depth on how I invoked the above core classes and rendered the output to UL. 1
would like to briefly discuss the core part of the tool. For example, how I am able to copy schema and
selected data from the source DB to the destination DB.
Below are just DTO classes to hold the list of objects (tables, stored procs, functions ete...) during the
runtime
ci
Copy Code
using Microsoft.SqlServer.Managenent .Sn03
using Systen.conponentModel;
using System.Drawing;
namespace SqlbbCloner.Core.Schema
{
public class Sqlobject
{
public Bitmap Status ( get; set; }
public string Name { get; set; }
[Browsable(false) ]
public Nanedsnoobject Object { gets sets }
public string Type { get; set; }
public string Error { gets set; }
public Sqlobject()
{
J/Status = Properties Resources. unknown}
>
,
»
Cit
Copy Codeusing System.Drawing
namespace SqlDbCloner.Core.Data
t
public class Datadbject
{
public Bitmap Status { get; set; }
public string Table { get; set; }
public string Sqlcommand { get; set; }
public string Error { gets set; }
public Dataobject()
{
//Status = Properties.Resources unknown;
y
,
Now lets start with copy
g schema.
Here is the SqlTransfer class code.
‘+ In constructor, [ am initializing the required objects like connections, server, database and transfer
objects of SMO
+ In” Initserver " method, Iam setting up the required properties of SMO objects when they
initialized. If we dont set them, then SMO will be very slow in getting those information on
demand for each object
‘+ When end user chose schema copy option, will try to connect to source DB and retrieve all SQL.
objects by invoking" GetSqlobjects " method.
‘+ Finally will loop through selected objects and call " CreateObject " or
" DropandCreatedbject " to create objects in destination database.
+ Except keys and indexes all other objects follow above approach.
At last we call the bellow methods in order to apply keys and indexes on destination databse tables
1. ApplyIndexes.
2. ApplyForeignKeys
3. ApplyChecks.
Shrink A Copy Codeusing Microsoft SqlServer Management .Common}
using Microsoft sqlServer.Managenent.Sno;
using System;
using System.collections.Generic;
using System.Data.SqlClient;
using System.Lings
namespace SqlDbCloner.Core. Schema
«
public class Sqltransfer
{
public string _sourceconnectionstring;
Public string _destinationConnectionstring;
public Database sourceDatabase;
public Database destinationbatabase;
public Server sourceservers
public Server destinationserver;
public Transfer transfer;
public Serverconnection sourceconnection;
public Serverconnection destinationConnection;
public List Sourceobjects;
public Listesqlobject> Destinationdbjects;
public SqlTransfer(string sre, string dst)
{
_sourceConnectionstring = sre;
“destinationConnectionString = dst;
sourceconnection = new Serverconnection(new
‘SqlConnection(_sourceconnectionstring));
sourceServer = new Server(sourceConnection) ;
destinationConnection = new ServerConnection(new
sqlConnection(_dest inationconnectionstring)) ;
destinationServer = new Server(destinationConnection) ;
InitServer(sourceserver);
InitServer(destinationServer) ;
sourcebatabase =
sourceServer. Databases [sourceServer .ConnectionContext .DatabaseNane] ;
destinationbatabase =
destinationServer . Databases {destinationServer .ConnectionContext.DatabaseName] ;
transfer = new Transfer(sourcedatabase) ;
transfer.DestinationServer = destinationConnection.ServerInstance;
transfer.DestinationDatabase = destinationConnection.DatabaseNane;
transfer.DestinationLogin = destinationConnection.Login;
transfer.DestinationPassword = destinationConnection. Password;
transfer.options.continueScriptingOnError = true;
transfer.Options NoFileGroup = true;
transfer.Options.NoFxecuteas = true;
transfer.options.WithDependencies » false;
transfer. Options .Dribefaults = true;
transfer.CopySchena = true;transfer.Copydata = false;
transfer.DropbestinationOdjectsFirst = false;
Sourcedbjects = Getsqlobjects(sourceDatabase) ;
Destinationobjects = GetSql0bjects(destinationDatabase) ;
>
private void Initserver(Server serv)
c
J] set the default properties we want upon partial instantiation -
11 swo is *really* slow if you don't do this
serv. SetDefaultinitFields(typeof(Table), "Issystenobject", "Nane");
serv. SetDefaultinitFields(typeof (StoredProcedure), "IsSystendbject", “Nane");
serv. SetDefaultinitFields(typeof(UserDefinedFunction), "IsSystenobject™, "Nane");
serv. SetDefaultInitFields(typeof (Microsoft. SqlServer.Managenent..Sno. View),
“IsSystenob ject”, "Nane");
serv, SetDefaultinitFields(typeof(Colunn), "Identity");
serv.SetDefaultInitFields(typeof(Index), "IndexkeyType");
>
private void ResetTransfer()
{
transfer.CopyallbatabaseTriggers = false;
transfer.CopyAllDefaults = false;
transfer.CopyAllLogins = false;
transfer.Copyallobjects = false;
transfer.CopyAL1Partitionfunctions = false;
transfer.CopyALlPartitionSchenes = false;
transfer.CopyAllRoles = false;
transfer.CopyALiRules = false;
‘transfer.CopyALiSchenas = false;
thansfer.CopyALisqlassenblies = false;
transfer. CopyAliStoredProcedures = false;
transfer.CopyAl synonyms = false;
transfer.CopyAlltables = false;
transfer.CopyALlUserDefinedAggregates = false;
transfer. CopyAllUserDefinedDatatypes = false;
transfer. CopyALlUserDefinedrunctions = false;
transfer.CopyAllUserDefinedTypes = false;
transfer.CopyALlUsers = false;
transfer. CopyAllViews = false;
transfer.CopyAl1xmlSchenaCollections = false;
transfer.CreateTargetDatabase = false;
/[transfer.Oropdest inationdbjectsFirst = false;
transfer.Prefetchobjects = false;
transfer.SourceTranslateChar = false;
d
public void DropAndCreateobject (NamedSmodbject obj)
{
ResetTransfer()3
transfer.bjectList.clear();
transfer.ObjectList .Add(oo4);
if (DestinationObjects.Any(d => ¢.Nane
{
obj .Name))
transfer.Options.ScriptDrops = true;
foreach (var script in transfer.Scripttransfer())(new SqlCommand(script,
dest inationconnection.SqlConnectionObject) ).ExecuteNonQuery();
>
transfer.options.Scriptorops = falses
foreach (var script in transfer.ScriptTransfer())
(new sqiConmand(script,
dest inationconnect ion.SqlConnect ionobject)) .ExecuteNonguery()5
}
public void Createdbject (NamedSmodbject obj)
{
ResetTransfer()3
transfer.objectList.Clear();
transfer.objectList.add(obj);
foreach (var script in transfer. ScriptTransfer())
(new SqlConmand(script,
dest inationConnection.SqlConnectiondbject)) .ExecuteNonguery();
y
private List GetSqlobjects (Database db)
{
Listesqlobject> items = new Listesqlobject>();
foreach(Sqlassenbly item in db.Assenblies)
{
if (Litem. Tssystendbject)
items.Add(new Sqlobject
iten.GetType().Name });
>
Name = iten.Name, Object = item, Type
foreach (UserDefinedDataType iten in db.UserDefinedDataTypes)
{
itens.Add(new SqlObject { Nane = iten.Nane, Object = item, Type
item.GetType().Nane });
y
foreach (UserbefinedTableType item in db.UserDefinedTabletypes)
{
itens.Add(new Sqlobject { Nane = item.Nane, Object = item, Type
item.GetType().Name });
>
foreach (Table item in db.Tables)
t
if (Idten. Issystenobje:
itens.Add(new SqlObject { Nane = iten.Nane, Object:
iten.GetType() Name });
}
item, Type =
foreach (Microsoft.SqlServer.Managenent.Sno.View iten in db.Views)
{
Af (item. Issystendbject)
itens.Add(new SqlObject { Name = iten.Nane, Object = item, Type
iten.GetType().Nane });
y
foreach (UserDefinedFunction iten in db.UserDefinedFunctions)if (litem. Issystenobject)
itens.Add(new SqlObject { Nane = item.Nane, Object = item,
item.GetType().Name })3
}
foreach (StoredProcedure iten in db.StoredProcedures)
if (Item. Tssystenobje:
itens.Add(new Sqlobject { Nane = item.Nane, Object = item,
iten.GetType().Name })5
}
foreach (Microsoft .SqlServer.Managenent .Sno.DatabaseDdl Trigger
db. Triggers)
{
Af (IMten. Issystenobje:
itens.Add(new SqlObject { Nane = iten.Nane, Object = item,
item.GetType().Name })5
}
return items;
,
internal void ApplyIndexes(NamedSnodbject sTable)
{
var dTable = destinationbatabase. Tables[ sTable.Nane];
foreach (Index srcind in (sTable as Table). Indexes)
t
try
{
string name = srcind.Names
Index index = new Index(cTable, name);
index. Indexkeytype = srcind. IndexkeyType;
index.IsClustered = srcind.TsClustered;
index. IsUnique = srcind. IsUnique;
index.CompactLargeObjects = srcind.CompactLargedbjects;
index. TgnoreDuplicatekeys = srcind. TgnoneDuplicateKeys;
index. IsFul1Textkey = srcind. IsFullTextkey;
index.PadIndex = srcind.Padrndex;
Index.FileGroup = sreind.FileGroup;
foreach (IndexedColumn srccol in srcind, IndexedColumns)
Type
Type =
item in
Type =
{
IndexedColunn column =
new IndexedColuma(index, srccol.Name, srecol Descending);
colunn.IsIncluded = srecol.IsIncluded;
index. IndexedColunns.Add (column) ;
}
index.FileGroup = dTable.FileGroup ?? index.FileGroup;
index.Create();
+
catch (Exception exc)
{
// Not yet handled>
internal void ApplyForeignkeys(NamedSmodbject sTable)
{
var dTable = destinationbatabase. Tables[ sTable.Nane];
foreach (Foreignkey sourcefk in (sTable as Table).Foreignkeys)
t
try
{
string name = sourcefk.Nane;
Foreignkey foreignkey = new Foreignkey(dTable, name);
foreignkey.Deletenction = sourcefk.Deletenction;
foreignkey.IsChecked = sourcefk.IsChecked;
foreignkey.IsEnabled = sourcefk. IsEnabled;
Foreignkey.ReferencedTable = sourcefk.Referencedtable;
foreignkey.ReferencedTableSchena = sourcefk. ReferencedTableSchena;
foreignkey.Updateaction = sourcefk.Updateaction;
foreach (ForeignkeyColunn scol in sourcefk.Columns)
{
string refcol = scol.ReferencedColunn;
ForeignkeyColumn column =
new ForeignkeyColumn(foreignkey, scol.Nane, refcol);
foreignkey.Colunns .Add(column) ;
y
foreignkey.Create();
}
catch (Exception exc)
{
J/ Not yet handled
}
>
internal void ApplyChecks(NamedSmoOb ject sTable)
{
var drable = destinationbatabase. Tables[ sTable.Nane];
foreach (Check chkConstr in (sTable as Table) Checks)
£
try
{
Check check = new Check(cTable, chkConstr.Nane) ;
check. TsChecked = chkConstr.TsChecked;
check. IsEnabled = chkConstr. IsEnabled;
check. Text = chkConstr.Text;
check .Create();
}
catch (Exception exc)
{
J/ Not yet handled
+
yinternal void Refresh()
t
Sourceddjects = GetSqlobjects(sourceDatabase) ;
Destinationobjects = GetSqlobjects destinationDatabase);
,
Now its time to look at copying data
Here we use SqIBulkCopy utility class in System.Data SqlClient namespace
‘+ In constructor, initialize all objects and retrieve all table names in source database.
‘+ Based on the selected list, loop through each one and call" Transferbata " method
By default, for each table" SELECT * " gets executed as query until the end user provide their own
query with WHERE clause to copy only selected rows.
cH
Shrink A Copy Codeusing Microsoft SqlServer Management .Common}
using Microsoft sqlServer..Managenent.Sno;
using System. Collections Generic;
using System.Data. SqlClient;
namespace SqlDbCloner.Core.Data
{
public class DataTransfer
«
public Serverconnection sourceconnection;
SqlBulkcopy bulkcopy;
public Listebataobject> Sourceobjects;
public Datatransfer(string src, string dest)
{
bulkCopy = new SqlBulkCopy(dest) ;
bulkcopy.BatchSize = 500;
bulkCopy.Notifyafter = 1000;
sourceConnection = new ServerConnection(new SqlConnection(sec));
var sourceServer = new Server(sourceConnection);
Initserver(sourceserver);
var db = sourceServer.Databases[ sourceServer..ConnectionContext .DatabaseNane
Sourcedbjects = new List();
foreach (Table item in db.Tables)
{
if (litem. Issystenobject)
Sourcedbjects.Add(new Datadbject { Table = item.Name });
>
private void Initserver(Server serv)
{
// set the default properties we want upon partial instantiation -
J/ sno is *really* slow if you don't do this
serv.SetbefaultInitFields(typeof (Table), "IsSystendbject”
serv.SetDefaultInitFields(typeof (StoredProcedure), "IsSystenObject”, “Nam
serv. SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystenObject
serv. SetbefaultInitFields( typeof (Microsoft .SqlServer .Managenent .Sno.View) ,
“TssystemObject", "Name");
serv.SetDefaultInitFields(typeof(Column), “Identity");
serv.SetDefaultInitFields(typeof(Index), "IndexkeyType");
>
public void Trasnferbata(string table, string query)
{
Sqlbatakeader reader = null;
SqlConmand myConmand;
try
t
myConmand = new SqlConnand(query, sourceConnection.SqlConnectionObject) ;
reader = nyConmand. ExecuteReader();
bulkCopy.DestinationTableNane = table;
bulkCopy.WriteToServer (reader) ;>
finally
{
if (reader != null && !reader.1sClosed)
reader.Close();