SQL Server Management Objects 2008 (SMO) New Features

MSDN defines SMO as - SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. In this article we will practically explore some features of SQL Server Management Objects.

To start with, let’s create a ‘Windows Application’ using Visual Studio and name the application as ‘SMOExamples’ as shown below –

Now let’s add the following references to our project.

1. Microsoft.SqlServer.ConnectionInfo.dll
2. Microsoft.SqlServer.Smo.dll
3. Microsoft.SqlServer.SmoExtended.dll
4. Microsoft.SqlServer.SqlEnum.dll
5. Microsoft.SqlServer.Management.Sdk.Sfc.dll

All these references can be found in the path – ‘C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies’.

Now let’s design our windows form as shown below –

Declare an object which will make a connection to our SQL Server instance. For this, we will use a class called ‘Server’ as shown below –

Server srv = new Server(Environment.MachineName);

Now to test the connection to the SQL Server, let’s write the following code on the Form Load event as shown below –

Now let’s create a Database and Table with the name ‘SMOExample’ and ‘SMOTable’ respectively by writing the following code on the click event of ‘Create Database And Table’ button –

For creating a database, we have used a class called ‘Database’ which takes two parameters in the constructor. First one is the SQL Server instance in which we have to create a database. Second is the database name.

Now let’s create a script for all the tables available in our database. Write bee following code on the click event of ‘Generate Script’ button –

For generating the script files we are using a ‘Scripter’ class.

Now let’s write the code to take a backup of the complete database. Write the following code on the click event of ‘Backup Database’ button –

To take a backup of the database, we are using a ‘Backup’ class and to decide what will be the device of backup, we are using ‘BackupDeviceItem’ class.

Now the last thing we will explore is how to verify the backup, which is taken before we restore it to another server/same server. Write the following code on the ‘Verify Backup’ button –

Now run your project. This will show you the date and time created for ‘AdventureWorks’ database as shown below –

Now let’s click the button ‘Create Database and Table’ and observe the results in SQL Server Management
 Studio Object Explorer –



Now click on the button ‘Generate Script’. Go to ‘C:\’ and verify the script file –

Finally click the button ‘Backup Database’ and check your ‘C:\’ drive. Your backup file should have been created. Similarly click on ‘Verify Backup’ button and it will show you a success message.

Summary – In this article we saw few capabilities of SQL Server Management Objects (SMO).

Download the source code