frm_backupdata.vb

图书管理系统 VB加SQL 数据库在文件里的DATA里 进到SQL里附加进去就可以使用了

        Me.Button4.TabIndex = 3
        Me.Button4.Text = "浏览"
        '
        'Frm_BackupData
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(474, 368)
        Me.Controls.Add(Me.TabControl1)
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedToolWindow
        Me.Name = "Frm_BackupData"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.Text = "备份数据"
        Me.TabControl1.ResumeLayout(False)
        Me.TabPage1.ResumeLayout(False)
        Me.TabPage2.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private db As New DataBase
    'Private sqlconnStr As String = "Server='" & compname & "';Database=tushugl;User ID=sa;Password=;"
    Private sqlconnStr As String = "Data Source=" & compname & ";Integrated Security=SSPI;Initial Catalog=tushugl"

    Dim mysqlconn As SqlConnection = New SqlConnection(sqlconnStr)
    '定义一个变量保存备份路径
    Dim backupdir As String
    '定义一个变量保存备份文件路径
    Dim backupdir1 As String
    '定义一个变量保存还原路径
    Dim restoredir As String

    '窗体加载
    Private Sub Frm_BackupData_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If userSF <> "超级管理员" Then
            TabControl1.TabPages(1).Enabled = False
        End If
    End Sub

    '浏览1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        SaveFileDialog1.AddExtension = True
        SaveFileDialog1.DefaultExt = ".bak"
        SaveFileDialog1.CheckPathExists = True
        SaveFileDialog1.RestoreDirectory = True
        SaveFileDialog1.InitialDirectory = "c:\"
        SaveFileDialog1.ShowDialog()
    End Sub

    '保存对话框的FileOk事件
    Private Sub SaveFileDialog1_FileOk(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles SaveFileDialog1.FileOk
        TextBox1.Text = SaveFileDialog1.FileName
    End Sub

    '确定
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If TextBox1.Text = "" Then
            MessageBox.Show("路径不能为空！", "提示", MessageBoxButtons.OK)
            Exit Sub
        End If
        Try
            '备份数据库
            backupdir = TextBox1.Text.Trim
            Me.backupDb()
            MsgBox("备份操作成功！", MsgBoxStyle.OKOnly)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OKOnly)
        End Try

    End Sub
    '取消
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub

    '浏览2
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        OpenFileDialog1.CheckFileExists = True
        OpenFileDialog1.Filter = "所有文件 (*.*)|*.*"
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            TextBox2.Text = OpenFileDialog1.FileName
        End If

    End Sub

    '还原
    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        If TextBox2.Text = "" Then
            MessageBox.Show("请输入数据库备份文件的路径！", "提示", MessageBoxButtons.OK)
            Exit Sub
        End If
        If TextBox3.Text = "" Then
            MessageBox.Show("请输入数据库还原后保存的路径！", "提示", MessageBoxButtons.OK)
            Exit Sub
        End If

        '执行还原过程
        Try
            backupdir1 = TextBox2.Text.Trim
            restoredir = TextBox3.Text.Trim
            'recoverDb为数据库恢复过程，其代码在下面
            If Not Me.recoverDb() Then
                Exit Sub
            End If
            MsgBox("数据恢复成功！", MsgBoxStyle.OKOnly)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OKOnly)

        End Try
    End Sub
    '关闭
    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Me.Close()
    End Sub

    '定义一个过程备份数据库
    Private Sub backupDb()
        Dim mysqlcomm As SqlCommand
        Dim strsql As String
        Dim strbak As String
        Try
            strbak = backupdir
            strsql = "backup database tushugl to disk='" + strbak + "' with name='Databukup backup all',description='Full Backup Of Databukup'"    '备份用的sql语句
            mysqlconn.Open()     'mysqlconn为创建的连接
            mysqlcomm = New SqlCommand(strsql, mysqlconn)   '执行备份
            mysqlcomm.ExecuteNonQuery()
            mysqlconn.Close()
        Catch ex As Exception
            If Not mysqlconn.State.Closed = ConnectionState.Closed Then
                mysqlconn.Close()
            End If
            Throw ex
        End Try
    End Sub

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

        '恢复数据库
        Dim mysqlcomm As SqlCommand
        Dim strsql As String
        Dim strbak As String
        'Dim sqlconnStr1 As String = "Server='" & compname & "';Database=master;User ID=sa;Password=;"
        Dim sqlconnStr1 As String = "Data Source=" & compname & ";Integrated Security=SSPI;Initial Catalog=master"

        Dim mysqlconn1 As SqlConnection = New SqlConnection(sqlconnStr1)
        Try
            '判断备份路径参数里面是否制定路径
            If backupdir1 = Nothing Then
                MessageBox.Show("请输入数据库备份文件的路径！", "提示", MessageBoxButtons.OK)
                Return False
                Exit Function
            End If

            strbak = backupdir1
            '数据库恢复语句
            'strsql = "restore database tushugl from disk='" + strbak + "' with recovery"
            strsql = "RESTORE DATABASE tushugl FROM DISK = '" + strbak + "' With Move 'tushugl_Data' TO '" & restoredir & "\tushugldb.mdf',Move 'tushugl_Log' TO '" & restoredir & "\tushugldb.ldf'"
            mysqlconn1.Open()
            mysqlcomm = New SqlCommand(strsql, mysqlconn1)
            mysqlcomm.ExecuteNonQuery()

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

    '清除用户连接
    Private Sub killAllUser()
        Dim sqlstr As String
        Dim myadapter As SqlDataAdapter
        Dim mydataset As New DataSet
        'Dim sqlconnStr1 As String = "Server='" & compname & "';Database=master;User ID=sa;Password=;"
        Dim sqlconnStr1 As String = "Data Source=" & compname & ";Integrated Security=SSPI;Initial Catalog=master"

        Dim mysqlconn1 As SqlConnection = New SqlConnection(sqlconnStr1)
        Try
            mysqlconn1.Open() '连接master数据库，打开连接
            '查出所有用户的spid
            sqlstr = "select spid from sysprocesses where dbid=(select dbid from sysdatabases where name like 'tushugl')"
            myadapter = New SqlDataAdapter(sqlstr, mysqlconn1)
            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, mysqlconn1)
                mysqlcomm.ExecuteNonQuery()
            Next
            mysqlconn1.Close()
        Catch ex As Exception
            If Not mysqlconn.State.Closed = ConnectionState.Closed Then
                mysqlconn1.Close()
            End If
            Throw ex
        End Try
    End Sub

End Class