SQL Distributed Management Objects (SQLDMO)

Last Updated: 20/4/2003

To use SQLDMO from .NET add a project reference for the 'Microsoft SQLDMO Object Library' COM component.

Topics

Getting a list of SQL Servers
Getting a list of SQL Servers is one of the few tasks that doesn't require you to log-in first:

SQLDMO.Application oApp = new SQLDMO.ApplicationClass();
SQLDMO.NameList oServerList = oApp.ListAvailableServers();
foreach(string strServerName in oServerList) {...}

Back to Topic List 

 

Connecting / Disconnecting with SQLDMO
As with SQL data access, we have to authenticate ourselves to SQL before we can use the majority of the Management Objects API functionality.

// Connect using Integrated authentification
SQLDMO.SQLServer oSqlSrv = new SQLDMO.SQLServerClass();
oSqlSrv.LoginSecure = true;
oSqlSrv.Connect("ServerName", "", "");

// Connect using SQL Server authentification
SQLDMO.SQLServer oSqlSrv = new SQLDMO.SQLServerClass();
oSqlSrv.LoginSecure = false;
oSqlSrv.Connect("ServerName", "username", "password");
 

Disconnection is simple, but it is worth nothing the unusual casing of the DisConnect:

// Disconnect (note the capital C)
oSqlSrv.DisConnection();

Back to Topic List 

 

Restoring a Database

Connect to the server as shown above to obtain a SQLDMO.SQLServer object, then:

SQLDMO.SQLServer oSqlSrv = ...
SQLDMO.Restore oRestore=
new RSQLDMO.RestoreClass();
oRestore.Action = SQLDMO.SQLDMP_BACKUP_TYPE.SQLDMOBackup_Databases;
oRestore.Database = "NorthWind";
oRestore.Files = @"c:\backupfile.bak";
// C: = SQL Server's C:  drive, not client's C: drive

// Following required if we want feedback during the process

oRestore.PercentCompleteNotification = 10;
// Message every 10%
oRestore.PercentComplete +=
new SQLDMO.BackupSink_PercentCompleteEventHandler(myPercentCompleteHandler);

// Start the backup
oBackup.SQLBackup(oSqlSrv);

...

public void myPercentCompleteHandler(string strMessage, int iPercent) {
    Console.WriteLine("{0}% Complete", iPercent.ToString());
}

Back to Topic List 

 

Backing up a Datbase

Connect to the server as shown above to obtain a SQLDMO.SQLServer object, then:

SQLDMO.SQLServer oSqlSrv = ...
SQLDMO.Application oApp =
new SQLDMO.BackupClass();
SQLDMO.Backup oBackup=
new SQLDMO.SQLDMP_BACKUP_TYPE.SQLDMOBackup_Databases;
oBackup.Database = "NorthWind";
oBackup.Files = @"c:\backupfile.bak";
// C: = SQL Server's C:  drive, not client's C: drive

// Following not actually required
oBackup.MediaName = sqlBackup.Database + " " + DateTime.Now.ToString();
oBackup.BackupSetName = sqlBackup.Database + " set";
oBackup.BackupSetDescription = sqlBackup.Database + " etc.";

// Following required if we want feedback during the process
// Message every 10%

oBackup.PercentCompleteNotification = 10;
// See Restore example for implementation of myPercentCompleteHandler
oBackup.PercentComplete += new SQLDMO.BackupSink_PercentCompleteEventHandler(myPercentCompleteHandler);

// Start the backup
oBackup.SQLBackup(oSqlSrv);

Back to Topic List