请教数据库还原问题???
我按照论坛上找到的一段数据库还原代码,自己修改了一下,可还是提示数据库正在使用中不能执行操作。。请大家帮忙看看啊!!!
创建存储过程如下:
CREATE PROCEDURE killspid AS
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql + ' kill '+cast(spid as varchar(8))
from master..sysprocesses
where dbid=db_id( 'HMIS ')
GO
系统代码如下:
Dim strsql As String
Dim con As SqlConnection
Dim com As SqlCommand
Dim databasepath As String
Try
Dim conn As SqlConnection = New SqlConnection=( "Server=localhost;Database=master;User ID=sa;Password=;Trusted_Connection=true ")
databasepath = txtRestorePath.Text & "\Backup_HMIS.bak "
con = New SqlConnection( "server=localhost;database=HMIS;uid=sa;pwd=; ")
strsql = "restore database HMIS from disk= ' " & databasepath & " ' "
com = New SqlCommand(strsql, con)
conn.Open()
con.Open()
Dim cmdRT As SqlCommand = New SqlCommand()
cmdRT.CommandType = CommandType.StoredProcedure
cmdRT.Connection = conn
cmdRT.CommandTimeout = 800
cmdRT.CommandText = "killspid "
cmdRT.ExecuteNonQuery()
com.ExecuteNonQuery()
MessageBox.Show( "数据库还原成功! ", "信息提示: ",MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show(ex.Message, "错误提示: ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Finally
con.Close()
End Try
End Sub
[解决办法]
con = New SqlConnection( "server=localhost;database=master;uid=sa;pwd=; ")
[解决办法]
是否可以考虑语句是否正确。
http://topic.csdn.net/t/20061031/17/5122922.html#
[解决办法]
既然改成con = New SqlConnection( "server=localhost;database=master;uid=sa;pwd=; ")
还提示数据库正在使用,那么可能就是你的应用程序其它地方有未关闭的连接。
不妨重新做一个程序来试验这段恢复数据库的代码看是否正确。
[解决办法]
Public Function Restock(ByRef tmpPB As ToolStripProgressBar) As Boolean
Dim tmp_I As Integer
Dim tmp_II As Integer
Dim tmp_Killer As String
Me.tmp_ExeStr = "Select spid From sysprocesses Where dbid = (Select dbid From sysdatabases Where name like ' " & Me._Control & " ') "
Try
Me.DoConnect()
Me.tmp_Flag = True
Me._DataSet.Clear()
Me.tmp_Connection.Open()
tmpPB.Value = 5
Me.tmp_Adapter = New SqlClient.SqlDataAdapter(Me.tmp_ExeStr, Me.tmp_Connection)
Me.tmp_Adapter.Fill(Me._DataSet, "SystemDB ")
tmpPB.Value = 10
tmp_II = Me._DataSet.Tables( "SystemDB ").Rows.Count
For tmp_I = 0 To tmp_II - 1
tmpPB.Value += tmp_II * Fix(85 / tmp_II)
tmp_Killer = "kill " + CType(Me._DataSet.Tables( "SystemDB ").Rows(tmp_I).Item(0), String)
Me.tmp_Command.CommandText = tmp_Killer
Me.tmp_Command.ExecuteNonQuery()
Next
Me.tmp_ExeStr = "Restore database " & Me._Control & " from disk= ' " & Me._Path & " ' with Recovery "
Me.tmp_Command.CommandText = Me.tmp_ExeStr
Me.tmp_Command.ExecuteNonQuery()
tmpPB.Value = 90
Me.tmp_ExeStr = "Alter database " & Me._Control & " set online "
Me.tmp_Command.CommandText = Me.tmp_ExeStr
Me.tmp_Command.ExecuteNonQuery()
tmpPB.Value = 100
Catch
Me.tmp_Flag = False
Finally
Me._Name = Me._Control
Me.DoConnect()
End Try
Return Me.tmp_Flag
End Function
[解决办法]
还有个办法: 清除连接池,再建个到服务器的连接后就能还原了
Public Class frmBackup
Protected Const SQL_CONNECTION_STRING As String = _
"Data Source=.\SQLEXPRESS; " & _
"Integrated Security=True; " & _
"Connect Timeout=30;User Instance=True; "
Private cnStr As String = SQL_CONNECTION_STRING
Dim cn As SqlConnection
Dim cm As SqlCommand
Private Sub btnRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRestore.Click
cm = New SqlCommand( "restore database [E:\计算机wq的作业\MyProject\MyDB\MyDB.mdf] " & _
"from disk = 'E:\计算机wq的作业\备份\MyDB.bak ' ", cn)
Try
SqlConnection.ClearAllPools() //就是这一举句
cn.Open()
cm.ExecuteNonQuery()
cn.Close()
'MsgBox( "数据库还原成功! ", MsgBoxStyle.Information, "还原 ")