首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > .NET > VB Dotnet >

请问数据库还原有关问题?

2012-01-07 
请教数据库还原问题???我按照论坛上找到的一段数据库还原代码,自己修改了一下,可还是提示数据库正在使用中

请教数据库还原问题???
我按照论坛上找到的一段数据库还原代码,自己修改了一下,可还是提示数据库正在使用中不能执行操作。。请大家帮忙看看啊!!!

创建存储过程如下:
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, "还原 ")

热点排行