Tag: SMO

Using SQL Server Management Objects (SMO) in C# with Setup

First Add References. Mine are located:

C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies
- Microsoft.SqlServer.Smo.dll
- You may have to add more than just this DLL.  Try adding one at a time until it works.

Then, you should be able to access the includes:

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

After this, you should be setup to transfer databases and script them out from one place to another like you are using SQL Server Management Studio. All the options should be available.

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

ServerConnection sourceConnection = new ServerConnection("SOURCEPATH");
Server sourceServer = new Server(sourceConnection);
//sourceServer.ConnectionContext.LoginSecure = false;
//sourceServer.ConnectionContext.Login = "3tier";
//sourceServer.ConnectionContext.Password = "3tier";
Database sourceDatabase = sourceServer.Databases["SourceDB"];
 
ServerConnection destinationConnection = new ServerConnection("localhost");
Server destinationServer = new Server(destinationConnection);
Database destinationDatabase;
if (destinationServer.Databases.Contains("SourceDB"))
{
    //destinationServer.ConnectionContext.LoginSecure = false;
    //destinationServer.ConnectionContext.Login = "3tier2";
    //destinationServer.ConnectionContext.Password = "3tier2";
    destinationDatabase = destinationServer.Databases["SourceDB"];
}
else
{
    destinationDatabase = new Database(destinationServer, "SourceDB");
    destinationDatabase.Create();
}
 
//Microsoft.SqlServer.Management.Smo
Transfer transfer = new Transfer(sourceDatabase);
transfer.CopyAllObjects = false; //turn off because we just want tables and sp
transfer.CopyAllTables = true;
transfer.CopyAllStoredProcedures = true;
 
transfer.DropDestinationObjectsFirst = true;
transfer.UseDestinationTransaction = true;
 
transfer.CopyAllTables = true;
transfer.Options.Indexes = true;
transfer.Options.WithDependencies = true;
transfer.Options.ContinueScriptingOnError = true;
transfer.CopySchema = true;
transfer.Options.WithDependencies = true;
transfer.Options.DriAll = true;
 
transfer.Options.AnsiFile = true;
transfer.Options.SchemaQualify = true;
transfer.Options.WithDependencies = false;
transfer.Options.ScriptDrops = true;
transfer.CreateTargetDatabase = true;
 
transfer.CopySchema = true;
transfer.CopyData = false;
 
transfer.DestinationServer = destinationServer.Name;
transfer.DestinationDatabase = destinationDatabase.Name;
//transfer.DestinationLoginSecure = false;
//transfer.DestinationLogin = "3tier2";
//transfer.DestinationPassword = "3tier2";
 
transfer.Options.IncludeIfNotExists = true;
transfer.TransferData();

Be sure to double and triple check because it can wipe out the destination databases without you realizing it.

I hope this helps you out!