Export and import database using smo

There will be situation where you want to export data from one database to other database. This can be achieved in my ways but the easiest way to do is using SSMS export/import wizard but there will be 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).

The following sample application to work properly you need to refer 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 no need to explicitly mention ‘CopyAllTables = true’ in the above code. The ‘Transfer’ object has four events which can be useful to identity the status of the transfer operation.

Advertisements

Deleting duplicate records from a table

Note:  The examples in this post are written and tested in SQL Server 2008.

The below example expalins how to delete duplicate  records from a table in SQL Server database.

First create a table and insert some duplicate records into it.


--Creating a table for duplicate records

CREATE TABLE StudentDetails(Id int Primary key,RollNum int,Name varchar(20))

--Inserting Records

Insert into StudentDetails values(1,10,'Krishna')

Insert into StudentDetails values(2,11,'Raju')

Insert into StudentDetails values(3,10,'Krishna')

Insert into StudentDetails values(4,12,'Jagadish')

Insert into StudentDetails values(5,10,'Krishna')

In the above table three records are duplicated with RollNum=10 and Name=”Krishna”. We can remove these duplicate values by this query.

--Deleting duplicate records

delete DuplicateRecordTable from

(

      select row_number() over

      (partition by RollNum,Name order by RollNum)as DuplicateCount ,

      * from StudentDetails

) as DuplicateRecordTable

where DuplicateCount>1

Now the resulting table only have 3 records.

Note:  In the above query after ‘partition by’ keyword we should have to specify column names which have the duplicate values.

Another example:

Another way of deleting duplicate records from a table which doesn’t have a primary key is explained below.

First create a table without any primary key and insert some duplicate records into it.


--Creating a table for duplicate records

CREATE TABLE StudentDtls(RollNum int,Name varchar(20))

--Inserting Records

Insert into StudentDtls values(10,'Krishna')

Insert into StudentDtls values(11,'Raju')

Insert into StudentDtls values(10,'Krishna')

Insert into StudentDtls values(12,'Jagadish')

Insert into StudentDtls values(10,'Krishna')

observe no primary key created in this example. Now, to delete duplicate records execute the following query.

--Deleting duplicate records

SELECT DISTINCT *

      INTO DuplicateRecordTable

      FROM StudentDtls

      GROUP BY RollNum,Name

      HAVING COUNT(RollNum) > 1

DELETE StudentDtls

  WHERE RollNum

  IN (SELECT RollNum

         FROM DuplicateRecordTable)

INSERT StudentDtls

  SELECT *

     FROM DuplicateRecordTable

DROP TABLE DuplicateRecordTable

Now the resulting table will have only three unique records.