Copy Database Schema and Data Using SQL Server Management Objects (SMO)

• Download SMOScriptingExample - 43.55 KB

Introduction

This article show how to use SQL Server Management Objects (SMO), C# to Script, Copy Database Schema with or without data .

Using the code

I have created a class DBHelper which is used to hold the SQL Server Instance Information such as Server Name, Database name and having CopyDatabase method used to copy the dastabase. Please make sure that you have SQL Server 2005 and MS SQL Server Management Objects Installed on your Computer

           
public static void CopyDatabase(bool bCopyData)

{
//Set Source SQL Server Instance Information
Server server = new Server(DBHelper.SourceSQLServer); 

//Set Source Database Name [Database to Copy]
Database database = server.Databases[DBHelper.SourceDatabase]; 

//Set Transfer Class Source Database
Transfer transfer = new Transfer(database);

//Yes I want to Copy All the Database Objects
transfer.CopyAllObjects = true;

//In case if the Destination Database / Objects Exists Drop them First
transfer.DropDestinationObjectsFirst = true;

//Copy Database Schema
transfer.CopySchema = true;

//Copy Database Data Get Value from bCopyData Parameter
transfer.CopyData = bCopyData;

//Set Destination SQL Server Instance Name
transfer.DestinationServer = DBHelper.DestinationSQLServer;

//Create The Database in Destination Server
transfer.CreateTargetDatabase = true; 

//Set Destination Database Name
Database ddatabase = new Database(server, DBHelper.DestinationDatabase);

//Create Empty Database at Destination
ddatabase.Create();

//Set Destination Database Name
transfer.DestinationDatabase = DBHelper.DestinationDatabase;

//Include If Not Exists Clause in the Script
transfer.Options.IncludeIfNotExists = true; 

//Start Transfer
transfer.TransferData();

//Release Server variable
server = null;
}
        

Points of Interest

Yes we can play with the SQL Server using Microsoft SQL Server Management Objects (SMO). Now I am in process of writing a library which will interact with SQL server for various parameters such as Instance information, Network Information, Backup, Restore and Script etc. and will publish the same as soon as possible