在vb.net中使用数据库备份文件还原sqlserver数据库

在应用程序界面上设置一button按钮为恢复按钮

其click事件为恢复过程

 Private Sub Button_recover_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button_recover.Click

        '执行恢复过程
        Try
          If Not Me.recoverDb() Then  ’recoverDb为数据库恢复过程，其代码在下面
                Exit Sub
            End If

           MsgBox("数据恢复成功！", MsgBoxStyle.OKOnly)
            Me.Button_recover.Enabled = True
            Me.Button_backup.Enabled = True
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OKOnly)
            Me.Button_recover.Enabled = True
            Me.Button_backup.Enabled = True
        End Try
    End Sub

 '恢复数据
    Private Function recoverDb() As Boolean
        'kill掉所有用户，因为在有用户连接数据库的时候不允许恢复
        Try
            killAllUser()    ‘kill掉所用用户的方法见下面的代码
        Catch ex As Exception
            Throw ex
        End Try

        '恢复数据库
        Dim mysqlcomm As SqlClient.SqlCommand
        Dim strsql As String
        Dim strbak As String
        Try
            '判断备份路径参数里面是否制定路径
            If backupDir = Nothing Then

                If Not System.IO.Directory.Exists(Application.StartupPath + "/backup") Then
                    MsgBox("没有备份数据！", MsgBoxStyle.OKOnly)
                    Return False
                End If
                backupDir = Application.StartupPath + "/backup"
            End If

            strbak = Application.StartupPath + "/backup"
            strsql = "restore database wjb from disk='" + strbak + "/wjb.bak' with recovery"  ’数据库恢复语句
            mysqlconn.Open()
            mysqlcomm = New SqlClient.SqlCommand(strsql, mysqlconn)
            mysqlcomm.ExecuteNonQuery()

            strsql = "alter database wjb set online"
            mysqlcomm = Nothing
            mysqlcomm = New SqlClient.SqlCommand(strsql, mysqlconn)
            mysqlcomm.ExecuteNonQuery()
            sqlconn.Close()
            Return True
        Catch ex As Exception
            If Not mysqlconn.State.Closed = ConnectionState.Closed Then
                mysqlconn.Close()
            End If
            Throw ex
        End Try
    End Function

‘清除用户连接

 Private Sub killAllUser()
        Dim sqlstr As String
        Dim myadapter As SqlClient.SqlDataAdapter
        Dim mydataset As New DataSet
        Try
            Me.mysqlconn.Open() '连接master数据库，打开连接
            '查出所有用户的spid
            sqlstr = "select spid from sysprocesses where dbid=(select dbid from sysdatabases where name like 'wjb')"
            myadapter = New SqlClient.SqlDataAdapter(sqlstr, mysqlconn)
            myadapter.Fill(mydataset, "sysprocesses")

            Dim i As Integer
            Dim mysqlcomm As SqlClient.SqlCommand
            For i = 0 To mydataset.Tables(0).Rows.Count - 1
                '删除用户spid
                Dim strkill As String
                strkill = "kill " + CType(mydataset.Tables(0).Rows(i).Item(0), String)
                mysqlcomm = New SqlClient.SqlCommand(strkill, mysqlconn)
                mysqlcomm.ExecuteNonQuery()
            Next
            mysqlconn.Close()
        Catch ex As Exception
            If Not mysqlconn.State.Closed = ConnectionState.Closed Then
                mysqlconn.Close()
            End If
            Throw ex
        End Try

    End Sub

使用上述代码就可以恢复数据库了。

基本步骤就是：

1 连接master数据库

2 kill掉所有用户的连接

3 恢复数据库

注明：在数据库恢复完后，必须推出应用程序系统重新登陆方可。