Shahar Gvirtz's Weblog

Generating SQL Backup Script for Tables & Data from any .NET Application using SMO

What is SMO?

SMO, SQL Server Management Object, is a collection of objects that includes all you need to manage SQL Server from .NET applications. With SMO you can manage pretty much everything – from databases, tables, stored procedures, jobs – you name it.

Actually, SMO is “.NET Version” of COM object known as SQL-DMO (SQL Distributed Management Objects).

SMO Assemblies (dll’s) can usually be found at: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\ (for SQL Server 2008).

What We’re Going To Do?

In this post I’ll show an easy way to write a method that generates SQL File that include backup of both the schema and the data of specific tables (CREATE statements for each table and INSERT statements for all the data). Same as if you right-click on DB name at SQL Server Management Studio and choose “Generate Scripts”

The Method

First, we’ll have to add references to the following assemblies:

1. Microsoft.SqlServer.ConnectionInfo
2. Microsoft.SqlServer.Management.Sdk.Sfc
3. Microsoft.SqlServer.Smo

[All of them can be found in the directory I mentioned before, with the same name as in the list just with “.dll” in the end]

In the following example, we want to backup specific tables (we know the tables names) that are in a specific DB. This is how our method will look:

   1: public static void Backup(string FileName, string[] Tables)

   2: {

   3:     StringBuilder sb = new StringBuilder();

   4:     Server srv = new Server(new Microsoft.SqlServer.Management.Common.ServerConnection("<db name>", "<user name>", "<password>"));

   5:     Database dbs = srv.Databases["<db name>"];

   6:     ScriptingOptions options = new ScriptingOptions();

   7:     options.ScriptData = true;

   8:     options.ScriptDrops = false;

   9:     options.FileName = FileName;

  10:     options.EnforceScriptingOptions = true;

  11:     options.ScriptSchema = true;

  12:     options.IncludeHeaders = true;

  13:     options.AppendToFile = true;

  14:     options.Indexes = true;

  15:     options.WithDependencies = true;

  16:     foreach (var tbl in Tables)

  17:     {

  18:         dbs.Tables[tbl].EnumScript(options);

  19:     }

  20: }

Our method get two parameters. The first one is the Full path to the file we want to export, and the second is an array with name of the tables we want to generate SQL backup script for.

In line 4 you can see that there is a new instance of “Server” object that represents the SQL Server itself.

In line 5 choose the DB that we want (Pay attention to add Exception handling if you going to use this code).

Then, from line 6 to 15 we set some properties in the ScriptingOptions object that includes our settings for the script generation. In this example, i backup both data and schema so ScriptData and ScriptSchema are both true.

I also backup the Indexes. And, in this example, i set WithDependencies property as true which means that the generated script will include CREATE statements for tables that are somehow related to the tables we actually backup. For example, if table A has FK to table B and I’ll backup table A with this method, it’ll also generates the SQL Statements to backup table B.
You can find the full documentation of all the available settings here.

In lines 15-18 we iterate the array we get as a parameter to the method and for each table call Table.EnumScript method thet get ScriptiongOptions instance as parameter (which includes all the settings). This method also returns a collection of strings (IEnumerable<string>) that includes the generated SQL.

In this example i Ignore the returned value because i set the property ScriptiongOptions.FileName to a path for a file where the SQL will be saved, so i don’t need the collection in the code (know that because i call EnumScript several times, for each table, it’s important to set ScriptingOptions.AppendToFile as true or we’ll override the file each time).

The Difference Between EnumScript and Script

If you’ll take a look in the documentation you’ll see that there is also method called Script that we can use with Table object. So why don’t I use it?

The reason (which i couldn’t find in the official documentation) is that if we set in the ScriptingOptions object we pass to the method ScriptiongOptions.ScriptData = true we must use EnumScript because Script will give us the following Exception:

“This method does not support scripting data”.

Summary

In order to work with SQL Server from .NET Applications we have SMO components.
We can generate SQL statements that can be used to backup table (create table and insert data) with Table.EnumScript method, and we can give the settings we want with ScriptingOptions object.

Shahar.