备份: '引用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
'引用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
打开“企业管理器”,右键盘点你的数据库,点“所有任务”里的“维护计划”,按照向导来做就成了,可以定时备份,删除早于某时间的数据。