Data Points

SQL Server Management Objects

John Papa

Code download available at: Data Points 2007_06.exe(173 KB)

Contents

SMO Essentials
Listing the Servers
Connecting to a Server
Listing Databases
Issuing a DDL or DML Command
Iterating Lists of Objects
Backing Up a Database
Restoring a Database
Verifying a Database Backup
Wrapping Up

Not only must database developers query and manipulate data, but they must also perform administrative tasks on a regular basis. SQL Server™ Management Objects (SMO) offer developers a robust toolset for operations such as backing up and restoring databases, and issuing Data Definition Language (DDL) commands. Using SQL SMO you can also connect to SQL Server, iterate through a collection of database objects and perform a variety of tasks against them.

In this column, I discuss how SMO can be used to examine a database’s objects and to perform a variety of administrative tasks. Specifically, I cover topics such as how to design your project to use SMO and how to connect to a server. I also walk through a sample application that issues DDL or Data Manipulation Language (DML) commands against the database. Finally, I demonstrate how to use SMO to perform database backups, restores, and backup verification.

SMO Essentials

SMO is a Microsoft® .NET Framework library of objects that let you manage one or more database servers. It is designed to work with the new features that SQL Server 2005 introduced, but it will also connect to SQL Server 2000 databases with support for their respective legacy features. However, some of the SMO classes, properties and methods only work with SQL Server 2005 to support the new features like SQL Service Broker and snapshot isolation.

While the COM-based SQL Distributed Management Objects (SQL-DMO) still ships with SQL Server 2005, it has not been updated to include direct access to the newest SQL Server 2005 features as has SMO. When designing a .NET application that will manage a SQL Server database, it is recommended that you choose SQL SMO over using COM interop with DMO. You should also consider upgrading applications that currently use DMO to instead use SMO.

You can install SMO during the installation of Microsoft® SQL Server 2005 by checking the option to install the Client Tools. Then, when creating a project that will use SMO, you first reference the appropriate assemblies. The two main DLLs you must reference are Microsoft.SqlServer.Smo.dll and Microsoft.SqlServer.ConnectionInfo.dll. The former contains the core SMO classes (such as Database, Server, Backup, and Restore) while the latter contains the library of classes that help manage the connections to the SQL Server objects. In many cases, you’ll also need to reference Microsoft.SqlServer.SqlEnum.dll, which mostly contains useful enumerations, and Microsoft.SqlServer.SmoEnum.dll, which provides the Urn class for programmatic access to Uniform Resource Name (URN) addresses that uniquely identify Microsoft SQL Server objects.

Listing the Servers

Using SMO you can retrieve a list of available SQL Server installations on a network. You can specify arguments that control whether to search the network for SQL Server or to just search locally. The SmoApplication class exposes a static method named EnumAvailableSqlServers. When passed a Boolean value of true, the method will only search for and return all local servers. Passing a value of false to this method will cause it to search for all available servers on the network. The following code will get a list of SQL Server instances available on the network and return them to an ADO.NET DataTable:

DataTable dtServers = SmoApplication.EnumAvailableSqlServers(false);
foreach (DataRow row in dtServers.Rows)
{
  string sqlServerName = row[“Server”].ToString();
  if (row[“Instance”] != null && row[“Instance”].ToString().Length > 0)
      sqlServerName += @”\” + row[“Instance”].ToString();
}

It then iterates through the rows of the DataTable and formats the name of the SQL Server and its instance (if instances exist). Unfortunately, this method can be unreliable as it relies on UDP broadcasts combined with a timeout, so you may have inconsistent or incomplete results.

Connecting to a Server

The next several examples are all part of the application that is available for download or for browsing on the MSDN® Magazine Web site. When building an application with SMO, one of the first steps is often to connect to a SQL Server instance. There is nothing out of the ordinary here; establishing a connection requires that you pass the name of the SQL Server (and its instance name if multiple instances exist) and the login credentials. If you want to connect using Windows® Authentication, you can specify this using SMO. However, if you want to connect using SQL Authentication, you must pass a valid login name and password.

Figure 1 shows the sample application where a user can enter the name of a SQL Server instance and his credentials. In this figure, I entered the name of an instanced SQL Server and told it to use Windows Authentication to connect to the server. When I clicked the connect button, the list of databases was populated.

Figure 1** Connecting to a Server via SMO **(Click the image for a larger view)

I created a class called SMOHelper in the sample application to assist with some of the common routines that my application will use. An instance of the SMOHelper class can be used to interact with a single SQL Server. It has a constructor that accepts the information required to connect to a server either through Windows or SQL Server Authentication. Figure 2 shows the SMOHelper constructor grabbing and setting the appropriate class properties, which will be used by the methods of this class to perform database operations.

Figure 2 Accepting the Server Connection Information

public SMOHelper(string serverAndInstanceName, 
      string userName, string password, 
      bool useWindowsAuthentication) 
{
  this.server = new Server();
  int slashPos = serverAndInstanceName.IndexOf(‘\\’);
  if (slashPos >= 0)
  {
    this.serverName = serverAndInstanceName.Substring(0, slashPos);
    this.instance = serverAndInstanceName.Substring(slashPos + 1);
  }
  else
  {
    this.serverName = serverAndInstanceName;
    this.instance = string.Empty;
  }
  this.userName = userName;
  this.password = password;
  this.useWindowsAuthentication = useWindowsAuthentication;
}

A connection to a server is made by creating an instance of the Server class and setting its ConnectionContext class’s properties. The code in Figure 3 sets the connection based upon the type of authentication that the user selected on the form. While you can explicitly call the ConnectionContext’s Connect method, this isn’t actually necessary, as SMO automatically takes and releases connections from a pool. See blogs.msdn.com/mwories/archive/2005/05/02/smoconnections.aspx for more information.

Figure 3 Connecting to a Server

public void Connect()
{
    server.ConnectionContext.ServerInstance = ServerAndInstanceName; 
    server.ConnectionContext.LoginSecure = useWindowsAuthentication;
    if (!this.useWindowsAuthentication)
    {
        server.ConnectionContext.Login = this.userName;
        server.ConnectionContext.Password = this.password;
    }
    // server.ConnectionContext.Connect();
}

Listing Databases

Once connected to the server, the list of databases on that server can be retrieved through the Server object’s Databases property. In my sample application, I used data binding to bind the list of databases on the server to a combobox control. I iterated through the DatabaseCollection and created a List<string> to contain the names of the databases. This list is then bound to the combobox control where the user can select a database and perform an action on it. The following code snippet loops through the databases and builds the list of name strings:

public List<string> GetDatabaseNameList()
{
    List<string> dbList = new List<string>();
    foreach (Database db in server.Databases) 
        dbList.Add(db.Name);
    return dbList;
}

In the code snippet shown above, I only wanted a list of database names, so I simply created a List<string>. However, the combobox control could just as easily have been bound to a list of a custom classes (like List<MyDatabase>) that wrap the functionally rich objects that are typically returned from many SMO methods.

Issuing a DDL or DML Command

Like ADO.NET, SMO can be used to execute commands on a database to retrieve or modify data using standard DML. While SMO is not optimized to handle complex DML, it can be used to perform simple DML statements.

The form in Figure 1 shows a textbox where a user can enter a DML or DDL statement and then execute it. If the query should retrieve a rowset (that is, it is a SELECT statement), SMO will retrieve the rowset via its ExecuteWithResults method. Otherwise, if the command does not return a rowset (INSERT, UPDATE, or DELETE) SMO will execute the command using its ExecuteNonQuery method. The following code sample executes a query and returns the rowset back into an ADO.NET DataSet. The DataSet is then bound to a DataGridView on the form:

  DataSet ds = db.ExecuteWithResults(txtSQL.Text);
  dgvResults.DataSource = ds.Tables[0];

The next code snippet will execute a non-result-returning command against the database object:

  db.ExecuteNonQuery(sql);

The commands that you use do not have to be DML commands, they can also be DDL commands. For example, you can execute an ALTER TABLE, CREATE PROCEDURE, RESTORE or even DBCC CHECKDBB command using the ExecuteNonQuery method. For example, the following command could be used to create a Person table:

    string sql = “CREATE TABLE Person “ +
       “(PersonID INT NOT NULL PRIMARY KEY,
       FullName VARCHAR(100))”;
    db.ExecuteNonQuery(sql);

Iterating Lists of Objects

SMO can also be used to locate a specific database object and perform a context-sensitive operation on it. SMO’s Database class exposes a series of collections of each of the objects that it contains. Figure 4 shows a partial list of the different collections that the Database class exposes.

Figure 4 Collections

Figure 5 shows the example application displaying all of the Tables, Stored Procedures and Users in the Northwind database on my development computer. Getting a list of the tables that exist in a specific database is easy enough. Once you have the Server object and you get the Database object from the Server object you want, you can access the TablesCollection through the Tables property. Figure 6 shows how to access the Database’s collections of objects, loop through them, and build a custom object of your own. In this code, I simply grab the name of the object and put it in a List<string>, which I then use to bind to the listbox controls on the SmoLists Form (shown in Figure 5).

Figure 6 Retrieving Lists

public List<string> GetTableNameList(Database db)
{
    List<string> tableList = new List<string>();
    foreach (Table table in db.Tables) tableList.Add(table.Name);
    return tableList;
}

public List<string> GetStoredProcedureNameList(Database db)
{
    List<string> storedProcedureNameList = new List<string>();
    foreach (StoredProcedure storedProcedure in db.StoredProcedures)
        storedProcedureNameList.Add(storedProcedure.Name);
    return storedProcedureNameList;
}

public List<string> GetUserNameList(Database db)
{
    List<string> userNameList = new List<string>();
    foreach (User user in db.Users) userNameList.Add(user.Name);
    return userNameList;
}

Figure 5** Database Lists **(Click the image for a larger view)

Of course, there are many more properties and methods contained within these objects besides the Name property. For example, you can also execute methods such as Revoke or Grant on a Table object that will set permissions for the Table.

Backing Up a Database

One of the more common tasks that developers use SMO for is to backup a database. SMO makes it relatively simple to perform a database backup using its Backup class. Figure 7 shows the sample Form that will allow you to back up a database and then restore it.

Figure 7** Backup and Restore **(Click the image for a larger view)

When you run the application and click the Backup button, you may notice the progress bar. I added a ProgressBar control to the Form in order to give the user some feedback on the status of the backup or restore operation. The SMO Backup class exposes some events that can be used to help track the progress of a backup. (I will examine these momentarily.)

When a user clicks the Backup button, the btnBackup_Click event handler executes, as you see here:

private void btnBackup_Click(
   object sender, EventArgs e)
{
   pbDBAction.Maximum = 100;
   pbDBAction.Style = ProgressBarStyle.Blocks;
   pbDBAction.Value = 0;
   pbDBAction.Step = 10;
   string databaseName = cboDatabase.SelectedValue.ToString();
   smoHelper.BackupDatabase(databaseName);
}

First, I set the Progressbar control’s properties so that it will appear as a set of blocks and so that it will start out empty. Next, I grab the name of the database from the selection in the combobox control and pass that to the BackupDatabase method that I created in Figure 8 for the SMOHelper class.

Figure 8 Backing Up a Database

public void BackupDatabase(string databaseName)
{
    Console.WriteLine(“*** Backing up ***”);

    Backup backup = new Backup();
    backup.Action = BackupActionType.Database;
    backup.Database = databaseName;
    backup.Incremental = false;
    backup.Initialize = true;
    backup.LogTruncation = BackupTruncateLogType.Truncate;

    string fileName = string.Format(“{0}\\{1}.bak”, 
      testFolder, databaseName);
    BackupDeviceItem backupItemDevice = 
      new BackupDeviceItem(fileName, DeviceType.File);
    backup.Devices.Add(backupItemDevice);
    backup.PercentCompleteNotification = 10;
    backup.PercentComplete += backup_PercentComplete;
    backup.Complete += backup_Complete;

    backup.SqlBackup(server);
}

In BackupDatabase, I first create an instance of the Backup class. Then I set the Action property to the BackupActionType.Database enumeration value (you can also backup Logs and Files). Of course, I then set the name of the database to backup and set a handful of other properties. Since I want a full database backup, I instruct the backup not to be an incremental backup by setting the Incremental property to false.

When backing up a database, you need to specify the file path location and filename of where you want to create the backup file. In my code, I put all backups in the same folder for simplicity (see the testFolder variable). Once I choose where to create the backup file, I create a BackupDeviceItem and add it to the Backup class’s Devices list. I chose to save the backup as a file, but you can save the backup to other destinations such as a LogicalDevice or a Tape as well.

I added an event handler to the PercentComplete event so that the backup process would notify the calling Form and its ProgressBar control. This event occurs at an interval defined by the PercentCompleteNotification property. In my sample application, this is what makes the ProgressBar control show progress. I also added an event handler to the Complete event so that the Form can be notified when the backup has completed. (See the sample application for details on how the events are hooked together.)

Finally, I issue the SqlBackup method, which is what actually performs the database backup task. Keep in mind that backing up a database may take longer than you expect depending on a number of factors, including the size of the database and its contents.

Restoring a Database

SMO exposes a Restore class that can be used to restore a database from a file or from another backup device. In the sample application, I restore the database that we just backed up, and the process is similar. Note that I don’t restore it to the database from where it was backed up but rather I restore the database to a new file name and a new logical name. In essence, this creates a copy of the database using backup methods. Of course, you can choose to restore a database over the existing database, but this process will come in handy.

The code that appears in Figure 9 displays the RestoreDB method that I use in my sample application to restore the database. My first step is to create an instance of the Restore class and then I set the name of the backup file that I will be restoring from.

Figure 9 Restoring a Database

public void RestoreDB(string databaseName)
{
    Restore restore = new Restore();
    string fileName = string.Format(“{0}\\{1}.bak”, 
        testFolder, databaseName);
    restore.Devices.Add(new BackupDeviceItem(fileName, DeviceType.File));

    // Just give it a new name
    string destinationDatabaseName = 
        string.Format(“{0}_newly_restored”, databaseName);

    // Go grab the current database’s logical names for the data 
    // and log files. For this example, we assume there are 1 for each.
    Database currentDatabase = server.Databases[databaseName];
    string currentLogicalData = 
        currentDatabase.FileGroups[0].Files[0].Name;
    string currentLogicalLog = currentDatabase.LogFiles[0].Name;

    // Now relocate the data and log files
    RelocateFile reloData = 
        new RelocateFile(currentLogicalData, 
        string.Format(@”{0}\{1}.mdf”, testFolder, 
            destinationDatabaseName));
    RelocateFile reloLog = 
        new RelocateFile(currentLogicalLog, 
        string.Format(@”{0}\{1}_Log.ldf”, testFolder, 
            destinationDatabaseName));
    restore.RelocateFiles.Add(reloData);
    restore.RelocateFiles.Add(reloLog);

    restore.Database = destinationDatabaseName;
    restore.ReplaceDatabase = true;

    restore.PercentCompleteNotification = 10;
    restore.PercentComplete += restore_PercentComplete;
    restore.Complete += restore_Complete;
    restore.SqlRestore(server);
}

Next, I create a new name for the database that I will restore (Northwind_newly_restored) and I get the names of the logical data and log files from the FileGroups and the Files collections. I need these names so that I can relocate the physical files for the backup to a new name (since I am restoring the database to a new and separate database). I then add an instance of the RelocateFile class (one for each file that I am relocating) to the restore object’s RelocateFiles collection.

I set the name of the database to restore and tell SMO to replace the existing database, if one already exists, with the same name. Since I want the ProgressBar to show the progress of the restore operation, I set the PercentComplete and Complete event handlers accordingly.

Verifying a Database Backup

It won’t do you much good to have a database backup if it is corrupted. Verifying a backup is a good idea, and can easily be done by creating an instance of the Restore class, setting the DeviceType to its appropriate medium, adding the device and executing the SqlVerify method. This will return a Boolean value indicating whether or not the backup is verified. The code below demonstrates how to perform this verification:

Restore restore = new Restore();
restore.DeviceType = DeviceType.File;
restore.Devices.Add(@”c:\MySMOTest.bak”);
boolean verified = restore.SqlVerify(server,out msg);

Wrapping Up

SMO makes it easy to perform database administration operations in .NET code. While all of these tasks can be performed with T-SQL directly on a database or through the SQL Server Management Studio, being able to use SMO offers broad flexibility to include these types of operations in .NET applications. In fact, SQL Server Management Studio relies on SMO for most of its management tasks.

Send your questions and comments for John to mmdata@microsoft.com.

John Papa is a professional consultant, trainer and mentor using Microsoft .NET technologies. A Microsoft C# MVP, John has authored several articles and books on architecture and data access technologies including ADO.NET, XML, and SQL Server. He is also a frequent speaker at industry conferences such as VSLive. Get in touch with John at www.johnpapa.net.