'nServer_Name = SQL server name 'nDB_Name = Database name 'nDB_Login = Login name 'nDB_Password = Password 'nBack_Dev =Backup device name 'nBack_Set = Backup set name 'nBack_Desc = Backup discription'Backup device name has to be specified by the SQL ADMIN. 'Which comes under SQL Backup. The name you specified must 'be same as Passing value of backup device name.'SQL ADMIN can only specify the device type(Tape, HD,...).'* * * * * * * * * * * * * *'* * * * * * * * * * * * * *Option Explicit Private oSQLServer As SQLDMO.SQLServerPublic Function DB_Backup(ByVal nServer_Name As String, _ ByVal nDB_Name As String, _ ByVal nDB_Login As String, ByVal nDB_Password As String, _ ByVal nBack_Dev As String, ByVal nBack_Set As String, _ ByVal nBack_Desc As String) As Boolean' nServer_Name = SQL server name ' nDB_Name = Database name ' nDB_Login = Login name ' nDB_Password = Password ' nBack_Dev =Backup device name ' nBack_Set = Backup set name ' nBack_Desc = Backup discription Dim oBackup As SQLDMO.Backup On Error GoTo ErrorHandler Set oBackup = CreateObject("SQLDMO.Backup") If Connect_SQLDB(nServer_Name, nDB_Login, nDB_Password) Then oBackup.Devices = "[" & nBack_Dev & "]" oBackup.Database = nDB_Name oBackup.BackupSetName = nBack_Set oBackup.BackupSetDescription = nBack_Desc oBackup.SQLBackup oSQLServer oSQLServer.DisConnect DB_Backup = True End If
Exit Function ErrorHandler: DB_Backup = False End Function Private Function Connect_SQLDB(ByVal nServer_Name As String, _ ByVal nDB_Login As String, _ ByVal nDB_Password As String) As Boolean
' nServer_Name = SQL server name ' nDB_Login = Login name ' nDB_Password = Password Set oSQLServer = CreateObject("SQLDMO.SQLServer") On Error GoTo ErrorHandler Connect_SQLDB = False oSQLServer.Connect nServer_Name, nDB_Login, nDB_Password Connect_SQLDB = True Exit Function ErrorHandler: oSQLServer.DisConnect Connect_SQLDB = False End Function必须安装SQL客户端
这个问题对我也很重要,但是一定要安装SQL客户端吗?
以下程序是一个备份程序段,不一定就要装SQL客户端的。Dim cnn As New ADODB.Connection Dim mytb As New ADODB.Recordset Dim query As StringPrivate Sub cmdback_Click() If bcnn = True Then '判断连接 Label1.Caption = "正在备份" Label1.ForeColor = vbYellow query = "" bfile = "" bfile = App.Path & "\b" & dataname & Format(Date, "yyyymmdd") query = "backup database " & dataname query = query & " to disk='" & bfile & ".bak" & "'"
If ball = False Then '差异备份
query = query & " with differential "
End If cnn.Execute query Label1.Caption = "成功备份完毕!" query = "" query = Date & " " & Time & " " & " 不能连接服务器,不能进行备份!" Label1.Caption = " 不能连接服务器,不能进行备份!" Label1.ForeColor = vbRed Open App.Path & "\back.txt" For Append As #1 Write #1, query Close #1 Exit Sub
Else Label1.Caption = "退出备份" Exit Sub End If err: Label1.Caption = "备份出错" End Sub
也mail给我 [email protected]
[email protected]
'nDB_Name = Database name
'nDB_Login = Login name
'nDB_Password = Password
'nBack_Dev =Backup device name
'nBack_Set = Backup set name
'nBack_Desc = Backup discription'Backup device name has to be specified by the SQL ADMIN.
'Which comes under SQL Backup. The name you specified must
'be same as Passing value of backup device name.'SQL ADMIN can only specify the device type(Tape, HD,...).'* * * * * * * * * * * * * *'* * * * * * * * * * * * * *Option Explicit
Private oSQLServer As SQLDMO.SQLServerPublic Function DB_Backup(ByVal nServer_Name As String, _
ByVal nDB_Name As String, _
ByVal nDB_Login As String, ByVal nDB_Password As String, _
ByVal nBack_Dev As String, ByVal nBack_Set As String, _
ByVal nBack_Desc As String) As Boolean' nServer_Name = SQL server name
' nDB_Name = Database name
' nDB_Login = Login name
' nDB_Password = Password
' nBack_Dev =Backup device name
' nBack_Set = Backup set name
' nBack_Desc = Backup discription
Dim oBackup As SQLDMO.Backup
On Error GoTo ErrorHandler
Set oBackup = CreateObject("SQLDMO.Backup")
If Connect_SQLDB(nServer_Name, nDB_Login, nDB_Password) Then
oBackup.Devices = "[" & nBack_Dev & "]"
oBackup.Database = nDB_Name
oBackup.BackupSetName = nBack_Set
oBackup.BackupSetDescription = nBack_Desc
oBackup.SQLBackup oSQLServer
oSQLServer.DisConnect
DB_Backup = True
End If
Exit Function
ErrorHandler:
DB_Backup = False
End Function
Private Function Connect_SQLDB(ByVal nServer_Name As String, _
ByVal nDB_Login As String, _
ByVal nDB_Password As String) As Boolean
' nServer_Name = SQL server name
' nDB_Login = Login name
' nDB_Password = Password Set oSQLServer = CreateObject("SQLDMO.SQLServer")
On Error GoTo ErrorHandler
Connect_SQLDB = False
oSQLServer.Connect nServer_Name, nDB_Login, nDB_Password
Connect_SQLDB = True
Exit Function
ErrorHandler:
oSQLServer.DisConnect
Connect_SQLDB = False
End Function必须安装SQL客户端
Dim mytb As New ADODB.Recordset
Dim query As StringPrivate Sub cmdback_Click()
If bcnn = True Then '判断连接
Label1.Caption = "正在备份"
Label1.ForeColor = vbYellow
query = ""
bfile = ""
bfile = App.Path & "\b" & dataname & Format(Date, "yyyymmdd") query = "backup database " & dataname
query = query & " to disk='" & bfile & ".bak" & "'"
If ball = False Then '差异备份
query = query & " with differential "
End If
cnn.Execute query
Label1.Caption = "成功备份完毕!"
query = ""
query = Date & " " & Time & " " & " 不能连接服务器,不能进行备份!"
Label1.Caption = " 不能连接服务器,不能进行备份!"
Label1.ForeColor = vbRed
Open App.Path & "\back.txt" For Append As #1
Write #1, query
Close #1
Exit Sub
Else
Label1.Caption = "退出备份"
Exit Sub
End If
err:
Label1.Caption = "备份出错"
End Sub
[email protected]