备份:
'引用ADO
Private Sub Command1_Click()
Dim backUpFileName As String
With CommonDialog1
.DialogTitle = "保存的文件名"
.Filter = "*.SQL|*.SQL"
.InitDir = App.Path
.ShowSave
backUpFileName = CommonDialog1.FileName
End With
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=登录名;Password=密码;Initial Catalog=数据库;Data Source=Sql服务器别名"
cn.CursorLocation = adUseClient
cn.Open
cn.Execute ("backup database 数据库 to disk='" & backupdilename & "'")
End Sub
恢复:
Private Sub Command2_Click()
Dim backUpFileName As String
With CommonDialog1
.DialogTitle = "保存的文件名"
.Filter = "*.SQL|*.SQL"
.InitDir = App.Path
.ShowSave
If Dir(CommonDialog1.FileName) <> "" Then
backUpFileName = CommonDialog1.FileName
Else
MsgBox "文件不存在!"
Exit Sub
End If
End With
Dim cn As ADODB.Connection, rs As New ADODB.Recordset
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=登录名;Password=密码;Initial Catalog=master;Data Source=Sql服务器别名" '借助master数据库来恢复
cn.CursorLocation = adUseClient
cn.Open
rs.Open "select spid from sysprocesses where dbid=db_id('你的数据库名)", cn
Do While Not rs.EOF
cn.Execute "kill " & rs("spid") '杀掉sql服务器的所有连接,否则会出现:数据库正在使用,无法完成排它操作等等,很重要
rs.MoveNext
Loop
cn.Execute ("restore database 数据库名 from disk='" & backUpFileName & "' with replace")
End Sub