Programming SQL Server 2005

Chapter 12. SQL Server Management Objects (SMO) Utility Classes

SMO utility classes perform specific tasks and are work independently of the SQL Server instance. SMO utility classes can be grouped according to their function:

• Database scripting operations

• Backup and restore databases

• Transfer schema and data between database instances

• Trace and trace replay operations

• Administering the Database Mail subsystem

The sections in this chapter describe these SMO utility classes and shows how to use them through programming examples.

There are three more SMO utility classes that are discussed in later chapters:

• SMO classes used to administer SQL Server Agent are covered in Chapter 16.

• SMO classes used to administer Service Broker are covered in Chapter 17.

• SMO classes used to administer Notification Services are covered in Chapter 18.

The examples in this section are all built using Visual Studio 2005. The examples need a reference to the following assemblies:

• Microsoft.SqlServer.ConnectionInfo

• Microsoft.SqlServer.Smo

Additional assembly references will be given for examples in which the assemblies are required.

Scripting

You can document a database schema by generating T-SQL scripts for the different objects. Possible uses for these scripts include the following:

• As a backup, allowing objects to be recreated if necessary

• To create development, testing, staging, and production environments

SQL Server Management Studio lets you script selected database objects. You can choose to use either a manual process ...