下面的代码能够实现备份,其中Combo1.Text为数据库名
On Error GoTo OYKBak
Dim Bak As ADODB.Recordset
If (Trim$(Combo1.Text) = "" ) Then
MsgBox "没有选择合法数据库。", , "警告信息"
Else
Dialog1.FileName = ""
Dim Path As String
Dialog1.Action = 2
Path = Dialog1.FileName
If Path <> "" Then
Set MDbConn = New ADODB.Connection
MDbConn.Open "Provider=SQLOLEDB.1;Password='" & MPassWD & "';Persist Security Info=False;User ID=sa;Initial Catalog=master;Data Source='" & MServer & "'"
MDbConn.CursorLocation = adUseClient
s = "use [master]"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "EXECUTE master.dbo.xp_get_tape_devices"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "set noexec off set parseonly off"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLDataRoot'"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "select name, phyname, cntrltype, status, convert(int, substring(convert(binary(4), 0), 1, 1)), floor(8192.0 / 1048576) from master..sysdevices o where 1 = 1 and (status & 0x10) <> 0 order by o.name"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "xp_fileexist '" & Path & "'"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "set noexec off set parseonly off"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "BACKUP DATABASE [" & Trim$(Combo1.Text) & "] TO DISK = N'" & Path & "' WITH NOINIT , NOUNLOAD , NAME = N'" & Trim$(Combo1.Text) & " backup', SKIP , STATS = 10, NOFORMAT"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
Set Bak = Nothing
MsgBox "数据库备份成功", , "OK"
End If
On Error GoTo OYKBak
Dim Bak As ADODB.Recordset
If (Trim$(Combo1.Text) = "" ) Then
MsgBox "没有选择合法数据库。", , "警告信息"
Else
Dialog1.FileName = ""
Dim Path As String
Dialog1.Action = 2
Path = Dialog1.FileName
If Path <> "" Then
Set MDbConn = New ADODB.Connection
MDbConn.Open "Provider=SQLOLEDB.1;Password='" & MPassWD & "';Persist Security Info=False;User ID=sa;Initial Catalog=master;Data Source='" & MServer & "'"
MDbConn.CursorLocation = adUseClient
s = "use [master]"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "EXECUTE master.dbo.xp_get_tape_devices"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "set noexec off set parseonly off"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLDataRoot'"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "select name, phyname, cntrltype, status, convert(int, substring(convert(binary(4), 0), 1, 1)), floor(8192.0 / 1048576) from master..sysdevices o where 1 = 1 and (status & 0x10) <> 0 order by o.name"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "xp_fileexist '" & Path & "'"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "set noexec off set parseonly off"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "BACKUP DATABASE [" & Trim$(Combo1.Text) & "] TO DISK = N'" & Path & "' WITH NOINIT , NOUNLOAD , NAME = N'" & Trim$(Combo1.Text) & " backup', SKIP , STATS = 10, NOFORMAT"
Set Bak = New ADODB.Recordset
With Bak
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
Set Bak = Nothing
MsgBox "数据库备份成功", , "OK"
End If
'注意,必须要数据库管理员身份才行,比如'sa''备份数据库
Public Function BackupDataBase(ByVal strBackupDBPath As String) As Boolean
'strBackupDBPath 为备份文件的路径以及名称
'Cn 为全局数据库连接
Dim sql As String
On Error GoTo errDeal
BackupDataBase = False
sql = "USE master" & vbCrLf
Cn.Execute sql
sql = " EXEC sp_addumpdevice 'disk', 'CurMigrantBak', '" & strBackupDBPath & "'" & vbCrLf
' CurMigrantBak 为备份文件的设备名称,不需要关心,因为最后该设备是删除掉的,作为一个临时文件而已,名字自己随便取
Cn.Execute sql
sql = " BACKUP DATABASE CurMigrant TO CurMigrantBak with INIT,FORMAT" & vbCrLf
' With 后面的参数可以参考SQL SERVER 说明,采用该默认值应该没问题的
Cn.Execute sql
sql = " EXEC sp_dropdevice 'CurMigrantBak'"
Cn.Execute sql
BackupDataBase = True
Exit Function
errDeal:
If Err.Number = -2147217900 Then
Resume Next
Else
MsgBox Err.Number & vbCrLf & Err.Description
End If
BackupDataBase = False
MsgBox "备份数据库失败!", vbOKOnly + vbInformation, "提示"
End Function
感动的要哭了!