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.
