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

浏览： ℃

字体：大中小

发布时间：2013-12-21 10:33:12

来源：

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

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

Imports System.Data.SqlClientPublic Class Form1    '备份数据库     'BACKUP DATABASE Test TO DISK='' 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        '选择备份路径        FolderBrowserDialog1.ShowDialog()        path = FolderBrowserDialog1.SelectedPath        If path = Nothing Then            MessageBox.Show("文件名不能为空", "系统提示")            Exit Sub        End If        '执行sql命令语句，备份数据库        cmd = New SqlCommand("BACKUP DATABASE test3 TO DISK='" & path & "/backup' 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    '还原数据库    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            '获取当前文件名筛选器字符            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()            '查询与数据库有关的进程            Dim cm As SqlCommand = New SqlCommand("use master select spid from master..sysprocesses where dbid=db_id('test3')", cn)            mydr = cm.ExecuteReader()            Dim cm1 As SqlCommand = New SqlCommand()            cm1.Connection = cn1            While (mydr.Read())                '杀死进程                str = "kill " & mydr("spid").ToString()                cm1.CommandText = str                cm1.CommandType = CommandType.Text                Application.DoEvents()                cm1.ExecuteNonQuery()            End While            mydr.Close()            '使要还原的数据库脱机            cm = New SqlCommand("ALTER DATABASE test3 SET OFFLINE WITH ROLLBACK IMMEDIATE", cn)            cm.ExecuteNonQuery()            '恢复备份            cm = New SqlCommand("RESTORE DATABASE test3 FROM DISK='" & all & "' WITH REPLACE", cn)            cm.ExecuteNonQuery()            '使要还原的数据库联机            cm = New SqlCommand("ALTER DATABASE test3 SET ONLINE WITH ROLLBACK IMMEDIATE", cn)            cm.ExecuteNonQuery()            MsgBox("恢复成功,软件自动关闭，请重新启动本系统！")            '关闭数据库连接            cn.Close()            cn1.Close()            Me.Close()        Else            Exit Sub        End If    End SubEnd Class

错误代码一：

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。杀进程前当然要先找出当前该数据库所有的进程了。