Using SQL DMO for automated Database Backup and Restore
SQL DMO or SQL Distributed Management Objects are available through a Development library called SQLDMO.DLL from Microsoft. This Library can be used to automate the repetitive tasks needed to be done on, SQL Databases and Servers using Enterprise manager.
Tasks like Back up, Restore, Scheduled Backup etc., can be easily automated. There are a large number of combinations in which this can be used apart from Backup and
restore. This could be automating even the task of creation and removal of databases. Almost any requirement on the SQL Server automation can be achieved either as batch jobs or even interactive jobs.
Features of SQL DMO:
SQL DMO exposes the objects outlined as in the SQL Server architecture. It includes Servers, Databases, Tables, Stored Procedures, Indexes, Users, Views etc., All these objects are
also exposed as collections and these can be manipulated using the individual objects made available with this Library. For example SQLServer Object exposes the properties of the Server, Table object exposes the properties of a Single table, StoredProcedure object exposes the properties of a Stored Procedure, Database object exposes the properties of the database(including the physical path of the db) etc .,
Another point to be noted is SQLDMO contains interfaces to objects for both SQL Server 7.0 and SQL Server 2000. We can use the Object names like Table, StoredProcedure, index etc for version 7.0 and names like Table2, StoredProcedure2 etc., for the 2000 Version. The version of the Sql Server can be obtained from the instance of the SQLServer object. Fortunately we need not know everything. The intellisense in Visual Studio .Net or the Object browser can clearly show us all the available objects. After this one needs only the knowledge of basic Sql server architecture and skill to code in C#. The rest all comes easily.
Using SQL DMO in C#:
SQLDMO.DLL is made available to the system while installing SQL Enterprise manager itself. This is available at "SQL Enterprise Installation
Path\80 \Tools \Binn \SQLDMO.DLL". The installation path is usually C:\Program Files\Microsoft SQL Server.
This SQLDMO.Dll is a COM dll which can be used with Interop in .Net. Its Interop dll can be generated using tlbimp.exe or if the SQLDMO.Dll is added as a reference through the Visual Studio .Net editor, it automatically creates the necessary
Interop assembly. From now on the SQL DMO is available for manipulation in .Net.
Working with Different Objects of SQL DMO:
The following sections explore how to write code for some commonly needed operations.
Generating a List of all Available Servers:
This can be done using the ListAvailableSQLServers function of
the Application class. The following code snippet will make things clear.
using SQLDMO; //Do not forget to add
the sqldmo.dll library from the sql server installation folder
SQLDMO.Application fApp = new SQLDMO.ApplicationClass();
SQLDMO.NameList nl = (SQLDMO.NameList)fApp.ListAvailableSQLServers();
foreach(string strServer in nl)
{
MessageBox.Show(strServer);
}
|
It should be noted that the above
code might give an error saying "QueryInterface for interface
SQLDMO.NameList failed". If such exception occurs, it means the server
should be updated with the latest service pack (above SP 2).
Getting a List of Databases in a Server:
This again is a very simple task
with SQL DMO. The first step is to connect to the Server with SQLServer class.
Once connected, the SQLServer object will give a collection named as
SQLDMO.Databases which will contain all the databases available on the server.
The sample code snippet is as below.
using SQLDMO; //Do not forget to add
the sqldmo.dll library from the sql server installation folder
SQLDMO.SQLServer fServer = new
SQLDMO.SQLServerClass();
fServer.Connect("ServerName","UserName","Password");
foreach(SQLDMO.Database db in fServer.Databases)
{
MessageBox.Show(db.Name);
}
|
Generating the List of Tables in the Database:
This is also similar to the above script. Connect to
the Server, Get the database and retrieve the Table enumeration. This can be
used to enumerate all the table script.
foreach(SQLDMO.Table2 tbl in
db.Tables)
{
if(tbl.SystemObject == false)
{
Console.WriteLine("/*--Begin Table " + tbl.Name + "--*/");
Console.Write(tbl.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default,"","",
SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default));
Console.WriteLine("/*--End Table " + tbl.Name + "--*/");
}
} |
Similarly we can use SQLDMO to
script out the data in the tables, scripts for indexes and more.. This DMO
objects can also be used to schedule back up jobs for SQL Database Backup and
Restore operations.
The sample application contains a
small set of code snippet to connect to the database and get the backup scripts
for the selected database. The default Server name is set to (local) where I
used the SqlServer 2000 MSDE database. This can be used to connect to any SQL
Server and tried. It is enough if we change the server name on the top text box.
After Connecting when the back up is
clicked, it creates a tblscripts.txt file with the db script, table scripts and
the stored procedure scripts for the selected database.
Attachments:
Project Files: SqlDmoBackupRestore.zip