Click here to Skip to main content
15,921,530 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to run a backup on our MSSQL Express db in a secondary process so as not to interfere with the users accessing the features of the application.

Here is the code for the secondary process that is triggered by a timer on my main application form.:

C#
    Private Sub CheckBackUp()
        ' check to see if a backup needs to be done
        Dim DoBackup As Boolean = False

        If IsBackUpLockFile() = True Then Exit Sub

        If ConfigCL.IsName("Last Backup Date") = True Then
            Dim LastBackupDate As Date = ConfigCL.Value("Last Backup Date")
            If LastBackupDate > SysStartDate Then
                Dim span As TimeSpan = Now.Subtract(LastBackupDate)

                If span.Days >= 1 Then
                    DoBackup = True
                ElseIf span.Hours >= BackupDateDelta Then
                    DoBackup = True
                End If

            End If

        Else
            ConfigCL.Add("Last Backup Date", Now.ToString, 1, "Last System Backup Date")
            DoBackup = True
        End If

        If DoBackup = True Then

            Dim task1 As New Task(Sub() Backup_Execute(SQLDataServer, SQLDataBaseName, Path.Combine(ServerPath, DataFileName)))

            Try
                task1.Start()

            Catch ae As AggregateException
                
            End Try

            task1 = Nothing


        End If

    End Sub


Here is the code for the Backup_Execute sub:

    Public Sub Backup_Execute(ByVal SQLDataServer As String, ByVal SQLDataBaseName As String, ByVal FullDataFileName As String)
        Dim strConn As String = "Data Source=" & SQLDataServer & ";Initial Catalog= '" & SQLDataBaseName & "'" & ";Integrated Security=TRUE"
        Dim con As New SqlConnection(strConn)
        Dim strQuery As String = "backup database " & SQLDataBaseName & " to disk='" & FullDataFileName & "'"

        Try
            con.Open()
            Dim cmd As SqlCommand = New SqlCommand(strQuery, con)
            cmd.CommandTimeout = 180 ' 3 minutes
            Dim iNumRows As Integer = cmd.ExecuteNonQuery()
            ConfigCL.Value("Last Backup Date") = Now.ToString

        Catch SqlEx As SqlException
            MsgBox("SQL Exception when running backup" & vbCrLf & SqlEx.ToString)

        Catch Ex As Exception
            MsgBox("Exception when running backup" & vbCrLf & Ex.ToString)


        End Try

        con.Close()
        con.Dispose()

    End Sub


The above sub runs fine when it is run in-line. The main application form has a menu option to run this explicitly so I know it is not the problem.

For some reason that I do not know, the backup crashes when run in a secondary process.  When it comes to the line &quot;&lt;pre lang=&quot;VB.NET&quot;&gt;Dim iNumRows As Integer = cmd.ExecuteNonQuery()&amp;quot;&lt;/pre&gt; it goes back to my main application user form. No exception is thrown so I am not able to determine what the problem is.</pre>


What I have tried:

I have run the sub in-line and it executes the backup correctly. I have verified the code to run the secondary process via calling other subs.
Posted
Updated 10-Mar-16 9:28am
Comments
an0ther1 17-Feb-16 17:37pm    
According to MSDN you should have task1.Wait() after task1.Start(). This will allow you to catch exceptions.
Refer: https://msdn.microsoft.com/en-us/library/system.threading.tasks.task(v=vs.110).aspx

1 solution

I modified the sub that runs the backup in a secondary process by adding the wait statement as suggested. The method now runs correctly without crashing:

C#
Private Sub CheckBackUp()
    ' check to see if a backup needs to be done
    Dim DoBackup As Boolean = False

    If IsBackUpLockFile() = True Then Exit Sub

    If ConfigCL.IsName("Last Backup Date") = True Then
        Dim LastBackupDate As Date = ConfigCL.Value("Last Backup Date")
        If LastBackupDate > SysStartDate Then
            Dim span As TimeSpan = Now.Subtract(LastBackupDate)

            If span.Days >= 1 Then
                DoBackup = True
            ElseIf span.Hours >= BackupDateDelta Then
                DoBackup = True
            End If

        End If

    Else
        ConfigCL.Add("Last Backup Date", Now.ToString, 1, "Last System Backup Date")
        DoBackup = True
    End If

    If DoBackup = True Then

        Dim task1 As New Task(Sub() Backup_Execute(SQLDataServer, SQLDataBaseName, Path.Combine(ServerPath, DataFileName)))

        Try
            task1.Start()
            task1.Wait()

        Catch ae As AggregateException
            Dim tstr As String = ae.ToString

        End Try

        task1 = Nothing


    End If

End Sub
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900