2 people like it.

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
type ConsoleColor =
  | Black = 0
  | DarkBlue = 1
  | DarkGreen = 2
  | DarkCyan = 3
  | DarkRed = 4
  | DarkMagenta = 5
  | DarkYellow = 6
  | Gray = 7
  | DarkGray = 8
  | Blue = 9
  ...

Full name: System.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