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

Introduction to SQL-DMO

By Andy Warren, (first published: 2003/08/15)

SQL-DMO (Distributed Management Objects) provides a programmatic (COM) interface to SQL Server. It’s a great tool for automating routine tasks – far more flexible than T-SQL in most cases. In this article I’ll demonstrate several uses of DMO that will give you a starting point for building your own solutions. I’ll be using VB Script for these examples, but you can use any language that supports COM. Code has been tested on SQL 2000, but should work fine in SQL 7.

  I like to use VB to code my DMO scripts, then convert to VB Script. If you use this method, a few things to remember:

  • Set a reference to Microsoft SQL-DMO so you get Intellisense

  • Use CreateObject instead of New when creating your objects– VB Script doesn’t support New

  • Use the variant model functions (CHR, UCASE, etc) instead of the ones optimized for strings (CHR$,UCASE$) – VB Script doesn’t support the string versions

  • Convert constants to their actual values

  • Remove all data typing. A handy trick is to just comment out the type declaration, so you can more easily read the code and you can always uncomment them if you dump the code back into VB to make changes.

If you’re just using Notepad to write your scripts, add a few lines at a time and test. Use Msgbox to help you debug and trace your program flow. Scripts should be saved with the standard .VBS extension – you can execute them by double clicking.

Example #1 – How many databases do you have?
This shows how to connect to SQL running on your local machine, display the number of databases, then disconnect.

Dim oServer

 

'get a reference to the DMO object (sqldmo.dll)

Set oServer = CreateObject("SQLDmo.SqlServer")

 

'use a trusted connection

oServer.LoginSecure = True

oServer.Connect

 

'this displays the count – should be the same count you’d get doing

'select count(*) from master..sysdatabases

MsgBox oServer.Databases.Count

 

'always clean up when you’re done

oServer.DisConnect

Set oServer = Nothing  

Example #2 – How much space is available in each database?
This script cycles through each database and returns the free space in megabytes. Note that this time I’m specifying the server to connect to rather than relying on the default behavior of connecting to the local machine.

Dim oServer

Dim oDatabase

Dim sResults

 

Set oServer = CreateObject("SQLDmo.SqlServer")

 

oServer.LoginSecure = True

 

'this time Im specifying which server to connect to

oServer.Connect "Andy"

 

'this will return the name of each database along with the total free

'space in megabytes. The Chr(9) is a tab, the chr(13) and the chr(10) map

'to carriage return and line feed respectively

For Each oDatabase In oServer.Databases

    sResults = sResults & oDatabase.Name & Chr(9) & oDatabase.SpaceAvailableInMB & Chr(13) & Chr(10)

Next

 

'show the answer - could easily send using xp_sendmail if we wanted

MsgBox sResults

 

oServer.DisConnect

Set oServer = Nothing

Example #3 – Update Statistics if Auto Update is Disabled
You aren’t restricted to just reading information, you can make changes or take action as well. In this example I’m going to update the statistics for any database that has the auto update option disabled. At the end I’m adding some additional text to the sResult variable to make the display a little more informative.
 

Dim oServer

Dim oDatabase

Dim sResults

 

Set oServer = CreateObject("SQLDmo.SqlServer")

 

oServer.LoginSecure = True

 

'this time Im specifying which server to connect to

oServer.Connect "Andy"

 

'if auto update stats is turned off, update the statistics and return a list of those

'databases

For Each oDatabase In oServer.Databases

    If oDatabase.DBOption.AutoUpdateStat = False Then

        sResults = sResults & oDatabase.Name & Chr(13) & Chr(10)

        oDatabase.UpdateIndexStatistics

    End If

Next

 

'here Im making the output a little fancier

If sResults <> "" Then

    sResults = "Statistics were updated for the following databases: " & Chr(13) & Chr(10) & sResults

Else

    sResults = "No databases had auto update statistics turned off."

End If

MsgBox sResults

 

oServer.DisConnect

Set oServer = Nothing

Example #4 – Backup All Databases

Dim oServer

Dim oDatabase

Dim oBackup

Dim sBAKFilePath

 

'change this to where ever you want to place your backup files, no trailing

'backslash, we add it below

sBAKFilePath = "C:\Backup"

 

'we need a backup object in addition to the sqlserver one

Set oServer = CreateObject("SQLDmo.SqlServer")

Set oBackup = CreateObject("SQLDmo.Backup")

 

oServer.LoginSecure = True

oServer.Connect "(local)"

 

'this will do a full backup of every database except TempDB to a file (not a

'device

For Each oDatabase In oServer.Databases

    If UCase(oDatabase.Name) <> "TEMPDB" Then

        oBackup.Database = oDatabase.Name

        'remove any previous backup - same as using T-SQL with init

        oBackup.Initialize = True

        'dynamically create the name of the backup file

        oBackup.Files = sBAKFilePath & "\" & oDatabase.Name & ".bak"

        'set the action property as needed

        '0 = Full backup

        '1 = Differential

        '2 = Specified files only

        '3 = Log backup

        oBackup.Action = 0

        oBackup.SQLBackup oServer

    End If

Next

 

'clean up

Set oBackup = Nothing

oServer.DisConnect

Set oServer = Nothing 

Deploying Your Scripts
Once you’ve gotten your code to work successfully, there are several ways to put it to work:

  • Leave it in a VBS file (or compile into an executable) to be run manually on as needed basis.

  • Add it as a step to SQL job – select ActiveX Script as the step type. Keep in mind that the size of the step is limited to about 3200 chars (the size of the command column in sysjobsteps).

  • Schedule the VBS/EXE to run as a OS scheduled task or as a SQL job of type CmdExec.

If you play to run your code on the server, comment out or remove any lines that use Msgbox, otherwise your script will be stalled waiting on the user to click OK. Even worse, if you run the script as a SQL Agent ActiveX Script job, the message box will be suppressed so the server operator cannot see it, but the job will still be waiting on user input.

One other technique you may find useful is to include ‘Print’ statements in your script. These will fail when you test your script in VB or VB Script, but when run under SQL Agent the output of the print statement will be entered into the job history. Example #1 might be modified as follows:

          Replace        MsgBox oServer.Databases.Count
          With             Print “Count=” & oServer.databases.count

Total article views: 38565 | Views in the last 30 days: 13
 
Related Articles
ARTICLE

Powershell Database Backup Script

Learn how to write a script for regular database backups using Powershell and SMO.

FORUM

xmla script for automate ssas database backup

xmla script for automate ssas database backup

SCRIPT

Script to Check the Database Backup duration

Script to Check the Database Backup duration of entire instances

SCRIPT

Backup & Restoration Script

This script provides very useful information about database backup and restoration.

SCRIPT

Script to Get the Backup History

Script to get the database backup history on SQL Server 2000/2005/2008

Tags
programming    
sql-dmo    
 
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