我有一个按钮Button_recover一个下拉列表DropDownList1。列表中绑定了备份过的数据库名
我要恢复的数据库名叫tianxiang
自己看不出来到底哪里出错了,,大家帮帮忙啊~~~~~~~~~~~~~~~~
Private Sub Button_recover_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button_recover.Click
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>")
Me.Button_recover.Enabled = True Catch ex As Exception
Response.Write("<script for=window event=onload>window.alert('")
Response.Write(ex.Message)
Response.Write("');</" & "script>")
Me.Button_recover.Enabled = True
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/") & DropDownList1.SelectedValue) Then
Response.Write("<script for=window event=onload>window.alert('磁盘上无备份文件,请检查!');</" & "script>")
Return False
End If
backupDir = Server.MapPath("backup/") & DropDownList1.SelectedValue
End If strbak = Server.MapPath("backup/") & DropDownList1.SelectedValue
strsql = "restore database tianxiang from disk='" + strbak + "/" & DropDownList1.SelectedValue & " 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
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
我要恢复的数据库名叫tianxiang
自己看不出来到底哪里出错了,,大家帮帮忙啊~~~~~~~~~~~~~~~~
Private Sub Button_recover_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button_recover.Click
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>")
Me.Button_recover.Enabled = True Catch ex As Exception
Response.Write("<script for=window event=onload>window.alert('")
Response.Write(ex.Message)
Response.Write("');</" & "script>")
Me.Button_recover.Enabled = True
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/") & DropDownList1.SelectedValue) Then
Response.Write("<script for=window event=onload>window.alert('磁盘上无备份文件,请检查!');</" & "script>")
Return False
End If
backupDir = Server.MapPath("backup/") & DropDownList1.SelectedValue
End If strbak = Server.MapPath("backup/") & DropDownList1.SelectedValue
strsql = "restore database tianxiang from disk='" + strbak + "/" & DropDownList1.SelectedValue & " 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
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
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货