VB.NET恢复SQL Server数据库代码无报错但未生效，求排查

嘿，我一眼就瞅出你代码里的问题啦！你这段代码的核心bug是连续三次给strQuery变量赋值，导致前两个关键的SQL语句（设置单用户、恢复数据库）根本没被执行——最后你只运行了ALTER DATABASE service_station SET MULTI_USER...这一句，数据库当然不会有恢复动作啦！

问题细节拆解 #

你看这段代码：

strQuery = "ALTER DATABASE service_station set SINGLE_USER WITH ROLLBACK IMMEDIATE"
strQuery = "RESTORE DATABASE service_station FROM disk='" & filename & "' WITH REPLACE,RECOVERY"
strQuery = "ALTER DATABASE service_station SET MULTI_USER WITH ROLLBACK IMMEDIATE"

每次赋值都会覆盖掉strQuery之前的内容，所以最终cmd.ExecuteNonQuery()只执行了最后一行的SQL，前面的设置单用户和恢复操作完全没跑起来。

修复方案 #

有两种靠谱的修复方式，你可以选适合自己的：

方式1：拼接多语句一次性执行

把三个SQL语句用分号分隔，拼成一个完整的字符串执行：

Sub restore_db()
    Dim con As New SqlConnection("Data Source=HP;Database=Master;integrated security=SSPI;")
    con.Open()
    Dim filename As String
    Dim strQuery As String
    Dim objdlg As New OpenFileDialog
    
    objdlg.FileName = "service_station"
    objdlg.Filter = "SQL Server database backup files|*.bak"
    
    ' 先判断用户是否选择了文件，避免空路径错误
    If objdlg.ShowDialog() = DialogResult.OK Then
        filename = objdlg.FileName
        
        ' 拼接三个SQL语句，用分号分隔
        strQuery = "ALTER DATABASE service_station set SINGLE_USER WITH ROLLBACK IMMEDIATE;" & _
                   "RESTORE DATABASE service_station FROM disk='" & filename & "' WITH REPLACE,RECOVERY;" & _
                   "ALTER DATABASE service_station SET MULTI_USER WITH ROLLBACK IMMEDIATE;"
        
        Dim cmd As New SqlCommand(strQuery, con)
        cmd.ExecuteNonQuery()
    End If
    
    con.Close()
End Sub

方式2：分三次执行独立命令（更稳妥）

对于RESTORE这类数据库操作，分开执行每个命令会更可靠，也便于排查单步问题：

Sub restore_db()
    Dim con As New SqlConnection("Data Source=HP;Database=Master;integrated security=SSPI;")
    con.Open()
    Dim filename As String
    Dim objdlg As New OpenFileDialog
    
    objdlg.FileName = "service_station"
    objdlg.Filter = "SQL Server database backup files|*.bak"
    
    If objdlg.ShowDialog() = DialogResult.OK Then
        filename = objdlg.FileName
        
        ' 1. 切换到单用户模式，强制回滚未完成的事务
        Using cmdSingle As New SqlCommand("ALTER DATABASE service_station set SINGLE_USER WITH ROLLBACK IMMEDIATE", con)
            cmdSingle.ExecuteNonQuery()
        End Using
        
        ' 2. 执行数据库恢复
        Using cmdRestore As New SqlCommand($"RESTORE DATABASE service_station FROM disk='{filename}' WITH REPLACE,RECOVERY", con)
            cmdRestore.ExecuteNonQuery()
        End Using
        
        ' 3. 恢复多用户模式
        Using cmdMulti As New SqlCommand("ALTER DATABASE service_station SET MULTI_USER WITH ROLLBACK IMMEDIATE", con)
            cmdMulti.ExecuteNonQuery()
        End Using
    End If
    
    con.Close()
End Sub

额外注意事项 #

• 确保执行代码的Windows账号拥有SQL Server数据库恢复权限和修改数据库状态的权限，否则可能会出现权限不足的隐性错误（虽然你说没报错，但最好确认一下）。
• 加上ShowDialog()的返回值判断，避免用户取消选择文件后，用空路径执行RESTORE导致的异常。

内容的提问来源于stack exchange，提问作者AlQaqaa Benghuzi