代码还原数据库出错怎么办?
大家好,我用VB.ENT来备份并还原数据时,如果该窗体作为单独运行时还原会成功,可是如果该窗体作为别的窗体的子窗口时还原就会出错------------数据库正在使用,未能获得数据库的排它访问权,所以还原失败.
这样应该怎样解决才好呢?
imports system.data.sqlclient
imports system
imports system.windows.forms
module feeling
public con as new sqlconnection("data source=127.0.0.1;database=mydatabase;integrated security=true;")
public con_bak as new sqlconnection("data source=127.0.0.1;database=mydatabase;integrated security=true;")
public ds as new dataset
public spid_num as integer
public dbname as string
public str_bak as string
public str_res as string
public str_save as string
public str_load as string
sub main()
application.run(new form1())
end sub
public class form1
inherits form
...
Private Sub Button1_Click(ByVal obj As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
con.Open()
Dim cmd As New SqlCommand("select count(*) from login where username='" & Trim(TextBox1.Text) & "' and password= '" & Trim(TextBox2.Text) & "' ", con)
If cmd.ExecuteScalar = 1 Then
Dim frm As New Form2
Me.Hide()
frm.Show()
Else
MsgBox("该用户不存在!")
End If
Catch sql As SqlException
MsgBox("sql information:" & sql.Message)
Catch ex As Exception
MsgBox("system information:" & ex.Message)
Finally
con.Close()
End Try
End Sub
...
end class
public class form2
inherits form
...
Private Sub Form2_Load(ByVal obj As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
con_bak.Open()
Dim cmd As New SqlCommand("select name from sysdatabases where name <> 'master' ", con_bak)
Dim rd As SqlDataReader
rd = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While rd.Read
ComboBox1.Items.Add(rd.Item(0))
End While
ComboBox1.SelectedIndex = 0
Catch sql As SqlException
MsgBox("sql information:" & sql.Message)
Catch ex As Exception
MsgBox("system information:" & ex.Message)
Finally
con_bak.Close()
End Try
Try
Dim cmd As New SqlCommand("EXEC sp_who", con_bak)
Dim da As New SqlDataAdapter
da.SelectCommand = cmd
con_bak.Open()
da.Fill(ds, "spwho")
DataGrid1.DataSource = ds.Tables("spwho")
Catch slq As SqlException
MsgBox("slq information:" & slq.Message)
Catch ex As Exception
MsgBox("system information:" & ex.Message)
Finally
con_bak.Close()
End Try
end sub
Private Sub backup_Click(ByVal obj As System.Object, ByVal e As System.EventArgs) Handles backup.Click
Try
Dim s As New SaveFileDialog
s.ShowDialog()
s.Filter = "(*.bak)|*.bak"
str_bak = s.FileName
If str_bak <> "" Then
str_save = "use master backup database " & Trim(ComboBox1.Text) & " to disk='" & Trim(str_bak) & "' "
Dim cmd As New SqlCommand
cmd.Connection = con_bak
cmd.CommandText = str_save
con_bak.Open()
cmd.ExecuteNonQuery()
MsgBox("successfully")
Else
MsgBox("wrongly")
End If
Catch sql As SqlException
MsgBox("sql information:" & sql.Message)
Catch ex As Exception
MsgBox("system information:" & ex.Message)
Finally
con_bak.Close()
End Try
End Sub
Private Sub restore_Click(ByVal obj As System.Object, ByVal e As System.EventArgs) Handles restore.Click
Try
Dim a As New OpenFileDialog
a.ShowDialog()
a.Filter = "(*.bak)|*.bak"
str_res = a.FileName
If str_res <> "" Then
str_load = " use master restore database " & Trim(ComboBox1.Text) & " from disk='" & Trim(str_res) & "'"
Dim cmd As New SqlCommand
cmd.Connection = con_bak
cmd.CommandText =str_load
con_bak.Open()
cmd.ExecuteNonQuery()
MsgBox("successfully")
Else
MsgBox("wrongly")
End If
Catch sql As SqlException
MsgBox("sql information:" & sql.Message)
MsgBox("请选择datagrid里的***数据库(正在使用,未能获得数据库的排它访问权)dbname=***的行后,点kill按钮,然后再点backup按钮,这即可实现排它功效!")
Catch ex As Exception
MsgBox("system information:" & ex.Message)
Finally
con_bak.Close()
End Try
End Sub
Private Sub kill_Click(ByVal obj As System.Object, ByVal e As System.EventArgs) Handles kill.Click
spid_num = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 0)
dbname= DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 6)
Try
Dim cmd As New SqlCommand
cmd.CommandText = "kill " & spid_num
cmd.Connection = con_bak
con_bak.Open()
cmd.ExecuteNonQuery()
MsgBox("数据库:" & dbname.ToString & "的进程已成功结束,你可以对该数据库进行还原了!")
Catch slq As SqlException
MsgBox("slq information:" & slq.Message)
Catch ex As Exception
MsgBox("system information:" & ex.Message)
Finally
con_bak.Close()
End Try
Try
Dim cmd As New SqlCommand("exec sp_who", con_bak)
Dim da As New SqlDataAdapter
da.SelectCommand = cmd
ds.clear()
con_bak.Open()
da.Fill(ds, "spwho")
DataGrid1.DataSource = ds.Tables("spwho")
Catch sql As SqlException
MsgBox("sql information:" & sql.Message)
Catch ex As Exception
MsgBox("system information:" & ex.Message)
Finally
con_bak.Close()
End Try
end sub
end class
end module
