Copy SQL Server database between servers

Example of using SQL Server Management Objects (SMO) to copy a database from one server to another.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46: 
47: 
48: 
49: 
50: 
51: 
52: 
53: 
54: 
55: 
56: 
57: 
58: 
59:

#r "Microsoft.SqlServer.ConnectionInfo" 
#r "Microsoft.SqlServer.Smo" 
#r "Microsoft.SqlServer.SmoExtended.dll"
#r "Microsoft.SqlServer.Management.Sdk.Sfc"

// Based on this script shown here:
// https://www.mssqltips.com/sqlservertip/1910/transfer-sql-server-database-schema-objects-and-data-with-smo/

open System
open Microsoft.SqlServer.Management.Smo
open Microsoft.SqlServer.Management.Common

let serverWithConnection (serverName : string) =
    let server = Server(serverName)
    server.ConnectionContext.LoginSecure <- true
    server.ConnectionContext.Connect()
    server

let sourceServer = serverWithConnection("RKNVIDW01")
let sourceDatabase = sourceServer.Databases.["Demo"]

let destServer = serverWithConnection("NZ-JEREMYH-WKS")
let destDatabase = Database(destServer, "DemoCopy")

if [| for database in destServer.Databases -> database.Name |] 
   |> Array.exists (fun databaseName -> databaseName = "DemoCopy") then
    let existingDestDatabase = destServer.Databases.["DemoCopy"]
    existingDestDatabase.Drop()
    destDatabase.Create()
else
    destDatabase.Create()

let transferDatabase = 
    Transfer(sourceDatabase, 
        CopyAllObjects = true, 
        CopyAllSchemas = true, 
        CopyAllUserDefinedDataTypes = true, 
        CopyAllTables = true, 
        CopyData = true, 
        CopyAllStoredProcedures = true,
        DestinationServer = destServer.Name,
        DestinationDatabase = destDatabase.Name)

transferDatabase.DataTransferEvent.Add(fun args ->
    Console.ForegroundColor <- ConsoleColor.Green
    Console.Write("[" + args.DataTransferEventType.ToString() + "] ")
    Console.ResetColor()
    Console.WriteLine(" : " + args.Message))

transferDatabase.DiscoveryProgress.Add(fun args ->
    Console.WriteLine("[" + args.Current.Value + "]" ))

transferDatabase.ScriptingProgress.Add(fun args ->
    Console.WriteLine("[" + args.Current.Value + "]"))

transferDatabase.ScriptingError.Add(fun args ->
    Console.WriteLine("[" + args.Current.Value + "]"))

transferDatabase.TransferData()

namespace System

namespace Microsoft

namespace Microsoft.SqlServer

namespace Microsoft.SqlServer.Management

namespace Microsoft.SqlServer.Management.Smo

namespace Microsoft.SqlServer.Management.Common

val serverWithConnection : serverName:string -> Server

Full name: Script.serverWithConnection

val serverName : string

Multiple items
val string : value:'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------
type string = String

Full name: Microsoft.FSharp.Core.string

val server : Server

Multiple items
type Server =
  inherit SqlSmoObject
  new : unit -> Server + 2 overloads
  member ActiveDirectory : ServerActiveDirectory
  member AffinityInfo : AffinityInfo
  member Alter : unit -> unit + 1 overload
  member AttachDatabase : name:string * files:StringCollection -> unit + 3 overloads
  member AuditLevel : AuditLevel with get, set
  member Audits : AuditCollection
  member AvailabilityGroups : AvailabilityGroupCollection
  member BackupDevices : BackupDeviceCollection
  member BackupDirectory : string with get, set
  ...

Full name: Microsoft.SqlServer.Management.Smo.Server

--------------------
Server() : unit
Server(name: string) : unit
Server(serverConnection: ServerConnection) : unit

property Server.ConnectionContext: ServerConnection

property ConnectionSettings.LoginSecure: bool

ConnectionManager.Connect() : unit

val sourceServer : Server

Full name: Script.sourceServer

val sourceDatabase : Database

Full name: Script.sourceDatabase

property Server.Databases: DatabaseCollection

val destServer : Server

Full name: Script.destServer

val destDatabase : Database

Full name: Script.destDatabase

Multiple items
type Database =
  inherit ScriptNameObjectBase
  new : unit -> Database + 1 overload
  member ActiveConnections : int
  member ActiveDirectory : DatabaseActiveDirectory
  member Alter : unit -> unit + 2 overloads
  member AnsiNullDefault : bool with get, set
  member AnsiNullsEnabled : bool with get, set
  member AnsiPaddingEnabled : bool with get, set
  member AnsiWarningsEnabled : bool with get, set
  member ApplicationRoles : ApplicationRoleCollection
  member ArithmeticAbortEnabled : bool with get, set
  ...

Full name: Microsoft.SqlServer.Management.Smo.Database

--------------------
Database() : unit
Database(server: Server, name: string) : unit

val database : Database

property Database.Name: string

type Array =
  member Clone : unit -> obj
  member CopyTo : array:Array * index:int -> unit + 1 overload
  member GetEnumerator : unit -> IEnumerator
  member GetLength : dimension:int -> int
  member GetLongLength : dimension:int -> int64
  member GetLowerBound : dimension:int -> int
  member GetUpperBound : dimension:int -> int
  member GetValue : [<ParamArray>] indices:int[] -> obj + 7 overloads
  member Initialize : unit -> unit
  member IsFixedSize : bool
  ...

Full name: System.Array

val exists : predicate:('T -> bool) -> array:'T [] -> bool

Full name: Microsoft.FSharp.Collections.Array.exists

val databaseName : string

val existingDestDatabase : Database

Database.Drop() : unit

Database.Create() : unit
Database.Create(forAttach: bool) : unit

val transferDatabase : Transfer

Full name: Script.transferDatabase

Multiple items
type Transfer =
  inherit DataTransferBase
  new : unit -> Transfer + 1 overload
  member BatchSize : int with get, set
  member BulkCopyTimeout : int with get, set
  member GetTransferProvider : unit -> IDataTransferProvider
  member TemporaryPackageDirectory : string with get, set
  member TransferData : unit -> unit
  event DataTransferEvent : DataTransferEventHandler

Full name: Microsoft.SqlServer.Management.Smo.Transfer

--------------------
Transfer() : unit
Transfer(database: Database) : unit

property Server.Name: string

event Transfer.DataTransferEvent: IEvent<DataTransferEventHandler,DataTransferEventArgs>

member IObservable.Add : callback:('T -> unit) -> unit

val args : DataTransferEventArgs

type Console =
  static member BackgroundColor : ConsoleColor with get, set
  static member Beep : unit -> unit + 1 overload
  static member BufferHeight : int with get, set
  static member BufferWidth : int with get, set
  static member CapsLock : bool
  static member Clear : unit -> unit
  static member CursorLeft : int with get, set
  static member CursorSize : int with get, set
  static member CursorTop : int with get, set
  static member CursorVisible : bool with get, set
  ...

Full name: System.Console

property Console.ForegroundColor: ConsoleColor

field ConsoleColor.Green = 10

Console.Write(value: string) : unit
   (+0 other overloads)
Console.Write(value: obj) : unit
   (+0 other overloads)
Console.Write(value: uint64) : unit
   (+0 other overloads)
Console.Write(value: int64) : unit
   (+0 other overloads)
Console.Write(value: uint32) : unit
   (+0 other overloads)
Console.Write(value: int) : unit
   (+0 other overloads)
Console.Write(value: float32) : unit
   (+0 other overloads)
Console.Write(value: decimal) : unit
   (+0 other overloads)
Console.Write(value: float) : unit
   (+0 other overloads)
Console.Write(buffer: char []) : unit
   (+0 other overloads)

property DataTransferEventArgs.DataTransferEventType: DataTransferEventType

Enum.ToString() : string
Enum.ToString(format: string) : string

Console.ResetColor() : unit

Console.WriteLine() : unit
   (+0 other overloads)
Console.WriteLine(value: string) : unit
   (+0 other overloads)
Console.WriteLine(value: obj) : unit
   (+0 other overloads)
Console.WriteLine(value: uint64) : unit
   (+0 other overloads)
Console.WriteLine(value: int64) : unit
   (+0 other overloads)
Console.WriteLine(value: uint32) : unit
   (+0 other overloads)
Console.WriteLine(value: int) : unit
   (+0 other overloads)
Console.WriteLine(value: float32) : unit
   (+0 other overloads)
Console.WriteLine(value: float) : unit
   (+0 other overloads)
Console.WriteLine(value: decimal) : unit
   (+0 other overloads)

property DataTransferEventArgs.Message: string

event TransferBase.DiscoveryProgress: IEvent<ProgressReportEventHandler,ProgressReportEventArgs>

val args : ProgressReportEventArgs

property ProgressReportEventArgs.Current: SqlServer.Management.Sdk.Sfc.Urn

property SqlServer.Management.Sdk.Sfc.Urn.Value: string

event TransferBase.ScriptingProgress: IEvent<ProgressReportEventHandler,ProgressReportEventArgs>

event TransferBase.ScriptingError: IEvent<ScriptingErrorEventHandler,ScriptingErrorEventArgs>

val args : ScriptingErrorEventArgs

property ScriptingErrorEventArgs.Current: SqlServer.Management.Sdk.Sfc.Urn

Transfer.TransferData() : unit

Raw view Test code New version

More information

Link:	http://fssnip.net/tM
Posted:	8 years ago
Author:	jeremyh
Tags:	smo , sql server