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

• Download sample - 19.3 KB

Introduction

This article shows how to use SQL Server Management Objects (SMO) and C# to Script to copy database schema with or without data.

Using the code

I have created a class DBHelper which is used to hold SQL Server instance information such as server name and database name, and the CopyDatabase method is used to copy the database. 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 SQL Server using Microsoft SQL Server Management Objects (SMO). Now I am in the process of writing a library which will interact with SQL Server for various parameters such as instance information, network information, backup and restore script, etc., and will publish it as soon as possible.