![](https://iknow-base.cdn.bcebos.com/lxb/notice.png)
VB如何用对数据库还原
我已经做好了备份,但是还原时直接用RESTORE语句不能还原,数据库在单机上,不需要复杂的连接,请教一下有什么方法,网上找的两个版本(邹建版和网易ACCESS版)都不好用...
我已经做好了备份,但是还原时直接用RESTORE语句不能还原,数据库在单机上,不需要复杂的连接,请教一下有什么方法,网上找的两个版本(邹建版和网易ACCESS版)都不好用。
展开
展开全部
利用master数据库来还原,不要连接需要还原的数据库来还原
Private Sub mnuDBResume_Click() '-------------------还原数据库
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
If Dir(App.Path & "\bak1.bak") <> vbNullString Then
If cnMain.State <> 0 Then cnMain.Close
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & strSQLUser & ";Password=" & strSQLPW & ";Initial Catalog=master;Data Source=" & strSQLServer '借助master数据库来恢复strSQLUser是SQL登录名,Password是密码,strSQLServer是服务器名
cn.CursorLocation = adUseClient
cn.Open
rs.Open "select spid from sysprocesses where dbid=db_id('SuperMarketdb')", cn
Do While Not rs.EOF
cn.Execute "kill " & rs("spid") '杀掉sql服务器的所有连接,否则会出现:数据库正在使用,无法完成排它操作等等,很重要
rs.MoveNext
Loop
cn.Execute ("restore database SuperMarketdb from disk='" & App.Path & "\bak1.bak" & "' with replace")
cn.Close
cnMain.Open "Provider=SQLOLEDB.1;User ID=" & strSQLUser & ";Password=" & strSQLPW & ";Initial Catalog=master;Data Source=" & strSQLServer
MsgBox "数据库恢复成功!", vbInformation
Else
MsgBox "请先备份数据库!", vbInformation, "提示:"
End If
Set cn = Nothing
End Sub
Private Sub mnuDBResume_Click() '-------------------还原数据库
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
If Dir(App.Path & "\bak1.bak") <> vbNullString Then
If cnMain.State <> 0 Then cnMain.Close
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & strSQLUser & ";Password=" & strSQLPW & ";Initial Catalog=master;Data Source=" & strSQLServer '借助master数据库来恢复strSQLUser是SQL登录名,Password是密码,strSQLServer是服务器名
cn.CursorLocation = adUseClient
cn.Open
rs.Open "select spid from sysprocesses where dbid=db_id('SuperMarketdb')", cn
Do While Not rs.EOF
cn.Execute "kill " & rs("spid") '杀掉sql服务器的所有连接,否则会出现:数据库正在使用,无法完成排它操作等等,很重要
rs.MoveNext
Loop
cn.Execute ("restore database SuperMarketdb from disk='" & App.Path & "\bak1.bak" & "' with replace")
cn.Close
cnMain.Open "Provider=SQLOLEDB.1;User ID=" & strSQLUser & ";Password=" & strSQLPW & ";Initial Catalog=master;Data Source=" & strSQLServer
MsgBox "数据库恢复成功!", vbInformation
Else
MsgBox "请先备份数据库!", vbInformation, "提示:"
End If
Set cn = Nothing
End Sub
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询