Web service method to backup database from mobile device using SQL Backup Command and asynchronous method calls
2.94/5 (10 votes)
Mar 20, 2007
2 min read
26751
Perform Backup SQL Server Database using Web Method/Web Service from Mobile Device
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 interactionsoLoginInfo- instance of a structure to hold SQL Server name, user credentialsUpdateIncomingStatus- 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 asynchronousoperation 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 informationto 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:
- start asynchronous call with
BeginBackupDatabase() BackupDatabase_Async_CallBackis executedBackupDatabase_Async_CallBackpasses 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