如何在VB中恢复SQL数据库?满分相送!

[baoxue10181018]

我在VB中用Restroe Database 数据库名 From Disk=路径名 恢复数据库
提示数据库正在使用,排它访问,我希望在恢复数据库前先关闭指定的数据库然后再执行恢复语句,可我不知道在VB中如何编程实现关闭数据库或有别的办法,但我希望这些操作在VB中完成,请各位指点!谢谢

[pengdali]

use master
Restroe Database 数据库名 From Disk=路径名

[txlicenhe]

1:
use master
go
restore database ...
2:
用如下存储过程 来关闭

use master
go
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500),@temp varchar(1000)
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 =0
begin
set @temp='kill '+rtrim(@spid)
exec(@temp)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end

--用法
use master
exec killspid '数据库名'

[pengdali]

如果还不行：

use master
go
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500),@temp varchar(1000)
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
set @temp='kill '+rtrim(@spid)
exec(@temp)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end

--用法
use master
exec killspid '数据库名'

[baoxue10181018]

请问这些存储过程可以在VB的SQL语句里面写吗

[zjcxc]

'VB中的SQL数据库恢复函数,具有关闭用户进程的能力.


'恢复数据库,返回出错信息,正常恢复,返回""
'调用:frestoredatabase_a "备份文件名","数据库名"

'sDataBasePath 恢复后的数据库存放目录
'sBackupNumber 是从那个备份号恢复
'sReplaceExist 指定是否覆盖已经存在的数据
Public Function fRestoreDatabase_a(ByVal sBackUpfileName$ _
, ByVal sDataBaseName$ _
, Optional ByVal sDataBasePath$ = "" _
, Optional ByVal sBackupNumber& = 1 _
, Optional ByVal sReplaceExist As Boolean = False _
) As String

Dim iDb As ADODB.Connection, iRe As ADODB.Recordset
Dim iConcStr$, iSql$, iReturn$, iI&

On Error GoTo lbErr

'创建对象
Set iDb = New ADODB.Connection
Set iRe = New ADODB.Recordset

'连接数据库服务器
iConcStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=zj"
iDb.Open iConcStr

'得到还原后的数据库存放目录,如果没有指定,存放到SQL SERVER的DATA目录
If sDataBasePath = "" Then
iSql = "select filename from master..sysfiles"
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
iSql = iRe(0)
iRe.Close
sDataBasePath = Left(iSql, InStrRev(iSql, "\"))
End If

'检查数据库是否存在
If sReplaceExist = False Then
iSql = "select 1 from master..sysdatabases where name='" & sDataBaseName & "'"
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
If iRe.EOF = False Then
iReturn = "数据库已经存在!"
iRe.Close
GoTo lbExit
End If
iRe.Close
End If

'关闭用户进程,防止其它用户正在使用数据库,导致数据恢复失败
iSql = "select spid from master..sysprocesses where dbid=db_id('" & sDataBaseName & "')"
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
While iRe.EOF = False
iSql = "kill " & iRe(0)
iDb.Execute iSql
iRe.MoveNext
Wend
iRe.Close

'获取数据库恢复信息
iSql = "restore filelistonly from disk='" & sBackUpfileName & "'" & vbCrLf & _
"with file=" & sBackupNumber
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly

'生成数据库恢复语句
iSql = "restore database [" & sDataBaseName & "]" & vbCrLf & _
"from disk='" & sBackUpfileName & "'" & vbCrLf & _
"with file=" & sBackupNumber & vbCrLf
With iRe
While Not .EOF
iReturn = iRe("PhysicalName")
iI = InStrRev(iReturn, ".")
iReturn = IIf(iI = 0, "", Mid(iReturn, iI)) & "'"
iSql = iSql & ",move '" & iRe("LogicalName") & _
"' to '" & sDataBasePath & sDataBaseName & iReturn & vbCrLf
.MoveNext
Wend
.Close
End With
iSql = iSql & IIf(sReplaceExist, ",replace", "")

iDb.Execute iSql
iReturn = ""
GoTo lbExit

lbErr:
iReturn = Error
lbExit:
fRestoreDatabase_a = iReturn
End Function

[aierong]

代码可以写在vb里面
用ado的command对象调用即可

[j2l2000]

learn