代码还原数据库出错怎么办?
大家好,我用VB.ENT来备份并还原数据时,如果该窗体作为单独运行时还原会成功,可是如果该窗体作为别的窗体的子窗口时还原就会出错------------数据库正在使用,未能获得数据库的排它访问权,所以还原失败.
这样应该怎样解决才好呢?
哦,好的,太感谢你了.
我的是这样的(交流一下),只是在还原数据库时会出错
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
