Click here to Skip to main content
Click here to Skip to main content

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

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

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.

License

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

Share

About the Author

SmartCodar

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 www.SmartCodar.com.

Comments and Discussions

 
BugHow to copy complete database with all table constraints( FKs, PKs ..) Pinmembermyssa2727-Sep-11 1:39 
Generalerror occured..:( Pinmemberurooj_mahmood25-May-10 20:54 
Questionhow to copy live data base to my local system?? Pinmemberurooj_mahmood25-May-10 19:57 
QuestionExclude System Objects Pinmemberstixoffire11-Sep-09 16:34 
QuestionHow do I do this for Access database Pinmemberwkusmider14-Jun-09 18:00 
QuestionVisual Studio 2005? Pinmemberfreddy1234520-Mar-09 9:50 
GeneralThanks PinmemberAdam Crawford12-Nov-08 23:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.141015.1 | Last Updated 28 May 2008
Article Copyright 2008 by SmartCodar
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid