Dim ret As Integer
If Text1.Text = "" Then
MsgBox "请选择要恢复的数据文件!", vbInformation, "提示"
Exit Sub
Else
ret = MsgBox("数据恢复操作将会覆盖以前的所有数据并且覆盖后无法恢复,您确定要进行恢复操作吗?", vbQuestion + vbOKCancel + "提示")
If ret = vbOK Then
Me.MousePointer = 11
Dim cn As New ADODB.Connection
Dim s_restore As String
'Debug.Print gs_conn_string
'此时需要连接master数据库才能完成数据恢复操作
cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=7;Initial Catalog=master;Data Source=127.0.0.1 "
'同上wzgl为需要恢复的数据库
s_restore = "restore database student1 from disk='" + Trim(Text1.Text) + "'"
'text1一个用于记录需要恢复文件的地址的textbox
cn.Execute s_restore
cn.BeginTrans
If Err.Number = 0 Then
cn.CommitTrans
MsgBox "数据恢复成功!", vbInformation, "提示"
Else
cn.RollbackTrans
MsgBox "数据恢复失败!", vbCritical, "提示"
End If
cn.Close
Set cn = Nothing
Me.MousePointer = 1
Else
Exit Sub
End If
End If在没有其它程序连接SQL SERVER 的时候它能实现恢复备份的功能但是我想在SQL SERVER被连接状态恢复数据库!提示“数据库在使用,为获得数据库的排它访问权”实时错误!请问各位老大怎么解决呢!急!!!!!!!!!!!!!!
If Text1.Text = "" Then
MsgBox "请选择要恢复的数据文件!", vbInformation, "提示"
Exit Sub
Else
ret = MsgBox("数据恢复操作将会覆盖以前的所有数据并且覆盖后无法恢复,您确定要进行恢复操作吗?", vbQuestion + vbOKCancel + "提示")
If ret = vbOK Then
Me.MousePointer = 11
Dim cn As New ADODB.Connection
Dim s_restore As String
'Debug.Print gs_conn_string
'此时需要连接master数据库才能完成数据恢复操作
cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=7;Initial Catalog=master;Data Source=127.0.0.1 "
'同上wzgl为需要恢复的数据库
s_restore = "restore database student1 from disk='" + Trim(Text1.Text) + "'"
'text1一个用于记录需要恢复文件的地址的textbox
cn.Execute s_restore
cn.BeginTrans
If Err.Number = 0 Then
cn.CommitTrans
MsgBox "数据恢复成功!", vbInformation, "提示"
Else
cn.RollbackTrans
MsgBox "数据恢复失败!", vbCritical, "提示"
End If
cn.Close
Set cn = Nothing
Me.MousePointer = 1
Else
Exit Sub
End If
End If在没有其它程序连接SQL SERVER 的时候它能实现恢复备份的功能但是我想在SQL SERVER被连接状态恢复数据库!提示“数据库在使用,为获得数据库的排它访问权”实时错误!请问各位老大怎么解决呢!急!!!!!!!!!!!!!!
AdoCon.DefaultDatabase = "master" '将当前默认数据库改为master
AdoCon.Execute "set noexec off set parseonly off "
AdoCon.Execute " restory database cargps to disk= 'e:\backup\xxx'"
AdoCon.Execute "user xxx"
MsgBox "请选择要恢复的数据文件!", vbInformation, "提示"
Exit Sub
Else
ret = MsgBox("数据恢复操作将会覆盖以前的所有数据并且覆盖后无法恢复,您确定要进行恢复操作吗?", vbQuestion + vbOKCancel, "提示")
If ret = vbOK Then
Label1.Visible = True
Command1.Enabled = False
Me.MousePointer = 11
Shell "scm -silent 1 -action 2 -service mssqlserver " 'Specfied sqlserver service
Sleep 100
Dim cn As New ADODB.Connection
Dim s_restore As String
'Debug.Print gs_conn_string
'此时需要连接master数据库才能完成数据恢复操作
cn.Open "Provider=SQLOLEDB.1;server=123;Persist Security Info=False;Initial Catalog=master;Data Source=127.0.0.1;user id=sa;password=7"
'同上student1为需要恢复的数据库
s_restore = "restore database student1 from disk='" + Trim(Text1.Text) + "'"
'text1一个用于记录需要恢复文件的地址的textbox
cn.Execute s_restore
cn.BeginTrans
If Err.Number = 0 Then
cn.CommitTrans
MsgBox "数据恢复成功!", vbInformation, "提示"
Command1.Enabled = True
Label1.Visible = False
Else
cn.RollbackTrans
MsgBox "数据恢复失败!", vbCritical, "提示"
Command1.Enabled = True
End If
cn.Close
Set cn = Nothing
Me.MousePointer = 1
Else
Exit Sub
End If
On Error Resume Next
Dim DBC As New DataBaseConnection
If db.State = 1 Then
db.Close
End If
db.ConnectionString = DBC.SqlConnectString(d1, d2, d3)
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
db.CursorLocation = adUseClient
db.Open
Set cmd.ActiveConnection = db
If Err.Number Then
MsgBox Err.Description, 16 + vbOKOnly, Err.Number
Exit Sub
End If
db.DefaultDatabase = "student1"
If Err.Number Then
MsgBox Err.Description, 16 + vbOKOnly, Err.Number
Exit Sub
End If
End If
我是这样做的!cn.Open "Provider=SQLOLEDB.1;server='" & x & "';Persist Security Info=False;Initial Catalog=master;Data Source=127.0.0.1;user id=sa;password=7"在执行到这条语句之前已经有值了!为什么不能运行呢!难道server=....这句只能指定值不能是变量值吗!还望各位帮帮忙!谢谢!
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = sServerName
cn.Properties("Initial Catalog").Value = sDatabaseName
cn.Properties("User ID").Value = sUserName
cn.Properties("Password").Value = sPassword cn.Open
你可以把这些信息写在ini文件中,然后,读取这些信息
Public Function GetProfile(strFileName As String, strSection As String, strName As String) As String
'这个函数是用来对INI文件进行读操作的
'函数说明:
'strFileName 是所要读取的文件名
'strSection 是这个文件中的一个节点名
'strName 是所要查找的字段名
'返回值:
strSectionTemp = ""
strNameTemp = ""
strreturn = ""
On Error GoTo ErrSrchSection
Open strFileName For Input As #1
' 下面这段程序是用来查找节点的
Do While Not EOF(1)
strCharA = Input(1, #1)
If strCharA = "[" Then
Do While Not EOF(1)
strCharB = Input(1, #1)
If strCharB = "]" Then Exit Do
strSectionTemp = strSectionTemp & strCharB
Loop
End If
If strSectionTemp = strSection Then
strCharA = Input(2, #1)
Exit Do
Else
strSectionTemp = ""
End If
Loop
On Error GoTo ErrReadFile
aa:
'下面这段程序是用来查找所要查找的字段的
strNameTemp = ""
Do While Not EOF(1)
strCharA = Input(1, #1)
If strCharA <> "=" Then
strNameTemp = strNameTemp & strCharA '得到名称
Else
Exit Do
End If
Loop
If strNameTemp = strName Then
Line Input #1, strreturn '如果找到与它匹配的字段名,就返回得到的值
Else
Line Input #1, strreturn '如果未找到与它匹配的字段名,就继续找
GoTo aa
End If
Close #1
GetProfile = strreturn
Exit Function
ErrReadFile:
Dim inrRet As Integer
intret = MsgBox("在文件中没有找到所要查找的字段", vbAbortRetryIgnore, "错误信息")
Select Case intret
Case vbAbort
GetProfile = ""
Close #1
Exit Function
Case vbRetry
Resume
Case vbIgnore
Resume Next
End Select
ErrSrchSection:
MsgBox "节点未找到", vbOKOnly
GetProfile = ""
Close #1
End Function
create proc killspid (@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
该存储过程,用来结束当前数据库进程。很好用。
在还原数据库前,先kill当前的数据库进程,就可以还原了。