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