Click here to Skip to main content
11,483,586 members (71,276 online)
Click here to Skip to main content

Export and Import Database using SMO

, 30 Apr 2014 CPOL 4.2K 6
Rate this:
Please Sign up or sign in to vote.
Export and import database using SMO

Introduction

There will be situation where you want to export data from one database to other database. This can be achieved in many ways but the easiest way to do is by using SSMS export/import wizard but there will be a situation where you have to export data using code, I mean from your application. Such situations can be easily dealt with using SQL Management Objects (SMO).

For the following sample application to work properly, you need to refer to the following SMO DLLs:

  • Microsoft.SqlServerConnectionInfo
  • Microsoft.SqlServer.Dmf
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoExtended
  • Microsoft.SqlServer.SqlEnum

Here is the code that shows how to export data from source database to destination database.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Configuration;
using System.Diagnostics;
using IOM.DataGrazer;
using IOM.DataGrazer.Common;
using IOM.DataGrazer.DBHelper;
using System.Data.SqlClient;

namespace ExportAndImportDatabase
{
class Program
{
static void Main(string[] args)
{
try
{
// Export source database to destination database
ExportData();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}

private static void ExportData()
{
string sourceConnectionString = string.Empty;
string destinationConnectionString = string.Empty;
SqlConnectionStringBuilder sourceConnBuilder = new SqlConnectionStringBuilder(sourceConnectionString);
SqlConnectionStringBuilder destConnBuilder = new SqlConnectionStringBuilder(destinationConnectionString);

ServerConnection serverConnection;
if (sourceConnBuilder.IntegratedSecurity)
{
serverConnection = new ServerConnection(sourceConnBuilder.DataSource);

// Windows Authentication
serverConnection.LoginSecure = true;
}
else
{
serverConnection = new ServerConnection
(sourceConnBuilder.DataSource, sourceConnBuilder.UserID, sourceConnBuilder.Password);
}

Server server = new Server(serverConnection);
Database database = server.Databases[sourceConnBuilder.InitialCatalog];

// Define a Transfer object and set the required options and properties. 
Transfer xfr = new Transfer(database);

xfr.CopyAllObjects = false;

xfr.CopyAllTables = true;
////xfr.CopyData = true;
////xfr.CopySchema = true;
////xfr.DropDestinationObjectsFirst = true;

////xfr.CreateTargetDatabase = true;
xfr.DestinationDatabase = "DestDBName";
xfr.DestinationServer = destConnBuilder.DataSource;

if (destConnBuilder.IntegratedSecurity)
{
xfr.DestinationLoginSecure = true;
}
else
{
xfr.DestinationLogin = destConnBuilder.UserID;
xfr.DestinationPassword = destConnBuilder.Password;
}

ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptSchema = true;
scriptOptions.ScriptData = true;
scriptOptions.EnforceScriptingOptions = true;
scriptOptions.DriAllKeys = true;
scriptOptions.ScriptBatchTerminator = true;
scriptOptions.ScriptDrops = true;
//scriptOptions.IncludeIfNotExists = true;

scriptOptions.WithDependencies = true;
////scriptOptions.Indexes = true; 
xfr.Options = scriptOptions;

xfr.DataTransferEvent += new DataTransferEventHandler(DataTransferEvent_Handler);
xfr.DiscoveryProgress += new ProgressReportEventHandler(DiscoveryProgress_Handler);
xfr.ScriptingProgress += new ProgressReportEventHandler(ScriptingProgress_Handler);
xfr.ScriptingError += new ScriptingErrorEventHandler(ScriptingError_Handler);

xfr.TransferData();
}

static void DiscoveryProgress_Handler(object sender, ProgressReportEventArgs e)
{
Console.WriteLine(e.Total + "/" + e.TotalCount + " " + e.Current.Value);
}

static void DataTransferEvent_Handler(object sender, DataTransferEventArgs e)
{
Console.WriteLine("[" + e.DataTransferEventType + "] " + e.Message);
}

static void ScriptingProgress_Handler(object sender, ProgressReportEventArgs args)
{
Console.WriteLine("[" + args.Current.Value + "]");
}

static void ScriptingError_Handler(object sender, ScriptingErrorEventArgs args)
{
Console.WriteLine("[" + args.Current.Value + "]");
}

}
} 

The above sample exports only tables, keys and its data to destination database. If you want to export everything including stored procedure and functions, make ‘CopyAllObjects = true’, then there is no need to explicitly mention ‘CopyAllTables = true’ in the above code. The ‘Transfer’ object has four events which can be useful to identify the status of the transfer operation.

License

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

Share

About the Author

Sridhar Pasham
Software Developer (Senior)
India India
I am a web developer with technologies include ASP.NET, C#, EF, SQL Server, JQuery. I like to blog about code, web and technology.

You can find more details about me at my blog www.sridharpasham.com
Follow on   Twitter   LinkedIn

Comments and Discussions

 
GeneralMy vote of 1 Pin
Sunasara Imdadhusen1-May-14 1:16
professionalSunasara Imdadhusen1-May-14 1:16 

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 | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 30 Apr 2014
Article Copyright 2014 by Sridhar Pasham
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid