Allen White

See you in Boston

Using SMO to set Database Properties

In my work environment we've got a number of production servers, each containing multiple databases from different applications, both internally developed and vendor supplied. Whenever we need to test changes to these apps I move a backup file from a production database to a development or QA server and restore the database there, allowing people to use real data to ensure the quality of their work. I do full backups on our production databases once a day, and transaction log backups once an hour (or more frequently, as needed). In the Dev/QA area, though, I don't need the transaction log backups, so I set the databases to Simple Recovery Mode.

It's easy to forget to change this setting on a busy day and then the drive allocated for the log segments fills up and people get mad because their testing stops working and blah, blah, blah. You know the drill.

I've found the quickest way to ensure I don't run into these problems is a quick SMO program to set the database properties of all databases on the server, in case I missed one along the way. I created a VB.Net console app to do this, and it's really quite straightforward.

The first thing you’ll need to do is to select Add Reference from the Project menu. Within the Add Reference dialog, use the Ctrl key to select the following assemblies (you may have to browse to the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder):

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll
Microsoft.SqlServer.SqlEnum.dll

Next, open up the code window for the application, and at the top of the code, before any declarations, insert the following lines:

Imports Microsoft.SqlServer.Management.SMO
Imports Microsoft.SqlServer.Management.Common

Now you'll have a Sub Main() and End Sub pair in the code window. After Sub Main, define your server like this:

        ' Connect to the server
        Dim srvMgmtServer As Server
        srvMgmtServer = New Server("MyDevServer")
        Dim srvConn As ServerConnection
        srvConn = srvMgmtServer.ConnectionContext
        srvConn.LoginSecure = True
        If srvConn.LoginSecure = False Then
            srvConn.Login = "SqlLogin"
            srvConn.Password = "xxxxxxxx"
        End If

This connects to the server using Windows Authentication if LoginSecure is set to True, and uses the supplied Login and Password values if LoginSecure is set to False.

Next is the code that loops through the databases to set the properties:

        ' Browse the database collection on the target server
        Dim lisDBs As DatabaseCollection
        lisDBs = srvMgmtServer.Databases
        Dim objDB As Database
        For Each objDB In lisDBs                    ' Loop through the databases on the server
            If Not objDB.IsSystemObject Then
                Dim objDBOptions As DatabaseOptions

                'objDB.CompatibilityLevel = CompatibilityLevel.Version90
                objDBOptions = objDB.DatabaseOptions
                objDBOptions.RecoveryModel = RecoveryModel.Simple
                objDBOptions.Alter()
                objDB.Alter()
            End If
        Next

I've commented the code setting the CompatibilityLevel because I sometimes run this on my SQL 2000 servers, but left it in the code so it's there when I need it. Setting the RecoveryModel to Simple solves my transaction log problem on my Dev/QA servers.

The important thing for me is that I don't have to open up the properties window for each database in Management Studio to make sure all the databases are in Simple Recovery mode - I just run this app and I know they're set correctly.

Allen