Table of contents
Introduction
When I was preparing for exam 70-433, I found that one objective was about Server Management Objects. When I passed the exam, I decided to continue my investigation of SMO. I picked up a lot of knowledge and it really made me so excited. Now I want to share my knowledge with others.
Before I will show examples of how to use SMO for various problems, I think it is appropriate to describe what Server Management Objects are. (People who are familiar with SMO can jump to the next section.)
Background
SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. All functions available in SQL Server Management Studio are available in SMO, but SMO includes several more features than Management Studio. SMO is a descendent of SQL Server Distributed Management Objects (SQL-DMO). SMO is easier to use than SQL-DMO and adds functionality to support the new features in SQL Server 2005.
SMO is compatible with SQL Server 2000 and SQL Server 7.0. This allows developers to create applications for various SQL Server versions. But there is one limitation. SMO can't be used for SQL Servers with compatibility level 60 or 65.
Connecting to SQL Server
When you want to connect to SQL Server 2005 (or later version), you can user SQL Server SMO. To get started with SMO, first you must add references in Visual Studio. In the Add Reference window, select:
Microsoft.SqlServer.SmoMicrosoft.SqlServer.SmoExtendedMicrosoft.SqlServer.Management.Sdk.SfcMircorost.SqlServer.ConnectionInfo
When the references are added, you must add two using statements for these namespaces:
Using Microsoft.SqlServer.Management.Common;
Using Microsoft.SqlServer.Management.Smo;
Now you can start working with SMO. Before doing restore and backup tasks, you must connect to the server. This connection is provided by the ServerConnection object. It lets you connect to the server and assign that connection to the Server object. The Server object represents an instance of SQL Server. In SMO programming, the Server object determines the connection to a physical SQL Server installation.
ServerConnection conn = new ServerConnection("INSTANCE",
"LOGIN", "PASSWORD");
try
{
Server srv = new Server(conn);
Console.WriteLine("Server: " + srv.Name);
Console.WriteLine("Edition: " + srv.Information.Edition);
conn.Disconnect();
}
catch (Exception err)
{
Console.WriteLine(err.Message);
}
This example shows you how to connect to SQL Server using a user name and password. Alternatively, you can use Windows Integrated Authentication:
ServerConnection conn = new ServerConnection();
conn.ServerInstance = "INSTANCE";
try
{
Server srv = new Server(conn);
Console.WriteLine("Server: " + srv.Name);
Console.WriteLine("Edition: " + srv.Information.Edition);
conn.Disconnect();
}
catch (Exception err)
{
Console.WriteLine(err.Message);
}
For disconnecting from SQL Server, we use the function Disconnect() of the ServerConnection class. The ServerConnection object is represented by Server.ConnectionContext, and alternatively you can disconnect from SQL Server by calling:
Server.ConnectionContext.Disconnect();
Working with databases
Enumerating databases, filegroups, and files
The Database property of the Server object represents a collection of Database objects. Using this collection, you can enumerate the databases on SQL Server.
Server srv = new Server(conn);
foreach (Database db in srv.Databases)
{
Console.WriteLine(db.Name);
foreach (FileGroup fg in db.FileGroups)
{
Console.WriteLine(" " + fg.Name);
foreach (DataFile df in fg.Files)
{
Console.WriteLine(" " + df.Name + " " + df.FileName);
}
}
}
Enumerating database properties
Database properties are represented by the Properties property of a Database object. Properties is a collection of Property objects. The following sample demonstrates how to get database properties:
Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
foreach (Property prop in database.Properties)
{
Console.WriteLine(prop.Name + " " + prop.Value);
}
Creating databases
With SMO, you can create databases. When you want to create a database, you must create the Database object. This example demonstrates how to create a database named MyNewDatabase and create a data file called MyNewDatabase.mdf (in the primary filegroup) and a log file named MyNewDatabase.log.
Database database = new Database(srv, "MyNewDatabase");
database.FileGroups.Add(new FileGroup(database, "PRIMARY"));
DataFile dtPrimary = new DataFile(database.FileGroups["PRIMARY"],
"PriValue", @"E:\Data\MyNewDatabase\MyNewDatabase.mdf");
dtPrimary.Size = 77.0 * 1024.0;
dtPrimary.GrowthType = FileGrowthType.KB;
dtPrimary.Growth = 1.0 * 1024.0;
database.FileGroups["PRIMARY"].Files.Add(dtPrimary);
LogFile logFile = new LogFile(database, "Log",
@"E:\Data\MyNewDatabase\MyNewDatabase.ldf");
logFile.Size = 7.0 * 1024.0;
logFile.GrowthType = FileGrowthType.Percent;
logFile.Growth = 10.0;
database.LogFiles.Add(logFile);
database.Create();
database.Refresh();
SMO allows you to set the Growth of a database and other properties. More about properties can be found on MSDN. When you want to drop a database, just call the Drop() method of the Database object.
Dababase backup
SMO allows you to backup databases very easily. For backup operations, you must create an instance of the Backup class and then assign the Action property to BackupActionType.Database. Now you have to add a device you want to backup to. In many cases, it is file. You can backup not only to file, but to tape, logical drive, pipe, and virtual device.
Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
Backup backup = new Backup();
backup.Action = BackupActionType.Database;
backup.Database = database.Name;
backup.Devices.AddDevice(@"E:\Data\Backup\AW.bak", DeviceType.File);
backup.PercentCompleteNotification = 10;
backup.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
backup.SqlBackup(srv);
SMO allows you to monitor the progress of a backup operation being performed. You can easily implement this feature. The first thing you must do is create an event handler with the PercentCompleteEventArgs parameter. This parameter includes the Percent property that contains the percent complete value. This value is an integer between 0 and 100.
static void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
{
Console.WriteLine(e.Percent);
}
Performing a log backup operation is similar to a database backup. Just set the Action property to Log instead of Database.
Dababase restore
SMO allows you to perform database restore easily. A database restore operation is performed by the Restore class which is in the Microsoft.SqlServer.Management.Smo.Restore namespace. Before running any restore, you must provide a database name and a valid backup file. Then you must set the Action property. To restore a database, set it to RestoreActionType.Database. To restore a log, just set it to RestoreActionType.Log. During restore, you can monitor the progress of the restoring operation. This could be done the same way as in the case of database backup.
Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
Backup backup = new Backup();
Restore restore = new Restore();
restore.Action = RestoreActionType.Database;
restore.Devices.AddDevice(@"E:\Data\Backup\AW.bak", DeviceType.File);
restore.Database = database.Name;
restore.PercentCompleteNotification = 10;
restore.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
restore.SqlRestore(srv);
Database tables
In SMO, tables are represented by the Table object. In SMO objects hierarchy, the Table object is below the Database object.
Enumerating tables and columns
This example demonstrates how to enumerate all the tables and columns of the AdventureWorks database.
Server srv = new Server(conn);
Database db = srv.Databases["AdventureWorks"];
foreach (Table table in db.Tables)
{
Console.WriteLine(" " + table.Name);
foreach (Column col in table.Columns)
{
Console.WriteLine(" " + col.Name + " " + col.DataType.Name);
}
}
Creating and removing tables
The following example shows how to create a database table. It creates MyFirstSMOTable in the MyNewDatabase database. The table contains two columns (ID and Name) and a Primary Key index on the first column.
Database myNewDatabase = srv.Databases["myNewDatabase"];
Table myFirstSMOTable = new Table(myNewDatabase, "MyFirstSMOTable ");
DataType dt = new DataType(SqlDataType.Int);
Column idColumn = new Column(myFirstSMOTable, "ID", dt);
idColumn.Nullable = false;
idColumn.Identity = true;
idColumn.IdentityIncrement = 1;
idColumn.IdentitySeed = 1;
myFirstSMOTable.Columns.Add(idColumn);
dt = new DataType(SqlDataType.VarChar, 50);
Column nameColumn = new Column(myFirstSMOTable, "Name", dt);
myFirstSMOTable.Columns.Add(nameColumn);
myFirstSMOTable.Create();
Index primaryKey = new Index(myFirstSMOTable, "PK_ID");
IndexedColumn indexedColumn = new IndexedColumn(primaryKey, "ID");
primaryKey.IndexedColumns.Add(indexedColumn);
primaryKey.IndexKeyType = IndexKeyType.DriPrimaryKey;
primaryKey.Create();
In this example, the Table object is instantiated and two parameters are passed into the constructor of this class. The first parameter is a database represented by the myNewDatabase object of the Database class. The second parameter is the name of the table to be created. The columns of the table are created by instantiating the Column object. Before creating a column object, it is important to define the data type of the newly created column. The data type is represented by the DataType object. Next, this DataType object is passed into the constructor of the Column object. The constructor of the Column object has two other parameters: table and name of column.
In this example, two columns are created and a Primary Key index. For creating Primary Key index, serves the Index object with two parameters in the constructor. First is the table and the second, the ID name of the index. The IndexedColumn object is important to be created with two parameters in the constructor (index and name of column).
Checking table integrity
The following example demonstrates how to check the integrity of all the tables and their indexes in the database. In this example, we call the method CheckIntegrity() with a single parameter. This parameter specifies the RepairType.
Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
StringCollection sc = database.CheckTables(RepairType.None);
foreach (object o in sc)
{
Console.WriteLine(o.ToString());
}
Views
In SQL Server Management objects, Views are represented by a View object.
Enumerating Views
Server srv = new Server(conn);
Database db = srv.Databases["AdventureWorks"];
foreach (View view in db.Views)
{
}
Creating and removing Views
In the following example, a view is created in text mode. In this case, the TextHeader property must be set. This property gets or sets the header part of the string that defines a View. The body of a View is represented by the TextBody property. Other properties that need mention are IsSchemaBound which gets or sets the value that specifies whether a schema is bound to the View, and IsEncrypted that specifies whether a View is encrypted. When a View is created and IsEncrypted is true, the definition of the View is stored in an obfuscated format. When IsSchemaBound is true, Views and tables participating in a View clause cannot be dropped unless that View is dropped or changed so that it no longer has schema binding.
View myview = new View(myNewDatabase, "My_SMO_View");
myview.TextHeader = "CREATE VIEW [My_SMO_View] AS";
myview.TextBody = "SELECT ID, NAME FROM MyFirstSMOTable";
myview.Create();
This example shows how to create a View in MyNewDatabse. The newly created View is named My_SMO_View and it selects all the rows from the table MyFirstTable.
Stored Procedures
The StoredProcedure object represents SQL Server Stored Procedures.
Enumerating Stored Procedures
Stored Procedures are represented by the StoredProcedures property of the Database object. The following example demonstrates how to enumerate Stored Procedures of a selected database (in this case, AdventureWorks).
Server srv = new Server(conn);
Database db = srv.Databases["AdventureWorks"];
foreach (StoredProcedure sp in db.StoredProcedures)
{
}
Creating a Stored Procedure
When you want to create a Stored Procedure, the TextBody property must be set to the T-SQL script that defines the Stored Procedure. Sometimes developers create a Stored Procedure with parameters. Parameters of the Stored Procedure are represented by the Parameters property of the StoredProcedure object. The Parameters property is a collection of StoredProcedureParameter objects. All parameters require the "@" prefix in the Stored Procedure parameter name.
Database myNewDatabase = srv.Databases["myNewDatabase"];
StoredProcedure sp = new StoredProcedure(myNewDatabase, "spRowByID_Get");
sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = false;
StoredProcedureParameter param;
param = new StoredProcedureParameter(sp, "@ID", DataType.Int);
sp.Parameters.Add(param);
string spBody = "SELECT * FROM MyFirstSMOTable WHERE ID=@ID";
sp.TextBody = spBody;
sp.Create();
In this example, a Stored Procedure named spRowByID_Get was created with an input parameter @ID. When you want to alter an existing Stored Procedure, just create a StoredProcedure object, set all the properties you need to change, and call the Alter() method.
Database myNewDatabase = srv.Databases["myNewDatabase"];
StoredProcedure sp = myNewDatabase.StoredProcedures["spRowByID_Get"];
sp.Alter();
When you want to drop an existing Stored Procedure, just call the Drop() method of the StoredProcedure object.
Triggers
Before talking about the code part, it is important to tell something about triggers. Triggers are a special kind of Stored Procedure that respond to special events. SQL Server offers two types of Triggers: Data Definition Language (DDL) Triggers and Data Manipulation Language (DML) Triggers.
DDL Triggers fire in response to a change in the structure of a database (when CREATE, ALTER, or DROP statements are executed).
Creating a DML Trigger
DML Triggers fire in response to changing of data (when INSERT, UPDATE, or DETELE statements are executes). DML Triggers are represented by a Trigger object. One of the main properties of the Trigger object is TextBody. This property represents code that runs when a Trigger is fired. The type of Trigger is set by the Insert, Update, or Delete properties.
Database myNewDatabase = srv.Databases["myNewDatabase"];
Table myFirstSMOTable = myNewDatabase.Tables["myFirstSMOTable"];
Trigger trigger = new Trigger(myFirstSMOTable, "SMOTrigger");
trigger.TextMode = false;
trigger.Insert = true;
trigger.Update = true;
trigger.Delete = false;
trigger.InsertOrder =
Microsoft.SqlServer.Management.Smo.Agent.ActivationOrder.First;
trigger.TextBody = " RAISERROR('MESSAGE',16,10) "; ;
trigger.ImplementationType = ImplementationType.TransactSql;
trigger.Create();
In this example, a Trigger named SMOTrigger is created. This trigger fires when an Insert or Update statement is executed. DML Triggers are bound to a table. In this case, the trigger is bound with MyFirstSMOTable and when one of the mentioned statements (INSERT or UPDATE) occurs, the Trigger fires.
Creating a DDL Trigger
Another type of Triggers is DDL Triggers. In SMO, this type is represented by two objects: ServerDdlTrigger and DatabaseDdlTrigger. Here is an example of DatabaseDdlTrigger:
Database myNewDatabase = srv.Databases["myNewDatabase"];
DatabaseDdlTrigger databaseTrigger =
new DatabaseDdlTrigger(myNewDatabase,"SMODatabaseTrigger");
databaseTrigger.TextHeader =
"CREATE TRIGGER SMODatabaseTrigger ON DATABASE FOR DROP_TABLE AS";
databaseTrigger.TextBody =
"PRINT 'You can not delete table!' ; ROLLBACK "; ;
databaseTrigger.ImplementationType = ImplementationType.TransactSql;
databaseTrigger.ExecutionContext = DatabaseDdlTriggerExecutionContext.Caller;
databaseTrigger.Create();
In this example, a DDL trigger named SMODatabaseTrigger in the database MyNewDatabase is created. This Trigger fires when someone tries to execute a Drop table statement.
Schemas
In SQL Server 2005, Microsoft introduced the concept of database schemas as opposed to object owners. A schema is a container of objects distinct from the users who created those objects.
Schema in SMO is represented by the Schema object. The Schema object represents an ownership context for database objects.
Enumerating schemas
You can enumerate all schemas of a database using the Schemas property of the Database object. The Schemas property is an ordinary collection of Schema objects.
Database myNewDatabase = srv.Databases["myNewDatabase"];
foreach (Schema schema in myNewDatabase.Schemas)
{
Console.WriteLine(schema.Name);
}
Creating schemas
The following example demonstrates how to create a new database table named MySecondSMOTable in the newly created NewSchema schema:
Database myNewDatabase = srv.Databases["myNewDatabase"];
Schema newSchema = new Schema(myNewDatabase, "NewSchema");
newSchema.Owner = "dbo";
newSchema.Create();
Table mySecondSMOTable = new Table(myNewDatabase,
"MySecondSMOTable","NewSchema");
DataType dt = new DataType(SqlDataType.Int);
Column idColumn = new Column(mySecondSMOTable, "ID", dt);
idColumn.Nullable = false;
idColumn.Identity = true;
idColumn.IdentityIncrement = 1;
idColumn.IdentitySeed = 1;
mySecondSMOTable.Columns.Add(idColumn);
dt = new DataType(SqlDataType.VarChar, 50);
Column nameColumn = new Column(mySecondSMOTable, "Name", dt);
mySecondSMOTable.Columns.Add(nameColumn);
mySecondSMOTable.Create();
History
- 12 Nov. 2010 - Article created.
- 13 Nov. 2010 - Content posted.
