Introduction

One of the features available in Siccolo - Management Tool For SQL Server is the ability to back up a database. The idea is trivial: A mobile device sends its request to a web service and executes a web method. The web method runs a "backup" command on a remote SQL Server to back up a database to/in a folder on that SQL Server:

Because the process of backing up a database may take a few "lengthy" moments, the Siccolo application uses an asynchronous method call to a web service. The code presented allows the mobile device to back up a database on a remote SQL Server.

Server Code

First, here is the web method to back up a database on a SQL Server. For this, you can use a standard "backup database" command.

<WebMethod()> Public Function BackupDatabase _
   (ByVal ServerAddress  As String, _
    ByVal UserName       As String, _
    ByVal UserPassword   As String, _
    ByVal DatabaseName   As String, _
    ByVal BackupFileName As String, _
    ByVal BackupName     As String, _
    ByRef ErrorInfo As String) As Boolean

   Try
      With oLoginInfo
                    .sServer    = ServerAddress
                    .sLoginName = UserName
                    .sPassword  = UserPassword
                    .sDatabase  = ""
      End With

      Dim ToDebugSetting As String =
         System.Configuration.ConfigurationSettings.
            AppSettings.Get("DebugMode")
      Dim ToDebug As Boolean = (ToDebugSetting <> "")

      If oCon.BackupDatabase(oLoginInfo, _
                                        DatabaseName, _
                                        BackupFileName, _
                                        BackupName, _
                                        ToDebug, _
                                        ErrorInfo) Then

         Return True
      Else
         If ToDebug Then
            oCon.UpdateIncomingStatus("BackupDatabase: failed" _
               & ErrorInfo, EventLogEntryType.Information)
         End If
         Return False
      End If

   Catch ex As Exception
      ErrorInfo = ex.Message()
      Return False
   End Try
End Function

where:

• oCon: The instance of a class handling all database/SQL Server interactions
• oLoginInfo: The instance of a structure to hold a SQL Server name and user credentials
• UpdateIncomingStatus: The method that writes to the event log on a server hosting this web service

The web method itself calls BackupDatabase():

Public Function BackupDatabase(ByVal oLogInf        As LoginInfo, _
                               ByVal DatabaseName   As String, _
                               ByVal BackupFileName As String, _
                               ByVal BackupName     As String, _
                               ByVal ToDebug        As Boolean, _
                               ByRef ErrorInfo As String) As Boolean
   Try
      oLoginInfo = oLogInf

      Dim SqlCommand = BackupDatabaseCommand(DatabaseName, _
                       BackupFileName, BackupName)

      If (objConnection.State.ToString() <> "Open") Then
         Connect(ToDebug, ErrorInfo)
      End If

      Dim objCommand As SqlCommand = New SqlCommand(SqlCommand, _
                                                    objConnection)

      objCommand.CommandType    = CommandType.Text
      objCommand.CommandTimeout = 60    '600 seconds = 10 min.
      'The time (in seconds) to wait for the command to execute.
      'The default is 30 seconds.
      'A value of 0 indicates no limit


      objCommand.ExecuteNonQuery()

      DisConnect()

      Return True

   Catch ex As Exception
                ErrorInfo = ex.Message
                Return False
   End Try

End Function

where BackupDatabaseCommand() simply builds a "backup command" string based on passed Database Name, Backup File Name, and Backup Name:

' VB.NET
Private Function BackupDatabaseCommand(ByVal DatabaseName As String, _
                                       ByVal BackupFileName _
                                       As String, _
                                       ByVal BackupName As String) _
                                       As String

   Dim strBackupCommand As String =
      "Backup Database [" & DatabaseName & "]" & _
                        "TO DISK = N'" & BackupFileName & "'" & _
                        "WITH INIT " & _
                        ", NAME = '" & BackupName & "'" & _
                        ", NOSKIP" & _
                        ", NOFORMAT"

   Return strBackupCommand
   'INIT
   '   Specifies that all backup sets should be overwritten, but
   '   preserves the media header.
   '   If INIT is specified, any existing backup set data on that
   '   device is overwritten.
   '
   'NAME = backup_set_name
   '   Specifies the name of the backup set. Names can have a
   '   maximum of 128 characters.
   '   If NAME is not specified, it is blank.
   'Siccolo passes something like this:
   '   DatabaseName + "_SiccoloBackup_" +
   '   System.DateTime.Now.ToString("MM_dd_yyyy") 

   'NOSKIP
   '   Instructs the BACKUP statement to check the expiration date
   '   of all backup sets on the media before allowing them to be
   '   overwritten.
   'NOFORMAT
   '   Specifies the media header should not be written on all
   '   volumes used for this backup operation and does not
   '   rewrite the backup device unless INIT is specified.
End Function

And that's it for a web method/web service.

Client Code

Now, the client. Here is the user interface (form frmBackupDatabase, in my application):

Private Sub PerformBackup_Async(ByVal DatabaseName As String, _
                                ByVal BackupFileName As String, _
                                ByVal BackupName As String)

   Cursor.Current = Cursors.WaitCursor

   Dim ErrorInfo As String = ""

   'sets which form to invoke
   objSQLManager.BackupDatabaseForm = Me

   'call class handling interactions with web service:
   objSQLManager.BackupDatabase_Async(DatabaseName, _
                                      BackupFileName, _
                                      BackupName, _
                                      ErrorInfo)
End Sub

Where objSQLManager is the class on the client, handling all interactions with te web service.

...
...
Private m_objUIBackupDatabaseForm As frmBackupDatabase

Friend WriteOnly Property BackupDatabaseForm() As frmBackupDatabase
   Set(ByVal value As frmBackupDatabase)
      m_objUIBackupDatabaseForm = value
   End Set
End Property

And BackupDatabase_Async is the actual asynchronous method call. The Backup Database asynchronous operation is implemented as two methods named BeginBackupDatabase and EndBackupDatabase that begin and end the asynchronous operation BackupDatabase, respectively.

The BeginBackupDatabase method takes as many parameters as are declared in the signature of the synchronous version of the method that are passed by value or by reference:

'taken from Reference.vb:
...
<System.Web.Services.Protocols.SoapDocumentMethodAttribute _
   ("http://tempuri.org/BackupDatabase", _
    RequestNamespace:="http://tempuri.org/", _
    ResponseNamespace:="http://tempuri.org/", _
    Use:=System.Web.Services.Description.SoapBindingUse.Literal, _
    ParameterStyle:=System.Web.Services.Protocols. _
    SoapParameterStyle.Wrapped)> _
Public Function BackupDatabase(ByVal ServerAddress  As String, _
                               ByVal UserName       As String, _
                               ByVal UserPassword   As String, _
                               ByVal DatabaseName   As String, _
                               ByVal BackupFileName As String, _
                               ByVal BackupName     As String, _
                               ByRef ErrorInfo As String) As Boolean
      Dim results() As Object = Me.Invoke("BackupDatabase", _
                                New Object() {ServerAddress, _
                                UserName, _
                                UserPassword, _
                                DatabaseName, _
                                BackupFileName, _
                                BackupName, _
                                ErrorInfo})
      ErrorInfo = CType(results(1),String)
      Return CType(results(0),Boolean)
End Function

'''<remarks/>
Public Function BeginBackupDatabase(ByVal ServerAddress  As String, _
                                    ByVal UserName       As String, _
                                    ByVal UserPassword   As String, _
                                    ByVal DatabaseName   As String, _
                                    ByVal BackupFileName As String, _
                                    ByVal BackupName     As String, _
                                    ByVal ErrorInfo      As String, _
                                    ByVal callback _
                                    As System.AsyncCallback, _
                                    ByVal asyncState As Object) _
                                    As System.IAsyncResult
   Return Me.BeginInvoke("BackupDatabase", _
                          New Object() {ServerAddress, _
                                        UserName, _
                                        UserPassword, _
                                        DatabaseName, _
                                        BackupFileName, _
                                        BackupName, _
                                        ErrorInfo}, _
                          callback, _
                          asyncState)


'''<remarks/>
Public Function EndBackupDatabase(ByVal asyncResult _
                                  As System.IAsyncResult, _
                                  ByRef ErrorInfo As String) _
                                  As Boolean
   Dim results() As Object = Me.EndInvoke(asyncResult)
   ErrorInfo = CType(results(1),String)
   Return CType(results(0),Boolean)
End Function
...

The BeginBackupDatabase method signature also includes two additional parameters; the first of these defines an AsyncCallback delegate that references a BackupDatabase_Async_CallBack method that is called when the asynchronous operation completes:

Private Delegate Sub _
   AsyncCallHandler_BackupDatabase(ByVal CallBackResult As Boolean, _
                                   ByVal ErrorInfo As String)

The second additional parameter is a user-defined object. This object can be used to pass application-specific state information to the method invoked when the asynchronous operation completes.

BeginBackupDatabase returns control to the calling thread, to frmBackupDatabase, immediately. If the BeginBackupDatabase method throws exceptions, the exceptions are thrown before the asynchronous operation is started. And, if the BeginBackupDatabase method throws exceptions, the callback method is not invoked.

Friend Sub BackupDatabase_Async(ByVal DatabaseName   As String, _
                                ByVal BackupFileName As String, _
                                ByVal BackupName     As String, _
                                ByRef ErrorInfo      As String)

   Try

      If m_objUIBackupDatabaseForm Is Nothing Then
         Throw New Exception("User Interface Form is not set!")
      End If

      ErrorInfo = ""

      'm_objSiccoloProcessorAsync - reference to a web method
      m_objSiccoloProcessorAsync.Timeout =
         System.Threading.Timeout.Infinite


      m_objSiccoloProcessorAsync.BeginBackupDatabase _
         (objLoginInfo.ServerAddress, _
          objLoginInfo.UserName, _
          objLoginInfo.UserPassword, _
          DatabaseName, _
          BackupFileName, _
          BackupName, _
          ErrorInfo, _
          New AsyncCallback(AddressOf Me._
                            BackupDatabase_Async_CallBack), _
          Nothing)

   Catch ex As Exception
      ErrorInfo = ex.Message

      m_objUIBackupDatabaseForm.Invoke( _
      New AsyncCallHandler_BackupDatabase _
         (AddressOf m_objUIBackupDatabaseForm. _
          PerformBackupDatabase_Async_CallBack), _
         False, _
         ErrorInfo)

   End Try
End Sub

Sequence of Events

1. Start the asynchronous call with BeginBackupDatabase().
2. BackupDatabase_Async_CallBack is executed.
3. BackupDatabase_Async_CallBack passes control back to the form via Invoke():

Private Sub BackupDatabase_Async_CallBack(ByVal result As IAsyncResult)
   Try
      Dim ErrorInfo As String = ""

      Dim CallBackResult As Boolean = True

      CallBackResult = _
         m_objSiccoloProcessorAsync.EndBackupDatabase(result, _
                                                      ErrorInfo)

      m_objUIBackupDatabaseForm.Invoke( _
      New AsyncCallHandler_BackupDatabase _
         (AddressOf m_objUIBackupDatabaseForm. _
          PerformBackupDatabase_Async_CallBack), _
          CallBackResult, _
          ErrorInfo)

   Catch ex_callback As Exception

      m_objUIBackupDatabaseForm.Invoke( _
      New AsyncCallHandler_BackupDatabase _
         (AddressOf m_objUIBackupDatabaseForm. _
          PerformBackupDatabase_Async_CallBack), _
          False, _
          "BackupDatabase_Async_CallBack(): " & ex_callback.Message)
   End Try
End Sub

And the form:

Friend Sub PerformBackupDatabase_Async_CallBack _
   (ByVal CallBackResult As Boolean, _
   ByVal ErrorInfo As String)

   Try

      If Not CallBackResult Then
         Throw New Exception(ErrorInfo)
      End If

      MessageBox.Show("Backup completed (async)" & vbCrLf, _
                      "Siccolo - Backup Database", _
                      MessageBoxButtons.OK, _
                      MessageBoxIcon.Asterisk, _
                      MessageBoxDefaultButton.Button1)

   Catch ex As Exception

      MessageBox.Show("Failed to perform database backup (async):" _
                      & vbCrLf & _
                      "-----------------------------------" _
                      & vbCrLf & _
                      ErrorInfo & vbCrLf & _
                      "-----------------------------------", _
                      "Siccolo - Backup Database", _
                      MessageBoxButtons.OK, _
                      MessageBoxIcon.Exclamation, _
                      MessageBoxDefaultButton.Button1)

   Finally

      Cursor.Current = Cursors.Default

   End Try
End Sub

Points of Interest

If you would like to read more on this story, please take a look at Siccolo - Free Mobile Management Tool For SQL Server and more articles at Siccolo Articles.