Using the SQL Server SMO object to remotely backup a database.

Sure, as a SysAdmin, you can do backups via the Management Studio interface, but what if you've just shut down SSMS for the day and someone comes begging you for a backup? Do you really want to wait for SSMS to pull itself together and get connected to a DB so you can open object explorer or type up the T-SQL required?

Well, okay. Maybe I'm a lazy DBA. I certainly prefer saving code snippets to do all my backup / restores to retyping them from scratch every time I need them. However, when I found out about the new SQL Server SMO object, I just about jumped up and down for joy. Finally, a new toy I could have lots of fun with!

Searching the internet for information on the new SMO object for SQL Server 2005, I found lots of information on how to use the SMO to backup local databases. Unfortunately, what I needed and couldn't find was information on how to backup the databases remotely. After a lot of work, I finally found the solution to my problem and thought I would pass it on to everyone else. Hopefully this will help someone else out. Just make sure the people you give this program to actually have permission to backup the databases or the program will fail.

Important Note: Make sure to save your work intermittently or you may loose everything you're working on if there's a power surge.

Start by creating a project for a new Visual Basic Windows Application. I called mine SMO_BackupDatabase. Okay, so I'm not going to win awards for creative program names, but it gets the point across. @=)

Next, add References to the project by going to Project -> Add Reference. You can add them all at once by using CTRL-Click. The references you need are listed below:

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo

Rename Form1 in properties and under the project / solution menu to ChooseEnvironment. Also, change the text on the form to "Choose Environment" so the end users know what they are looking at. NOTE: I rename all my objects to descriptive user-friendly names so when a bug occurs, I know exactly where to find it. (See Figure1)

Add a label, rename it to WelcomeLabel, and enter appropriate text as introduction to Program. I used the following:

"Welcome to the SQL SMO Database Backup Program!

Please Select an Environment to get started."

FIGURE 1

Add a GroupBox & as many Radio buttons as you have environments. (I use four, one each for Dev, Test, QC and Production).

Make sure to change your names and the Text properties to something appropriate. I used ServerEnvironment, Development, Test, QC, and Production for mine.

Lastly, add 2 buttons to the bottom of the form. Call the first one ChoseDBButton and set the Text as "Choose Database(s)". Call the second one CancelExitProgramButton and set the Text as "Cancel / Exit" (See Figure 2)

FIGURE 2

Double-click the ChooseEnvironment form. Since it is the initial form you want to start with, set the following code. The FormStartPosition tells the computer where on the monitor to place the form when the program runs.

Public Class ChooseEnvironment
   Private Sub  ChooseEnvironment_Load(ByVal sender As System.Object _
               , ByVal e As System.EventArgs) Handles MyBase.Load
 Me.StartPosition = FormStartPosition.CenterScreen
 End Sub
 
End Class

Double-click each radio button, the ChooseDBButton and the CancelExitProgramButton. You want to put in the following code right above the "End Class" of the ChooseEnvironment code.

Private Sub Development_CheckedChanged(ByVal sender As System.Object _
            , ByVal e As System.EventArgs) _
Handles Development.CheckedChanged
 If Me.Development.Checked = True Then
 MyEnv = "Dev"
 End If
 End Sub
 Private Sub Test_CheckedChanged(ByVal sender As System.Object, _
              ByVal e As System.EventArgs) _
          Handles Development.CheckedChanged
 If Me.Test.Checked = True Then
 MyEnv = "Test"
 End If
 End Sub
 Private Sub QC_CheckedChanged(ByVal sender As System.Object, _
          ByVal e As System.EventArgs) _
      Handles Development.CheckedChanged
 If Me.QC.Checked = True Then
 MyEnv = "QC"
 End If
 End Sub
Private Sub Production_CheckedChanged(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) _
   Handles Development.CheckedChanged
 If Me.Production.Checked = True Then
 MyEnv = "Production"
 End If
 End Sub
 Private Sub CancelExitProgramButton_Click(ByVal sender As System.Object, _
          ByVal e As System.EventArgs) _
    Handles CancelExitProgramButton.Click
 MsgBox("Ending program as requested.")
 CloseObjects()
 End
 End Sub
 Private Sub ChooseDBButton_Click(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) _
Handles ChooseDBButton.Click 
CreateMyConnection(MyEnv)
 My.Forms.BackupDatabase.ShowDialog()
End Sub

When this code is created, there will be several underlines indicating that Visual Studio doesn't know what variables and objects you're talking about. That's okay. We'll fix that in a moment.

Next, create a new code module called CreateInitialConnection. (Right click solution name -> Add -> Module). All commented out code is used for debugging to verify I'm connecting to the correct servers. You can delete it if you want to.

First, the following Import statements go above the module name:

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Data.SqlClient

Then it's time to declare the objects and write the module code. You will need a Server object, a ServerConnection object, and 2 Public string objects. I actually force the users to close the program if they want to choose a different environment. You can change this to disconnect and reconnect to the server objects if you want to, but I'm currently leaving it out of the scope of this discussion.

Module CreateInitialConnection Public ProdServer As New Server()
 Public ProdConn As ServerConnection
 Public MyServerEnv As String
 Public MyEnv As String
 Public Sub CreateMyConnection(ByVal Environment As String)
 If MyServerEnv = Nothing Then
 MyServerEnv = Environment
Else
 MsgBox("Sorry.The program must be closed and restarted to choose a new environment.")
 End
 End If
'MsgBox("MyServerEnv is " & MyServerEnv)
 ProdConn = ProdServer.ConnectionContext
 Select Case MyServerEnv
 Case "Dev"
 ProdConn.ServerInstance = "MyDevServer, 1433"
 Case "Test"
 ProdConn.ServerInstance = "MyTestServer, 1433"
 Case "QC"
 ProdConn.ServerInstance = "MyQCServer, 1433"
 Case "Production"
 ProdConn.ServerInstance = "MyProdServer, 1433"
 Case Else
 MsgBox("You must choose an environment first.")
 End Select
 'MsgBox("Connection String is " & ProdConn.ServerInstance.ToString)
End Sub
End Module

Explanation: For each ServerInstance of the connection object, I set the ServerName and Port# manually. Replace the above with your own ServerName, Port#. This is easier, and faster if you only have a few servers, to code than enumerating your servers. However, if server names change, and they can, you'll have to edit your code and redeploy your program as needed.

Once this code is completed, you will notice all the underlines from the ChooseEnvironment code MyEnv object will disappear.

Add a new module. Call it CloseConnObjects. This code will reset all variables and close all objects when it is called. I do not use the Me.Close() in this because it closes the program the instant I try to open it. Instead, I call this module in my various Exit Program buttons.

Module CloseConnObjects
 Public Sub CloseObjects()
 ProdServer = Nothing
 ProdConn = Nothing
 MyServerEnv = Nothing
 MyEnv = Nothing
 End Sub
End Module

Add a new form. Right click the solution name -> Add -> Windows Form. Call it BackupDatbase.

Drag a Listbox over to the new form and enlarge to a size where the user will be able to see all database names easily. Call it DatabaseList.

Add 2 new buttons to BackupDatabase form. Call one BackupDatabaseButton and the other ExitProgramButton. Change the text on the first to "Backup Database" and the text on the other to "Exit Program". Resize buttons as needed. (See Figure 3)

FIGURE 3

Double-click the BackupDatabase form and it will take you to a new code window.

You will need to add the following import statements above your BackupDatabase form code:

ImportsMicrosoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

You will also need to append a last import statement, using the name of your solution and the new module. This will make sure that your CreateInitialConnection actually connects.

Imports SMO_BackupDatabase.CreateInitialConnection

The below code enumerates all the databases in the environment (or instance) that the user has selected. It does not show Snapshots or system databases.

Public Class BackupDatabase
 Private Sub BackupDatabase_Load(ByVal sender As System.Object, _
         ByVal e As System.EventArgs) _
Handles MyBase.Load
 Me.StartPosition = FormStartPosition.CenterScreen
 '##### Add list database code here #####
 Dim db As Database
 For Each db In  ProdServer.Databases
 If Not db.IsDatabaseSnapshot And Not db.Name = "tempdb" Then
 DatabaseList.Items.Add(db.Name)
 End If
 Next
 Try
BackupDatabaseButton_Click(DatabaseList.SelectedItem.ToString, e)
 Catch
             MyExeception   As System.NullReferenceException
   'Call MsgBox("You must select a database to continue.")
 End Try
 db = Nothing
 'Clear out values
End Sub

Above the "End Class", you'll need to add the Backup

Database button code. It uses the dating format that SQL Server's database

maintenance plans use when backing up the databases.

Private Sub BackupDatabaseButton_Click _
  (ByVal sender As System.Object, ByVal e As System.EventArgs) _
  Handles BackupDatabaseButton.Click
 ' If no database is selected, exit this event handler
 If DatabaseList.SelectedIndex = -1 Then Exit Sub
 MsgBox("Database being backed up. Please be patient.")
 '##### Add backup code here #####
 Dim dbName As String = DatabaseList.SelectedItem.ToString
 Dim MyBackup As New Backup
 Dim ProcessDate As Date = Now()
 Dim BackupTime  As String
 Dim DeviceLocation    As String
 Select Case MyServerEnv
 Case "Dev"
 DeviceLocation = "MyDevServer\SQL_BAK_DEV\"
  Case "Test"
 DeviceLocation = "MyTestServer\SQL_BAK_TEST\"
 Case "QC"
 DeviceLocation = "MyQCServer\SQL_BAK_QC\"
 Case "Production"
 DeviceLocation = "MyProdServer\SQL_BAK\"
 Case Else
 DeviceLocation = Nothing
 MsgBox("You must choose an environment first.")
 Me.Close()
 End Select
 'Sets the share name where the backup will be stored
  BackupTime = ProcessDate.ToString("yyyy") _
           & ProcessDate.ToString("MM") _
  & ProcessDate.ToString("dd") _
           & ProcessDate.ToString("HH") _
   & ProcessDate.ToString("mm")
 MyBackup.Action = BackupActionType.Database
 MyBackup.BackupSetName = dbName & "Backup"
 MyBackup.Database = dbName
 Dim MyDevice As BackupDeviceItem = New BackupDeviceItem( _
 "\\" & DeviceLocation & dbName _
        & "_db_" _
  & BackupTime _
  & ".BAK", DeviceType.File)
 MyBackup.Devices.Add(MyDevice) 
 MyBackup.SqlBackup(ProdServer)
 MessageBox.Show(dbName & " has been successfully backed up in " _
       & "the SQL_BAK folder with the following FileName: " _
 & CStr("\\" & DeviceLocation & dbName _
       & "_db_" & BackupTime _
 & ".BAK"))
 dbName = Nothing
 MyBackup = Nothing
 ProcessDate = Nothing
 BackupTime = Nothing
 MyDevice = Nothing
 DeviceLocation = Nothing
 DatabaseList.SelectedIndex = -1
 'Clear all variables
End Sub

Above the "End Class", but below the BackupDatabaseButton code, add the following code for the Exit Program button.This code tells the user it is closing the program and closes all objects as it does so.

Private Sub ExitProgramButton_Click(ByVal sender As System.Object _
     , ByVal e  As System.EventArgs) _
  Handles ExitProgramButton.Click
 MsgBox("Program terminating as requested.")
 CloseObjects()
 End
End Sub

Then verify in the ChooseEnvironment code that all the

underlines have disappeared. If you've done everything correctly, you should

be good to go.

Save the program, then go to Debug -> Start Debugging and

test it on your servers. I recommend testing on Dev first. It usually takes a

moment for the database list to appear, so if you want to stick an additional

message box in the ChooseDBButton code that tells the end user to wait, that

might help.

The most common error with debugging here is forgetting to

substitute your real ServerName\InstanceName and Port # in the

CreateInitialConnection and BackupDatabaseButton sections of code. So if

you're running into connection errors, those are the first two places you

should look.

The way I've got this coded is that you can only backup 1

database at a time. However, you do not have to close the program to backup

another database in the same environment. Just keep choosing databases and

clicking the "Backup Database" button to your heart's content. When

you're done playing with one environment, close the program and re-open to

switch to another environment.

Once you've debugged your code, it's time to create the

program for distribution. This is a great opportunity to allow Accounting to

do a database backup before they run month end or the development team to

backup a database before they put in a release.

Building your application installation file

Please note, this is a brief summary intended for those

(like myself) who have never done this before. The scope of this article does

not include all the possible variations or security options available to you.

Double-click My Project on Solution Explorer. Make sure the

Application tab is highlighted and that the Startup Form is listed as

ChooseEnvironment.

Using the main menu in VS, navigate to File -> Add ->

New Project -> Other Project Types -> Setup and Deployment. The default

name of the new project is Setup1 and it will bring up a new page in your

project called File System. (See Figure 4)

FIGURE 4

Click Setup1 in Solution Explorer. Look at the properties window. Here is where you will enter useful information such as Author's name, version #, localization information and a description of the program. (See Figure 5)

FIGURE 5

Right-click Setup1 and choose Properties. VS will bring up a window telling you the default location of where the .msi file will be created. You can change this if you want, but don't change it too much. Keeping the .msi file with your project will make finding it much easier at a later date. Click Cancel to exit.

Right-click the Application Folder and choose Add -> Project Output. A window will pop up asking what output. You'll want to make sure Primary output and a configuration of Active are chosen. Click OK. (See Figure 6)

FIGURE 6

Right-click the Primary Output and choose "Create Shortcut to Primary Output..." which will force a shortcut to be created. (See Figure 7) Rename the shortcut to something appropriate and user-friendly. Drag the new item into either the "User's Desktop" or "User's Program Menu" folders. Upon installation, a shortcut in the User's Start -> Program Files menu and/or the User's desktop will be created. If you want one of each, you have to create 2 shortcuts to the application folder and drag them to the appropriate folder.

FIGURE 7

Right-click Setup1 and choose Build this time. This will build your .msi file for later distribution.

If you left the defaults, you'll find your .msi under SMO_BackupDatabase\Setup1\Debug. You can distribute it to whomever might need the ability to backup the database. Double-clicking the .msi installs it automatically.

When all is said and done, this code should work perfectly for remotely backing up databases, as long as you have permission do to so. I'm still learning VB .Net myself, so there's a lot of things missing from this version of my code (error handling, re-initializing the server connection to switch environments, progress bars, etc.). If anyone has any thoughts on how this could be improved, I'd be glad to hear them.

Having fun coding!

by Brandie Tarvin

MSDBA, MSCA