Web Service Method to Back Up a Database from a Mobile Device

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.