Web service method to backup database from mobile device using SQL Backup Command and asynchronous method calls

Introduction

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

Because process of backing up a database may take a few "lengthy" moments, Siccolo application is using
asynchronous method call to a web service.

The code presented allows mobile device to backup a database on a remote SQL Server.

Server Code

First, web method to backup a database on a SQL Server. For this we can use 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 - instance of a class handling all database/SQL Server interactions
• oLoginInfo - instance of a structure to hold SQL Server name, user credentials
• UpdateIncomingStatus - method that writes to event log on a server hosting this web service

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 = 600    '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.
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 = ""

     objSQLManager.BackupDatabaseForm = Me            'sets which form to invoke

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

Where objSQLManager - class on the client, handling all interactions with 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.

Backup Database asynchronous operation is implemented as two methods named BeginBackupDatabase and
EndBackupDatabase that begin and end the asynchronous operation BackupDatabase respectively.
BeginBackupDatabase method takes as many parameters 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:parameterstyle:="System.Web.Services.Protocols.SoapParameterStyle.Wrapped)" 
use:="System.Web.Services.Description.SoapBindingUse.Literal,"
 responsenamespace:="http://tempuri.org/" 
requestnamespace:="http://tempuri.org/" />  _
        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)
        End Function
        
        '''<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
        ...

BeginBackupDatabase method signature also includes two additional parameters - first of these defines an
AsyncCallback delegate that references a method BackupDatabase_Async_CallBack 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 BeginBackupDatabase
method throws exceptions, the exceptions are thrown before the asynchronous operation is started.
And if 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 asynchronous call with BeginBackupDatabase()
2. BackupDatabase_Async_CallBack is executed
3. BackupDatabase_Async_CallBack passes control back to 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