大家好,我用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

注:现在我们用的SQL SERVER2000 很多都不能(除该软件开发者或购买者...)删除主数据库master.所以以上的例子只提供了删除master数据库以外的任何数据库!

哦,好的,太感谢你了.
我的是这样的(交流一下),只是在还原数据库时会出错
Dim cn As New SqlConnection("data source='" & cnname & "';database=master;integrated security=true;")
Dim backupdb As String
Dim backupdisk As String
Dim restoredb As String
Dim restoredisk As String
Dim cmdatabase As New SqlCommand
Dim SaveFileDialog As New SaveFileDialog
Dim openfiledialog As New openfiledialog
Dim cmBackup As New SqlCommand
Dim cmRestore As New SqlCommand
Dim cmd As New SqlCommand("CREATE PROC p_backupDataBase(@db varchar(20),@disk varchar(100)) AS BACKUP DATABASE @db TO DISK=@disk", cn)
Dim cmd1 As New SqlCommand("CREATE PROC p_restoreDataBase(@db varchar(20),@disk varchar(100)) AS RESTORE DATABASE @db FROM DISK=@disk", cn)

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
cn.Close()
cn.Open()
cmd.ExecuteNonQuery()
cmd1.ExecuteNonQuery()
cn.Close()
Catch ex As Exception
'MsgBox(ex.ToString)
Finally
cn.Close()
End Try
Try
cn.Close()
'从SQL Server服务器获取所有数据库列表，通过comboBox显示
cn.Open()
cmdatabase.Connection = cn
cmdatabase.CommandText = "sp_helpdb"
cmdatabase.CommandType = CommandType.StoredProcedure
cmBackup.Connection = cn
cmBackup.CommandText = "[p_backupDataBase]"
cmBackup.CommandType = CommandType.StoredProcedure
cmBackup.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4)
cmBackup.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
cmBackup.Parameters.Add("@db", SqlDbType.VarChar, 20)
cmBackup.Parameters.Add("@disk", SqlDbType.VarChar, 100)

cmRestore.Connection = cn
cmRestore.CommandText = "[p_RestoreDataBase]"
cmRestore.CommandType = CommandType.StoredProcedure
cmRestore.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4)
cmRestore.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
cmRestore.Parameters.Add("@db", SqlDbType.VarChar, 20)
cmRestore.Parameters.Add("@disk", SqlDbType.VarChar, 100)

Dim dr As SqlDataReader
dr = cmdatabase.ExecuteReader(CommandBehavior.CloseConnection)
'将数据库列表绑定到控件combBox
Do While dr.Read()
combDataBaseList.Items.Add(dr.GetString(0))
Loop
dr.Close()
combDataBaseList.SelectedIndex = 0
Catch ex As Exception

End Try
End Sub

Private Sub butBackup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butBackup.Click
Try
If combDataBaseList.Text = "" Then
MsgBox("请选择要备份的数据库!")
Exit Sub
End If

'声明一个Result枚举变量，记录对话框的返回值，即判断单击了“打开”还是“取消”按钮
Dim Result As Windows.Forms.DialogResult
'设置文件筛选器
SaveFileDialog.Filter = "BAK格式(*.bak)|*.bak"
'设置对话框标题
SaveFileDialog.Title = "保存文件"
'打开对话框
Result = SaveFileDialog.ShowDialog()
'如果单击了“保存”按钮，并且filename不为空时，为变量@filename赋值
If Result = DialogResult.OK And Len(SaveFileDialog.FileName) > 0 Then
backupdisk = SaveFileDialog.FileName
End If
'为sqlCommand参数赋值

'备份数据库
Try
'打开连接，执行存储过程
If backupdisk <> "" Then
cmBackup.Parameters(1).Value = backupdb
cmBackup.Parameters(2).Value = backupdisk
cn.Open()
cmBackup.ExecuteNonQuery()
infoLabel.Text = "备份成功"
End If
Catch ex As SqlException
infoLabel.Text = "备份失败" + ex.Message
Catch ex As Exception
'异常处理
infoLabel.Text = "备份失败" + ex.Message
End Try
'关闭连接
cn.Close()
Catch ex As Exception

End Try
End Sub

Private Sub butRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butRestore.Click
Try
If combDataBaseList.Text = "" Then
MsgBox("请选择要还原的数据库!")
Exit Sub
End If
'声明一个Result枚举变量，记录对话框的返回值，即判断单击了“打开”还是“取消”按钮
Dim Result As Windows.Forms.DialogResult
'设置文件筛选器
openfiledialog.Filter = "BAK格式(*.bak)|*.bak"
'设置对话框标题
openfiledialog.Title = "打开文件"
'打开对话框
Result = openfiledialog.ShowDialog()
'如果单击了“打开”按钮，并且filename不为空时，为变量@filename赋值
If Result = DialogResult.OK And Len(openfiledialog.FileName) > 0 Then
restoredisk = openfiledialog.FileName
End If
'为sqlCommand参数赋值

'备份数据库
Try
'打开连接，执行存储过程
If restoredisk <> "" Then
cmRestore.Parameters(1).Value = restoredb
cmRestore.Parameters(2).Value = restoredisk
cn.Open()
cmRestore.ExecuteNonQuery()
infoLabel.Text = "恢复成功"
End If
Catch ex As SqlException
infoLabel.Text = "备份失败" + ex.Message
Catch ex As Exception
'异常处理
infoLabel.Text = "恢复失败" + ex.ToString()
End Try
'关闭连接
cn.Close()
Catch ex As Exception
End Try
End Sub

Private Sub combDataBaseList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles combDataBaseList.SelectedIndexChanged
Try
backupdb = combDataBaseList.Text
restoredb = combDataBaseList.Text
Catch ex As Exception
End Try
End Sub

你所做的实例可能在单个窗体下运行,那么还原master以外的所有数据库是不会出错.
但是,如果你是要做个系统,你所要用到的数据库就可能是别的数据库名如:fproducts. 当你登陆的时候,用到的登陆表是来自数据库welcome里的一个子表,当你登陆成功时,数据库的服务器自动会产生相应的进程,如:进程----select count(*) from login WHERE username= 'process' and password=85
该进程可在企业管理器下的--管理--当前活动--进程信息--- 查看详细信息:数据库=welcome.右击该进程,选择属性,即可看到进程的具体信息. 当你右击它,选择'取消进程'之后,在对先前已经备份的数据库(welcome),即可顺利完成.
关键:杀进程----kill spid

那请问你DATAGRID1里面显示的是什么内容,用来干嘛呢?

你可以打开SQL SERVER2000查询分析器
先执行语句:EXEC sp_who 返回的结果,就是显示在datagrid1中的信息
(注:sp_who是 提供关于当前 SQL Server 2000用户和进程的信息)
目的是用来杀进程(kill)-----请看:kill_Click 按钮下的单击事件(删除你在datagrid1中所选中的进程).

查看sql自带的"联机丛书",输入sp_who,kill之类的关键字,即可收到很多可用的信息.
(注:在查询分析器中可以执行的命令,一般都可在vb.net(windows)下实现相同的功效)

完整版:(数据库master能备份不能还原;数据库tempdb不能备份)
首先:在master数据库下创建如下存储过程:
create proc killprocess (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status<>-1
begin
exec('kill '+@spid)
fetch next from getspid into @spid
End
close getspid
deallocate getspid
End
GO
然后修改先前的代码:

imports system.data.sqlclient
imports system
imports system.windows.forms

module feeling
public killdb_spid As New SqlCommand
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=master;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
......
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
' Me.kill.Enabled = False
con_bak.ConnectionString = new_shared_con_str
con_bak.Open()
Dim cmd As New SqlCommand("select name from sysdatabases where name <> 'master' and name<>'tempdb'", 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
rd.Close()
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
con_bak.Open()
killdb_spid.Connection = con_bak
killdb_spid.CommandText = "[killprocess]" '之前所创建的存储过程
killdb_spid.CommandType = CommandType.StoredProcedure
killdb_spid.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4)
killdb_spid.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
killdb_spid.Parameters.Add("@dbname", SqlDbType.VarChar, 20)

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 backup_Click(ByVal obj As System.Object, ByVal e As System.EventArgs) Handles backup.Click
......
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()
killdb_spid.Parameters(1).Value = Trim(ComboBox1.Text) '需要备份的数据库
killdb_spid.ExecuteNonQuery()
cmd.ExecuteNonQuery()
MsgBox("successfully")
Else
MsgBox("wrongly")
End If

Catch sql As SqlException
MsgBox("sql information:" & sql.Message)
MsgBox("请选择datagrid里的***数据库(正在使用,未能获得数据库的排它访问权)dbname=***的行后,点kill按钮,然后再点backup按钮,这是手动删除进程!")
' Me.kill.Visible = True
' Me.kill.Enabled = True
Catch ex As Exception
MsgBox("system information:" & ex.Message)
'Me.kill.Visible = True
'Me.kill.Enabled = True
Finally
con_bak.Close()
End Try
可省去该按钮--'Private Sub kill_Click(ByVal obj As System.Object, ByVal e As System.EventArgs) Handles kill.Click
'......
'end sub
end class
end module

[此贴子已经被作者于2006-4-5 12:19:59编辑过]