2 people like it.
Like the snippet!
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
More information