备份: '引用ADO Private Sub Command1_Click() 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='c:\test'") End Sub
恢复: Private Sub Command2_Click() 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='c:\test' with replace") End Sub
只需要引用ADO(Microsoft ActiveX Data Objects 2.X Library),窗体放一个Command1和一个Command2就行了,把上面的代码改一下,比如数据库的连接,和要备份、恢复的数据库名
Dim conConnection As New ADODB.Connection Dim strDataExport As String
On Error GoTo funBackupDatabase_Error frmMaintenance.MousePointer = 11 '设置鼠标指针形状 strDataExport = "backup database XinZi to disk='" + strPath + "'"
conConnection.Close Set conConnection = Nothing frmMaintenance.MousePointer = 1
Call funOpenDatabase
funRestoreDataBase = 0 这是数据为还原的,需要的条件同上
Public Cn As ADODB.Connection Set Cn = New ADODB.Connection strcon = "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=jtsg;Data Source=" + ServerIp + ";" + _ "use procedure for prepare=1;auto translate=true;" + _ "use encryption for data=false;tag with column collation when possible=false" Cn.ConnectionString = strcon On Error Resume Next Cn.Open If Cn.Errors.Count > 0 Then For i = 0 To Cn.Errors.Count - 1 If Cn.Errors(i).Number = -2147467259 Then MsgBox "网络不通或服务器未开,请确认后再试", vbOKOnly + vbInformation, "提醒" Unload Me Exit Sub Exit For End If Next i End If完全备份 Private Sub BackupAll() Cn.Execute "backup database jtsg to disk='" + App.Path + "\backup\myjtsgall.bak' with name='jtsg backup all',description='Full Backup Of jtsg'" MsgBox "数据完全备份已经完成", vbOKOnly + vbInformation, "提醒" End Sub差异备份 Private Sub BackupDif() Cn.Execute "backup database jtsg to disk='" + App.Path + "\backup\myjtsgdif.bak' with differential ,noinit,name='jtsg backup dif',description='Differential Backup Of jtsg'" MsgBox "数据差异备份已经完成", vbOKOnly + vbInformation, "提醒" End Sub数据恢复 Private Sub RestoreData() Cn.Execute "restore database jtsg from disk='" + App.Path + "\backup\myjtsgall.bak' with FILE=1" MsgBox "数据恢复已经完成", vbOKOnly + vbInformation, "提醒" End Sub
'引用ADO
Private Sub Command1_Click()
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='c:\test'")
End Sub
Private Sub Command2_Click()
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='c:\test' with replace")
End Sub
On Error GoTo funBackupDatabase_Error frmMaintenance.MousePointer = 11 '设置鼠标指针形状 strDataExport = "backup database XinZi to disk='" + strPath + "'"
conConnection.Open "Provider=SQLOLEDB.1;Password=" & g_SQLDatabase.strPassWord & _
";Persist Security Info=True;User ID=" & g_SQLDatabase.strLoginName & _
";Initial Catalog=" & g_SQLDatabase.strDataBaseName & _
";Data Source=" & g_SQLDatabase.strSource
'这里不需要连接master数据库,即可完成备份
conConnection.BeginTrans
conConnection.Execute strDataExport
conConnection.CommitTrans
conConnection.Close
Set conConnection = Nothing
frmMaintenance.MousePointer = 1
funBackupDatabase = 0
这是数据备份用的,只需要给它相应的文件路径就可以
Dim sn As New ADODB.Recordset
Dim s_restore As String
On Error GoTo funRestoreDataBase_Error
frmMaintenance.MousePointer = 11
conConnection.Open "Provider=SQLOLEDB.1;Password=" & g_SQLDatabase.strPassWord & _
";Persist Security Info=True;User ID=" & g_SQLDatabase.strLoginName & _
";Initial Catalog=master" & _
";Data Source=" & g_SQLDatabase.strSource
sn.Open "select spid from sysprocesses where dbid=db_id('XinZi')", conConnection
Do While Not sn.EOF
DoEvents
conConnection.Execute "kill " & sn("spid")
sn.MoveNext
Loop
sn.Close
s_restore = "restore database XinZi from disk='" + strPath + "' with REPLACE"
conConnection.Execute s_restore
' Debug.Print gs_conn_string
' 此时需要连接master数据库才能完成数据恢复操作
' 同上XinZi为需要恢复的数据库
' s_restore = "restore database XinZi from disk='" + strPath + "'"
' 'text1一个用于记录需要恢复文件的地址的textbox
' conConnection.Execute s_restore
conConnection.BeginTrans
conConnection.CommitTrans
conConnection.Close
Set conConnection = Nothing
frmMaintenance.MousePointer = 1
Call funOpenDatabase
funRestoreDataBase = 0
这是数据为还原的,需要的条件同上
Set Cn = New ADODB.Connection
strcon = "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=jtsg;Data Source=" + ServerIp + ";" + _
"use procedure for prepare=1;auto translate=true;" + _
"use encryption for data=false;tag with column collation when possible=false"
Cn.ConnectionString = strcon
On Error Resume Next
Cn.Open
If Cn.Errors.Count > 0 Then
For i = 0 To Cn.Errors.Count - 1
If Cn.Errors(i).Number = -2147467259 Then
MsgBox "网络不通或服务器未开,请确认后再试", vbOKOnly + vbInformation, "提醒"
Unload Me
Exit Sub
Exit For
End If
Next i
End If完全备份
Private Sub BackupAll()
Cn.Execute "backup database jtsg to disk='" + App.Path + "\backup\myjtsgall.bak' with name='jtsg backup all',description='Full Backup Of jtsg'"
MsgBox "数据完全备份已经完成", vbOKOnly + vbInformation, "提醒"
End Sub差异备份
Private Sub BackupDif()
Cn.Execute "backup database jtsg to disk='" + App.Path + "\backup\myjtsgdif.bak' with differential ,noinit,name='jtsg backup dif',description='Differential Backup Of jtsg'"
MsgBox "数据差异备份已经完成", vbOKOnly + vbInformation, "提醒"
End Sub数据恢复
Private Sub RestoreData()
Cn.Execute "restore database jtsg from disk='" + App.Path + "\backup\myjtsgall.bak' with FILE=1"
MsgBox "数据恢复已经完成", vbOKOnly + vbInformation, "提醒"
End Sub