SQL Management Objects
SQL Management Objects (SMO) is a new set of programming objects that expose the management functionalities of the SQL Server Database. In simpler terms, with the help of SMO objects and their associated methods/attributes/functionalities we can automate SQL Server tasks which can range from

1. Administrative Tasks - Retrieval of Database Server Settings, creating new databases Etc.
2. Application Oriented Task - Applying T-SQL Scripts
3. Scheduling Tasks - Creation of new Jobs, Running and maintenance of existing Jobs via SQL Server Agent.

SMO is also an upgraded version of SQL-DMO (Database Management Objects) which came along earlier versions of SQL Server. Even the SQL Management Studios is utilizes SMO, therefore in theory one should be able to replicate any function which can be performed with the help of SQL Management Studios.

Scope of the Article

The scope of this document is to explain the use of SMO dynamic linked assembly for creation and restoration of databases .This involves creating copies of databases from an existing database template backup (.bak).

Possible Applications

Such an approach to database creation can be made to effective use in scenarios such as:

1. Automated Database creation and Restoration:
   If a need arises to create 'N' number of database and restore them from a common database backup (.bak).
2. Creation of a new database.
3. Overwriting an existing database.

I would elaborate on the first application, creation of copies of databases from a common database backup. Below is a list of the implementation in detail:

Reference SMO
For us to make use of the builtin functionality that SMO objects offer we have to refer the SMO assemblies via a .Net application. This can be done by adding via the Visual Studios 2005 by browsing to, Project ->Add Reference Tab and adding the following.

- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Smo
- Microsoft.SqlServer.SmoEnum
- Microsoft.SqlServer.SqlEnum

Also include the following in the code editor of your Visual Studios 2005 project

Using Microsoft.SqlServer.Management.Smo;

The Configurable Values which are required are:
- Server Name -Name of the Server which you want to connect to and restore the
databases at.
- User Name and Password in case of SQL Authentication
- Name of the Database
- Data File Path of the Database - By default it will be stored under SQL Server Directory.
- Log File Path of the Database - By default it will be stored under SQL Server Directory.

Step1: Connecting to the Server
This can be done either by using SQL authentication or by using the built in NT authentication depending upon your usage and application.

a) SQL Authentication

ServerConnection conn = new ServerConnection();

//Windows Authentication is False
 conn.LoginSecure = false;

//Specify the UserName
 conn.Login = <UserName>
 //Specify the Password
 conn.Password = <Password>

//Specify the Server Name to be connected
 conn.ServerInstance = <ServerName>;
 Server svr = new Server(conn);

//Connect to the server using the configured Connection 
 //String
 svr.ConnectionContext.Connect();

---

b) Windows Authentication:

ServerConnection conn = new ServerConnection();

//Windows Authentication is True
 conn.LoginSecure = true;

//Specify the Server Name to be connected
 conn.ServerInstance = <ServerName>;

 Server svr = new Server(conn);

 //Connect to the server using the configured Connection 
 //String
 svr.ConnectionContext.Connect();

---

Step2: Obtaining Information of the New Database to be Restored

a) Use Restore object to facilitate the restoration of the new Database. Specify the name of the New Database to be restored.

Restore res = new Restore();
 res.Database = <New DataBase Name> ;

---

b) Specify the location of the Backup file from which the new database is to be restored.

//Restore from an already existing database backup

res.Action = RestoreActionType.Database;
 res.Devices.AddDevice(<Location of the BackUp File>, 
 DeviceType.File);
 res.ReplaceDatabase = true;

//Data Table to obtain the values of the Data Files of 
 // the database.
 DataTable dt;
 dt = res.ReadFileList(svr);

//Obtaining the existing Logical Name and Log Name of 
 //the database backup
 string TemplateDBName = dt.Rows[0]["LogicalName"].ToString();
 string TemplateLogName = dt.Rows[1]["LogicalName"].ToString();

---

c) Specify the new location of the database files which with the relocate property.(This is similar to the restoration of a database from SQL Server Script with the help of WITH MOVE option

// Now relocate the data and log files
 //
 RelocateFile reloData = new RelocateFile(TemplateDBName, @"D:\" + <New Database Name> + 
 "_data.mdf");

 RelocateFile reloLog = new RelocateFile(TemplateLogName, @"D:\" + <New Database Name> + 
 "_log.ldf");

 res.RelocateFiles.Add(reloData);
 res.RelocateFiles.Add(reloLog);

---

d) Restore the Database. This will restore an exact replica of the database backup file which was specified.

//Restore the New Database File
 res.SqlRestore(svr);
 //Close connection
 svr.ConnectionContext.Disconnect();

---

Conclusion

Such an implementation of SMO for automated database restore can be put to use by a Database administrator for ease of use and customization.

Another possible utilization which i had implemented was to improve the efficiency of a data intensive operation .This was done by automating the restoration of multiple copies of the database. A real time scenario where such an approach can be made use of is to process bulk data with the help of staging databases. A particular data intensive operation was spilt across similar databases to process individual units of data and split the load.