Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using SQL Management Objects to create and restore SQL Databases.

By Amriteshwar Singh,

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.

Total article views: 5185 | Views in the last 30 days: 3
 
Related Articles
FORUM

sql script to find which user started restore and stopped while database was restoring in sql server 2008.

sql script to find which user started restore and stopped while database was restoring in sql server...

FORUM

sql script to find which user started restore and stopped while database was restoring in sql server 2008

sql script to find which user started restore and stopped while database was restoring in sql server...

FORUM

connect Database server

connect Database server

FORUM

Restoring database from another server

Restoring database backed up from another server

Tags
backup / restore    
smo    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones