在辅助进程中运行mssql备份 [英] Running mssql backup in a secondary process

问题描述

我正在尝试在辅助进程中在MSSQL Express数据库上运行备份，以免干扰访问应用程序功能的用户。



以下是我的主申请表上由计时器触发的辅助流程的代码：

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.:

    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>

我尝试了什么：



我已经运行了子 - 行，它正确执行备份。我已经验证了通过调用其他subs来运行辅助进程的代码。

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.

推荐答案

我通过添加wait语句修改了在辅助进程中运行备份的sub建议。该方法现在可以正常运行而不会崩溃：

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:

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

这篇关于在辅助进程中运行mssql备份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！