Wednesday, July 8, 2009

Back up SQL Database Using SQLDMO

Possible you require code to back up your database SQL. We will try to give functioning code to back up your database by using SQLDMO. If you not yet owned SQLDMO, please download beforehand SQLDMO library by using search engine.

Make a module and give name "modbackupsqldatabase" or up to you. Afterwards, including code hereunder to module.

Public Function DBBackup(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
Dim oBackup As SQLDMO.Backup
On Error GoTo ErrorHandler
Set oBackup = CreateObject("SQLDMO.Backup")
If ConnectSQLDB(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:
DBBackup = False
End Function

Private Function ConnectSQLDB(ByVal nServer_Name As String, _
ByVal nDB_Login As String, _
ByVal nDB_Password As String) As Boolean
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
On Error GoTo ErrorHandler
Connect_SQLDB = False
oSQLServer.Connect nServer_Name, nDB_Login, nDB_Password
ConnectSQLDB = True
Exit Function
ErrorHandler:
oSQLServer.DisConnect
ConnectSQLDB = False
End Function


To its use as follows:

If DBBackup(yourservername, _
yourdbname, _
yourloginID, yourdbpassword, _
yourbackupdevicename, yourbackupsetname, _
yourbackupdescription) = True Then
MsgBox "Succes to back up database."
Else
MsgBox "Cant to back up database!"
End if


No comments:

Post a Comment