Click here to Skip to main content
13,400,107 members (46,083 online)
Click here to Skip to main content
Add your own
alternative version


24 bookmarked
Posted 28 May 2008

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

, 28 May 2008
Rate this:
Please Sign up or sign in to vote.
How to use SMO to copy database schema and data using C#.


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

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

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

    //Start Transfer

    //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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Ameet Parse
India India
I am a Tech Lead / .Net Architect,

I love working with Business Challenges, Requirements

I work mainly for Designing eCommerce Portals Using ASP.Net enabled Frameworks such as Mediachase, zNode and Storefront.

I love writing my Articles at Code Project and my site

You may also be interested in...


Comments and Discussions

BugHow to copy complete database with all table constraints( FKs, PKs ..) Pin
myssa2727-Sep-11 2:39
membermyssa2727-Sep-11 2:39 
Generalerror occured..:( Pin
urooj_mahmood25-May-10 21:54
memberurooj_mahmood25-May-10 21:54 
Questionhow to copy live data base to my local system?? Pin
urooj_mahmood25-May-10 20:57
memberurooj_mahmood25-May-10 20:57 
QuestionExclude System Objects Pin
stixoffire11-Sep-09 17:34
memberstixoffire11-Sep-09 17:34 
QuestionHow do I do this for Access database Pin
wkusmider14-Jun-09 19:00
memberwkusmider14-Jun-09 19:00 
QuestionVisual Studio 2005? Pin
freddy1234520-Mar-09 10:50
memberfreddy1234520-Mar-09 10:50 
GeneralThanks Pin
Adam Crawford13-Nov-08 0:42
memberAdam Crawford13-Nov-08 0:42 

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180218.2 | Last Updated 28 May 2008
Article Copyright 2008 by Ameet Parse
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid