备份恢复数据库-中谷项目

WBOY

Lepaskan： 2016-06-07 15:22:16

asal

1064 orang telah melayarinya

每个系统都应该有数据库的备份和还原功能，以防各种情况下的数据库损坏造成不可挽回的损失。这个功能挺简单，但在代码实现过程中也多多少少遇到了些问题，我把错误都总结了出来，供大家参考学习。下面先给出正确的代码实现： Imports System.Data.SqlClient

每个系统都应该有数据库的备份和还原功能，以防各种情况下的数据库损坏造成不可挽回的损失。这个功能挺简单，但在代码实现过程中也多多少少遇到了些问题，我把错误都总结了出来，供大家参考学习。

下面先给出正确的代码实现：

Imports System.Data.SqlClient

Public Class Form1

    &#39;备份数据库 
    &#39;BACKUP DATABASE Test TO DISK=&#39;&#39; WITH Format   sql语句

    Private Sub btnBackup_Click(sender As Object, e As EventArgs) Handles btnBackup.Click

        Dim conn As SqlConnection
        conn = New SqlConnection("Data Source=.;Initial Catalog=test3;User ID=sa;Password=123456")
        Dim cmd As SqlCommand
        Dim path As String

        &#39;选择备份路径
        FolderBrowserDialog1.ShowDialog()
        path = FolderBrowserDialog1.SelectedPath
        If path = Nothing Then
            MessageBox.Show("文件名不能为空", "系统提示")
            Exit Sub
        End If
        &#39;执行sql命令语句，备份数据库
        cmd = New SqlCommand("BACKUP DATABASE test3 TO DISK=&#39;" & path & "\backup&#39; WITH format,BACKUP LOG WITH NORECOVERY", conn)
        conn.Open()
        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OkOnly, "系统提示")
            Exit Sub
        End Try
        MsgBox("备份成功")
        conn.Close()

    End Sub

    &#39;还原数据库
    Private Sub btnRecovery_Click(sender As Object, e As EventArgs) Handles btnRecovery.Click
        If MsgBox("真的要还原吗？数据会恢复到最近备份的数据！", MsgBoxStyle.YesNo, "系统提示") = MsgBoxResult.Yes Then

            Dim cn As New SqlConnection
            Dim cn1 As New SqlConnection
            Dim mydr As SqlDataReader
            Dim str As String
            Dim tmpConnectionString As String = "Data Source=.;Initial Catalog=test3;User ID=sa;Password=123456;pooling=false"
            Dim all As String

            &#39;获取当前文件名筛选器字符
            Me.OpenFileDialog1.Filter = "所有文件(*.*)|*.*|备份文件(*.bak)|*.bak"
            Me.OpenFileDialog1.ShowDialog()
            all = OpenFileDialog1.FileName
            If all = Nothing Then
                MessageBox.Show("文件名不能为空", "系统提示")
                Exit Sub
            End If
            cn.ConnectionString = tmpConnectionString
            cn1.ConnectionString = tmpConnectionString
            cn.Open()
            cn1.Open()

            &#39;查询与数据库有关的进程
            Dim cm As SqlCommand = New SqlCommand("use master select spid from master..sysprocesses where dbid=db_id(&#39;test3&#39;)", cn)
            mydr = cm.ExecuteReader()
            Dim cm1 As SqlCommand = New SqlCommand()
            cm1.Connection = cn1
            While (mydr.Read())
                &#39;杀死进程
                str = "kill " & mydr("spid").ToString()
                cm1.CommandText = str
                cm1.CommandType = CommandType.Text
                Application.DoEvents()
                cm1.ExecuteNonQuery()
            End While
            mydr.Close()

            &#39;使要还原的数据库脱机
            cm = New SqlCommand("ALTER DATABASE test3 SET OFFLINE WITH ROLLBACK IMMEDIATE", cn)
            cm.ExecuteNonQuery()

            &#39;恢复备份
            cm = New SqlCommand("RESTORE DATABASE test3 FROM DISK=&#39;" & all & "&#39; WITH REPLACE", cn)
            cm.ExecuteNonQuery()

            &#39;使要还原的数据库联机
            cm = New SqlCommand("ALTER DATABASE test3 SET ONLINE WITH ROLLBACK IMMEDIATE", cn)
            cm.ExecuteNonQuery()

            MsgBox("恢复成功,软件自动关闭，请重新启动本系统！")

            &#39;关闭数据库连接
            cn.Close()
            cn1.Close()
            Me.Close()
        Else
            Exit Sub
        End If
    End Sub
End Class

Salin selepas log masuk

错误代码一：

select spid from test3..sysprocesses where dbid=db_id('test3')

错误描述：

解决方法：

在sql语句前添加use master 即

use master select spid from master..sysprocesses where dbid=db_id('test3')

User master表示在master数据库执行该语句，spid指当前用户进程的会话 ID，master是系统数据库,它里面有很多对象,每个对象都有自己的所有者.如果没有指明所有者,系统就默认dbo为对象的所有者.一个对象完整的表达式为:数据库名.所有者.对象, 比如:master.dbo.sysobjects。杀进程前当然要先找出当前该数据库所有的进程了。