不同名数据库的恢复

    '清除用户连接



    Private Sub killAllUser()

        Dim sqlstr As String

        Dim myadapter As SqlDataAdapter

        Dim mydataset As New DataSet

        Dim constr As String = "data source=.;initial catalog=master;uid=sa;pwd=sa;"

        Dim mysqlconn As SqlConnection = New SqlConnection(constr)

        Try

            mysqlconn.Open() '连接master数据库，打开连接

            '查出所有用户的spid

            sqlstr = "select spid from sysprocesses where dbid=(select dbid from sysdatabases where name like '数据库名')"

            myadapter = New SqlClient.SqlDataAdapter(sqlstr, mysqlconn)

            myadapter.Fill(mydataset, "sysprocesses")



            Dim i As Integer

            Dim mysqlcomm As 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 SqlCommand(strkill, mysqlconn)

                mysqlcomm.ExecuteNonQuery()

            Next

            mysqlconn.Close()

        Catch ex As Exception

            If Not mysqlconn.State = ConnectionState.Closed Then

                mysqlconn.Close()

            End If

            Throw ex

        End Try



    End Sub



    ''' <summary>

    ''' 还原数据库

    ''' </summary>

    ''' <param name="sender"></param>

    ''' <param name="e"></param>

    ''' <remarks></remarks>

    Private Sub 还原数据RToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 还原数据RToolStripMenuItem.Click

        Dim mysqlcomm As SqlCommand = New SqlCommand

        Dim strsql As String = String.Empty

        Dim strbak As String = String.Empty

        Dim fo As OpenFileDialog = New OpenFileDialog

        fo.DefaultExt = ".bak"

        fo.InitialDirectory = Application.StartupPath

        fo.Filter = "数据库文件|*.bak|所有文件|*.*"

        If fo.ShowDialog = Windows.Forms.DialogResult.OK Then

            Try

                killAllUser()    'kill掉所用用户的方法见下面的代码

            Catch ex As Exception

                Throw ex

            End Try

            strbak = fo.FileName

            strsql = "restore database sincehome from disk='" + 数据库路径 + "' with recovery"  '数据库恢复语句

            Dim constr As String = "data source=.;initial catalog=master;uid=sa;pwd=sa;"

            Dim mysqlconn As SqlConnection = New SqlConnection(constr)

            mysqlconn.Open()

            mysqlcomm = New SqlCommand(strsql, mysqlconn)

            mysqlcomm.ExecuteNonQuery()

            strsql = "alter database sincehome set online"

            mysqlcomm = Nothing

            mysqlcomm = New SqlClient.SqlCommand(strsql, mysqlconn)

            mysqlcomm.ExecuteNonQuery()

            mysqlconn.Close()

            mysqlcomm.Dispose()

            MsgBox("数据库还原成功", MsgBoxStyle.OkOnly, "提醒")

        End If

    End Sub