。NET备份与恢复（修改）

缺点：无进度条，无提示；
附上暴赱『零零伍』的 .Net下的数据备份和还原 （C#，有进度条）
http://blog.csdn.net/Eddie005/archive/2004/11/15/182060.aspx


Imports System.Exception

 
    Dim backupid As Integer
    Dim backupname As String
    Dim sql As String
    Dim mysqlconn As SqlClient.SqlConnection

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If InStr("," & Session("admin_flag") & ",", ",37,") = 0 Then
            Response.Redirect("admin_login.aspx")
        Else
            getdata.myconn = New SqlClient.SqlConnection
            getdata.myconn.ConnectionString = (xinxi.stringconnection)
            If Not Page.IsPostBack Then
                DropDownListAdd()
                Panel1.Visible = True
                Panel2.Visible = False
            End If
        End If
        '在此处放置初始化页的用户代码  
    End Sub

    Sub DropDownListAdd()
        sql = "select backupid,backupname,backupdate from databackup order by backupdate desc"
        DropDownList1.DataSource = getdata.getdataview(sql, "databackup")
        DropDownList1.DataValueField = "backupname"
        DropDownList1.DataTextField = "backupdate"
        DropDownList1.DataBind()
    End Sub

    Private Sub Submit1_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit1.ServerClick
        If Text1.Value = "" Then
            Response.Write("<script for=window event=onload>window.alert('请输入备份文件名！');</" & "script>")
        Else
            sql = "select backupid,backupname from databackup where backupname='" & Text1.Value & "'"
            getdata.myconn.Open()
            Dim Reader As SqlClient.SqlDataReader = getdata.getdatareader(sql)
            If Reader.Read() Then
                backupid = Reader.Item(0)
                Reader.Close()
                sql = "update databackup set backupdate='" & Now() & "',backupuser='" & Session("admin_username") & "'where backupid=" & backupid
                getdata.executesql(sql)
                Kill(Server.MapPath("backup") & "/" & Text1.Value)
            Else
                Reader.Close()
                sql = "insert into databackup(backupname,backupdate,backupuser) values('" & Text1.Value & "','" & Now() & "','" & Session("admin_username") & "')"
                getdata.executesql(sql)
            End If
            sql = "backup database tianxiang to disk='" & Server.MapPath("backup") & "/" & Text1.Value & "'"
            getdata.executesql(sql)
            On Error Resume Next
            getdata.myconn.Close()
            If Err.Number <> 0 Then
                Response.Write("<script for=window event=onload>window.alert('错误：")
                Response.Write(Err.Description)
                Response.Write("');</" & "script>")
            Else
                Response.Write("<script for=window event=onload>window.alert('数据备份成功！');</" & "script>")
            End If
        End If
    End Sub

    Private Sub Submit2_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit2.ServerClick
        If DropDownList1.SelectedValue = "" Then
            Response.Write("<script for=window event=onload>window.alert('没有可以恢复的备份！');</" & "script>")
        Else
            Try
                If Not Me.recoverDb() Then  'recoverDb为数据库恢复过程，其代码在下面
                    Exit Sub
                End If
                Response.Write("<script for=window event=onload>window.alert('数据恢复成功，请退出后台重新登陆或者刷新页面再继续操作！');</" & "script>")
            Catch ex As Exception

                Response.Write(ex.Message)

            End Try

        End If
    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
        Dim backupDir As String

        Try
            '判断备份路径参数里面是否制定路径
            If backupDir = Nothing Then
                If Not System.IO.Directory.Exists(Server.MapPath("backup")) Then
                    Response.Write("<script for=window event=onload>window.alert('磁盘上无备份文件，请检查！');</" & "script>")
                    Return False
                End If
                backupDir = Server.MapPath("backup")
            End If

            strbak = Server.MapPath("backup/") & DropDownList1.SelectedValue
            strsql = "restore database tianxiang from disk='" & strbak & "' with recovery" '数据库恢复语句

            mysqlconn.Open()
            mysqlcomm = New SqlClient.SqlCommand(strsql, mysqlconn)
            mysqlcomm.ExecuteNonQuery()

            strsql = "alter database tianxiang set online"
            mysqlcomm = Nothing
            mysqlcomm = New SqlClient.SqlCommand(strsql, mysqlconn)
            mysqlcomm.ExecuteNonQuery()
            mysqlconn.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
        mysqlconn = New SqlClient.SqlConnection
        mysqlconn.ConnectionString = "server=(local);database=master;uid=tianxiang;pwd=asd;"
        Try
            Me.mysqlconn.Open() '连接master数据库，打开连接
            '查出所有用户的spid
            sqlstr = "select spid from sysprocesses where dbid=(select dbid from sysdatabases where name like 'tianxiang')"
            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