Click here to Skip to main content
11,789,512 members (49,830 online)
Click here to Skip to main content

SMO - Manage your SQL Server!

, 31 May 2006 CPOL 171.9K 4K 120
Rate this:
Please Sign up or sign in to vote.
A quick start tutorial on how to manage your Microsoft SQL Server 2000/2005 with C# and the Microsoft's SQL Server Management Objects (SMO)
Sample Image - SMODemo.jpg


May I introduce you to SMO, the Framework provided by Microsoft to manage your Microsoft SQL Servers? This article is a quick start tutorial. SMO is a replacement of the SQL-DMO - it's now managed code and it supports the features of the SQL Server 2005. The intention of this article is to show what SMO is, explain some basic steps, and to make you hungry for more.


  • .NET: Use your well known framework to develop your SQL Server applications.
  • OOP: It's much easier to browse the object hierarchy with IntelliSense than browsing hundreds of stored procedures.
  • Exceptions: SMO uses exceptions in an exemplary way.


  • Requires .NET Framework to be installed on the client PC
  • Works only with Microsoft SQL Servers
  • Needs SMO to be installed

Note that most (or even all) features provided by the SMO are also available as stored procedures on the SQL Server. So you could say the SMO is a wrapper for the stored procedures on the SQL Server.

System Requirements

  • .NET Framework 2.0
  • SQL Server 2000 or 2005 (NOTE: SMO Supports features that are new in SQL Server 2005 and do not work with SQL Server 2000)
  • SMO needs to be installed

Skill Requirements

  • Basic C# knowledge + Windows Forms
  • Basic SQL Server knowledge
  • About 30 minutes of time

Adding a Reference to SMO

To use the SMO, you have to add it as a Reference to your Project. This screen shots have been made with Visual Studio 2005 Express Edition.

Solution Browser - > References -> Add Reference

Select SQL Server Components and Hit OK.

Getting Connected

The first thing we have to do is make a connection to our server.
Now you might be thinking "Hey, there is already a class existing to connect to a SQL Server - System.Data.SqlClient.SqlConnection", and you are all right - you can use this class to build your connection to the SQL Server.

Microsoft.SqlServer.Management.Smo.Server server;
///  <summary>
/// Initializes the field 'server'
/// </summary> 
void InitializeServer()
    // To Connect to our SQL Server - 
    // we Can use the Connection from the System.Data.SqlClient Namespace.
    SqlConnection sqlConnection = 
      new SqlConnection(@"Integrated Security=SSPI; Data Source=(local)\SQLEXPRESS");

    //build a "serverConnection" with the information of the "sqlConnection"
    Microsoft.SqlServer.Management.Common.ServerConnection serverConnection = 
      new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);

    //The "serverConnection is used in the ctor of the Server.
    server = new Server(serverConnection);

Object Hierarchy

Once you have got a connection to your server - accessing databases is very simple. Most of the SMO objects are stored in a Parent/Child Collection ownership.
A Server has got a collection of databases (The databases Parent is the Server), a database has got a collection of Tables, a Table has got a collection of Columns.....

//this Code adds all known Databases to a Listview

//clean up the listview first.

//building the Columns
listView1.Columns.Add("# of Tables");

//iterate over all Databases
foreach( Database db in server.Databases )
    //add the Data to the listview.
    ListViewItem item = listView1.Items.Add(db.Name);
    item.SubItems.Add( db.Tables.Count.ToString() );

This code shows how to enlist Backup Devices:



foreach (BackupDevice backupDevice in server.BackupDevices)
    ListViewItem item = listView1.Items.Add(backupDevice.Name);

Create a New Database

Of course - we are not limited to getting information about our SQL Server - we can also create, drop and alter objects. Most SMO objects have 2 requirements - a valid (unique) Name and a valid Parent.

database.Name = dbName.Text;
database.Parent = server;

You see - SMO uses really compact code Smile | :) Now - let's create a backup device.

backupDevice.Parent = Server;
backupDevice.Name = "myBackupDevice";
backupDevice.PhysicalLocation = @"C:\myNewBackupDevice.bak";
backupDevice.BackupDeviceType = BackupDeviceType.Disk;

Scripting with T-SQL!

In some cases, you might want to have a T-SQL script of an operation. Let's take the example from above - we want a script for adding a Backup Device to our SQL Server.

backupDevice.Parent = Server;
backupDevice.Name = "myBackupDevice";
backupDevice.PhysicalLocation = @"C:\myNewBackupDevice.bak";
backupDevice.BackupDeviceType = BackupDeviceType.Disk;
StringCollection strings =  backupDevice.Script();
// strings [0] = "EXEC master.dbo.sp_addumpdevice  @devtype = N'disk',
// @logicalname = N'myBackupDevice', @physicalname = N'C:\myNewBackupDevice.bak'"

Doing a Backup

Finally, I want to show you how to do a backup of your database. Note that the class Backup doesn't represent a BackupDevice - it represents a "Backup Operation".

Backup backup = new Backup();
//we assume that there is a Logical Device with the Name "myBackupDevice"
backup.Devices.AddDevice("myBackupDevice", DeviceType.LogicalDevice);
backup.Database = "Master";

Additional Features

The functional range of SMO is amazing!
SMO supports really everything you will need.
Stored Procedures,
Full Text Catalogues,
HTTP Protocol,
Asymmetric Encryption,

In short:
Everything you desire Smile | :)
And if you understand the basics of a specific feature, you won't have problems to implement it with SMO.


  • 04/11/06 - First version of article
  • 04/12/06 - Fixed wrong requirements (thx to akosows) - Added hint for SQL-DMO
  • 05/31/06 - Minor changes
Thank you for reading my article, comments and critiques are very welcome!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Thomas Haller
Software Developer (Senior)
Austria Austria
Thomas, living in austria, is a Softwaredeveloper focused on C# and MS Sql Server since 2004. He started software development at the age of 8 with Basic for Amiga 500, without great success, due his lack of english skills...
But the enthusiasm for software engeneering came back at the age of 15, and is still there Smile | :)
Nowadays, at the age of 30, Thomas is very fascinated about the possibilities of neural Networks, and is studying this at the university in addition to his regular work. (Blended Learning)

You may also be interested in...

Comments and Discussions

GeneralMy vote of 3 Pin
AFMatambo20-Jan-11 7:18
memberAFMatambo20-Jan-11 7:18 
GeneralPlease let me kow the procedure how to implement it Pin
shavil6-May-10 21:10
membershavil6-May-10 21:10 
Generali am using EZManage SQL Pro to manage and backup all my sql server Pin
itayl27-May-09 3:13
memberitayl27-May-09 3:13 
Try EZmanage SQL Pro for better mssql backup

EZManage SQL Pro provids 5 time faster sql backup using VDI
GeneralLicense of SMO Pin
lorecasas17-May-08 7:59
memberlorecasas17-May-08 7:59 
GeneralRe: License of SMO Pin
alhambra-eidos6-Oct-08 21:35
memberalhambra-eidos6-Oct-08 21:35 
GeneralRe: License of SMO Pin
Lambin6-Apr-09 20:12
memberLambin6-Apr-09 20:12 
GeneralServer for Backup Pin
lorecasas17-May-08 7:56
memberlorecasas17-May-08 7:56 
GeneralDropping Stored Procs Pin
blacklocist18-Oct-07 8:40
memberblacklocist18-Oct-07 8:40 
GeneralRe: Dropping Stored Procs Pin
blacklocist18-Oct-07 8:45
memberblacklocist18-Oct-07 8:45 
GeneralRe: Dropping Stored Procs Pin
blacklocist18-Oct-07 9:58
memberblacklocist18-Oct-07 9:58 
GeneralRe: Dropping Stored Procs Pin
Thomas Haller18-Oct-07 10:17
memberThomas Haller18-Oct-07 10:17 
GeneralRe: Dropping Stored Procs Pin
blacklocist19-Oct-07 4:33
memberblacklocist19-Oct-07 4:33 
GeneralRe: Dropping Stored Procs Pin
Thomas Haller19-Oct-07 7:56
memberThomas Haller19-Oct-07 7:56 
GeneralError running demo Pin
MarkChimes25-Feb-07 23:23
memberMarkChimes25-Feb-07 23:23 
GeneralRe: Error running demo Pin
MarkChimes25-Feb-07 23:38
memberMarkChimes25-Feb-07 23:38 
GeneralFind Relation between two table Pin
atefeh mokhtary26-Nov-06 20:03
memberatefeh mokhtary26-Nov-06 20:03 
GeneralSql Transaction Pin
valamas_salmat19-Nov-06 14:35
membervalamas_salmat19-Nov-06 14:35 
GeneralProblems to create a DB [modified] Pin
Dark_leon11-Jun-06 23:16
memberDark_leon11-Jun-06 23:16 
GeneralRe: Problems to create a DB Pin
Dark_leon11-Jun-06 23:47
memberDark_leon11-Jun-06 23:47 
GeneralRequirements on System Pin
akosows11-Apr-06 22:25
memberakosows11-Apr-06 22:25 
GeneralRe: Requirements on System Pin
Thomas Haller12-Apr-06 3:14
memberThomas Haller12-Apr-06 3:14 
GeneralRe: Requirements on System Pin
alhambra-eidos6-Oct-08 21:40
memberalhambra-eidos6-Oct-08 21:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1509028.1 | Last Updated 31 May 2006
Article Copyright 2006 by Thomas Haller
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid