Back up a SQL Database Using SQL/DMO

Category:

Database

Type:

Snippets

Difficulty:

Intermediate

Author:

Sanjaya Warakagoda

Version Compatibility: Visual Basic 6

More information:

See comments within code for usage/explanation. Requires a reference to SQL/DMO object library.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
'* * * * * * * * * * * * * * 'Passing Values '* * * * * * * * * * * * * * '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.SQLServer

Code:

Public 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