navigation
 Sunday, July 08, 2007
Get meta data, do backups with SQL Management Objects (SMO)

SQL Management Objects (SMO) is something of a Swiss army knife that lets you traverse meta data, automate backup and restore, and otherwise manage SQL Server 2005 through .NET code.  For instance, to perform the classic database hierarchy walk use the SmoApplication object and enumerate the servers.  First you need to reference the Smo assemblies:

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll

Then call the SmoApplication EnumAvailableSqlServers() method, passing 'true' to list only local servers:

1
 
DataTable tblServers = SmoApplication.EnumAvailableSqlServers(true);

EnumAvailableSqlServers() may not work if you don't have a network connection, but you can use the RegisteredServers collection property instead. This will work without network connection and will pick up server instances for SQL 2000/2005, SQL Express and MSDE. Here the heirarchy follows the expected pattern of Server/Database/Table: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
foreach (RegisteredServer registeredServer in SmoApplication.SqlServerRegistrations.RegisteredServers)
{
    Server server = new Server(registeredServer.Name);
    Console.WriteLine("Server: {0}  Version: {1}", 
        registeredServer.Name, server.PingSqlServerVersion(server.Name).Major); 

    foreach (Database database in server.Databases)
    {
        if ((!database.IsSystemObject) && (database.IsAccessible))
        {
            Console.WriteLine("Database: {0}", database.Name);

            foreach (Table table in database.Tables)
            {
                Console.WriteLine("Table: {0} Rows: {1}", table.Name, table.RowCount);
            }
        }
    }
}

There are a large number of collections and enumerating methods, for example: stored procedures, user defined types, roles, rules, schemas, locks and permissions.  SMO works against Sql Server earlier than 2005, but some methods may not be supported.  You can handle that by checking the sql version: 

1
2
3
4
5
6
7
 
if (server.PingSqlServerVersion(server.Name).Major > 8)
{
    foreach (UserDefinedDataType udf in database.UserDefinedDataTypes)
    {
        Console.WriteLine("Type: {0}", udf.Name);
    }
}

A nifty SMO bonus is that objects Database and downwards in the hiearchy have a Script StringCollection property.  Script is pre-populated with T-SQL and can be used to recreate objects.

SMO provides DBA automation functionality like backup/restore and create/drop databases. These operations depend on the SMO Server object.  The constructor for Server can have a) no parameters for your local server, b) a server name only, or c) a ServerConnection object in case you need to supply user name and password.

1
2
 
ServerConnection serverConnection = new ServerConnection("MyMachine", "sa", "MyM@ch1n3");
Server raServer = new Server(serverConnection);

Here is an example of creating a new database to be used as a backup location: 

1
2
3
4
5
6
7
8
 
static Database CreateBackupDatabase(Server server, string databaseName)
{
    string backupDatabaseName = databaseName + "_" + DateTime.Now.ToString("yyMMddHHmmffffff");
    Database backupDatabase = new Database(server, backupDatabaseName);
    Console.WriteLine("Creating new database {0}", backupDatabase.Name);
    backupDatabase.Create();
    return backupDatabase;
}

The backup operation requires a backup "device", in this case a file that will be used to store the backup.  Assign an SMO Database object to be backed up, add the backup device to its list of devices and call the SqlBackup() method.  You may also want to assign event handlers for PercentComplete and Complete events.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
static void DoBackup(Server server, string databaseName, Database backupDatabase)
{
    Console.WriteLine("Backing up {0}", databaseName);

    BackupDeviceItem backupDevice =
        new BackupDeviceItem(databaseName + ".bak", DeviceType.File);

    Backup backup = new Backup();
    backup.Database = databaseName;
    backup.Devices.Add(backupDevice);
    backup.PercentComplete += new PercentCompleteEventHandler(InProgress);
    backup.Complete += new ServerMessageEventHandler(Complete);
    backup.SqlBackup(server);
}

static void InProgress(object sender, PercentCompleteEventArgs e)
{
    Console.WriteLine("Percent complete: {0}", e.Percent);
}

Restoring can sometimes be trickier due to the original database having a strangle-hold on the physical data and log files.  To work around this use the Restore RelocateFiles property to map new file names and locations. Also notice that to get the physical file path we use the Database object's FileGroups property, drill down into the Files and use the path of the first file in the list.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
static void DoRestore(Server server, string databaseName, Database backupDatabase)
{
    Console.WriteLine("Restoring {0}", backupDatabase.Name);
    Restore restore = new Restore();
    restore.Database = backupDatabase.Name;
    restore.ReplaceDatabase = true;
    string backupPath = Path.GetDirectoryName(backupDatabase.FileGroups[0].Files[0].FileName);
    string dataPath = string.Format("{0}\\{1}.mdf", backupPath, backupDatabase.Name);
    restore.RelocateFiles.Add(new RelocateFile("Falafel", dataPath));
    restore.RelocateFiles.Add(new RelocateFile("Falafel_log", Path.ChangeExtension(dataPath, ".ldf")));
    BackupDeviceItem backupDevice =
        new BackupDeviceItem(databaseName + ".bak", DeviceType.File);
    restore.Devices.Add(backupDevice);
    restore.PercentComplete += new PercentCompleteEventHandler(InProgress);
    restore.Complete += new ServerMessageEventHandler(Complete);
    restore.SqlRestore(server);
}

Thats a few of the things SMO can do for you. It's not a replacement for T-SQL, but if you need access to meta-data, automation or other DBA tasks from managed code, then the SMO namespace may be worth exploring. BTW, you can also use SMO in PowerShell directly, wrapped in commands or as the infrastructure for PowerShell providers.

posted on July 8, 2007  #    by Noel Rice  Comments [1]
Related posts:
Get More Out of Your Foreign Keys
How To Write Secure Dynamic SQL
Good article about some lesser-known differences between temp tables and table variables
Dealing with #1.INF (Positive Infinity) in SQL Server
MSSQL System Stored Procedures Everyone Should Know
Hypothetical Indices
Saturday, October 20, 2007 6:25:30 PM UTC
I think someone build a product based on this.
http://www.DigitalTools.com/
Jim
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, i, strike, u) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):
Live Comment Preview

Copyright © 2003-2008 Falafel Software Inc.