Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using the SMO to create Database Snapshots in SQL 2005

SQL SMO Programming

Using the SMO to create Database Snapshots in SQL 2005

by  Catadmin  Posted    (Edited  )
Create Snapshots using the new SQL Management Object (SMO)

This version uses the Visual Basic .Net programming language and requires access to Visual Studio .Net 2005 as well as SQL Server 2005.

First, open up Visual Studio .Net and navigate to File -> New -> Project. When the pop-up window appears, choose the Visual Basic -> Windows -> Windows Application project type. This will pop up Form1 on the Design view.

Go back up to the main menu and navigate to Project -> Add Reference. Scroll down until you see Microsoft.SqlServer.ConnectionInfo. Click on it, continue scrolling down until you see Microsoft.SqlServer.Smo, hold down the CTRL key and click on this. Click okay.

Double-click the form or go to View -> Code. Either will open a new window in the Design section that has code in it. In the General Section, you will need to add the following import statements if they do not already exist:

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

Go back to the window which contains the form. Add a ListBox and a button to the form. Change the Text and Name properties to something user friendly. For the ListBox, I changed both the TEXT and NAME to ôDatabaseListö and for the button, I changed the TEXT to ôCreate Snapshotö and the NAME to ôCreateSnapshotö (the NAME property does not allow spaces).

Go back to the Code window. Create a new public sub which contains your connection info. HereÆs my code:

Code: 
    Public Sub CreateMyConnection()

        ProdConn = ProdServer.ConnectionContext
        ProdConn.ServerInstance = "<MyServer>, <MyPort>"

    End Sub

We use ports where I am. If you just have a server name, ignore the ô, <MyPort>ö part of the ServerInstance.

Next go the Private Sub Form1_Load class. The below shows my code for enumerating all the databases, except for the system DBs and previously created snapshots.

Code: 
   CreateMyConnection()
        Dim dbname As Database
        For Each dbname In ProdServer.Databases
            If Not dbname.IsSystemObject And Not dbname.IsDatabaseSnapshot Then
                DatabaseList.Items.Add(dbname.Name)
            End If
        Next
        'The above code cycles through the list of all databases on the server and displays
        'them in the ListBox.  It does NOT include system databases or existing snapshots

        Try
            CreateSnapshot_Click(DatabaseList.SelectedItem.ToString, e)
        Catch MyExeception As System.NullReferenceException
            'Call MsgBox("You must select a database to continue.")
        End Try
        'The above calls the button event which will create the snapshot

        CloseObjects()
        'The above calls the public sub CloseObjects() to clear out all variables

        dbname = Nothing
        'The above clears out the variable dbname

BTW, the Try/Catch was put in because VS forced me to check for a Null Reference Exception. However, when I put in the MsgBox, it listed the message before the form popped up and the ListBox filled, hence the reason I commented it out. If anyone has a fix for this, IÆd be happy to hear about it.



Now itÆs time for the button code. We need to make sure it actually does something when clicked. @=) The database needs to be highlighted for the below to work.

Code: 
        Dim SnapshotCode As String
        Dim DBToSnapShot As String = DatabaseList.SelectedItem.ToString
        'Prepares my variables to store the database name as well as the T-SQL to snapshot


        SnapshotCode = "CREATE DATABASE " & DBToSnapShot & "_SS ON (NAME=" _
    & DBToSnapShot & "_Data,FILENAME='K:\SQL_DATA\" & DBToSnapShot & ".SNAP') AS SNAPSHOT OF " _
    & DBToSnapShot
        'The above sets the T-SQL, including the drive path, of where the Snapshot will go

        'MsgBox("The Snapshot Code is translated as " & SnapshotCode)
        'The above MsgBox is used for debugging the Snapshot Code

        Dim MySQLConn As New SqlConnection("data source=<MyServer>, <MyPort>;initial catalog=master;Trusted_Connection = Yes")
        Dim command As New SqlCommand(SnapshotCode, MySQLConn)
        Dim QueryReturnedValue As Integer
        'The above code sets up a new SQL Connection, as well as setting the query
        'to use the above connection, and accepts a return code for verifying
        'success or failure.  

        MySQLConn.Open()
        'Above opens the SQL Connection

        QueryReturnedValue = command.ExecuteNonQuery()
        'Above executes the T-SQL Snapshot command

        If QueryReturnedValue = 0 Then
            MsgBox("Snapshot Creation failed.  Try again or contact a DBA for further instructions.")
        Else
            MsgBox("Snapshot Creation succeeded.  Chose another database to snapshot or close this form.")
        End If
        'User-Friendly response to button click

        MySQLConn.Close()
        'Close out connection (just good programming)

        MySQLConn = Nothing
        command = Nothing
        QueryReturnedValue = Nothing
        SnapshotCode = Nothing
        DBToSnapShot = Nothing
        'Empty all variables for trash collection


Lastly, we create our other Public Sub which clears out our Server objects.

Code: 
    Public Sub CloseObjects()
        ProdServer = Nothing
        ProdConn = Nothing
        ' Me.Close()
    End Sub

The ôMe.Closeö is supposed to close out the form automatically, but when I ran it, the form didnÆt even open for me to choose a db to snapshot (or it opened and closed it right away, not sure). If anyone has a fix for this, please let me know.

Anyway, this is how you can create your own Windows Application to Snapshot databases. And, this program can be distributed to developers and other DBAs so they can snapshot a DB without being logged into SSMS.

I hope this helps someone out. My next task is to learn C# so I can write it in that, and then to code the SMO for backing up remote servers to their own local drives without being logged into SSMS.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top