'**************************************************************
' Bill Wunder
' Backup a Databases
' Used in one step package with 4 Global Vars listed below
' backup format of "sls" for SQL Lite Speed "bak" for SQL Backup
' fail if not one or the other
'**************************************************************

Option Explicit

Function Main()

  Dim sBackupFolder, sSourceSQLServerName, sDatabaseName, sBackupFormat
  Dim oSourceSQLServer, oDB, oBackup
  Dim sFileName, sCommand
    
  ' Global Variables that must be provided
  sBackupFolder = DTSGlobalVariables("Backup Folder")
  sSourceSQLServerName = DTSGlobalVariables("Source SQL Server")
  sDatabaseName = DTSGlobalVariables("Database Name")
  sBackupFormat = DTSGlobalVariables("Backup Format") 
  
  If NOT(LCase(sBackupFormat) = "bak") and NOT(LCase(sBackupFormat) = "sls") Then
    Err.Raise 1, "BackupDatabase", "Invalid Backup Format specified - must be sls (SQLLiteSpeed) or bak (native SQL backup)"
  End If 		

  If sDatabaseName <> "tempdb" Then 
        
    Set oSourceSQLServer = CreateObject("SQLDMO.SQLServer2")

    oSourceSQLServer.LoginTimeout = 15
    oSourceSQLServer.LoginSecure = True
    oSourceSQLServer.Connect sSourceSQLServerName    
                            
    ' make sure a good db name was provided 
    Set oDB = oSourceSQLServer(sDatabaseName)    

    If Not(oDB.Status = 1024) Then 'SQLDMODBStat_Standby

      ' set the backup disk device name
      If Right(sBackupFolder,1) <> "\" Then
        sBackupFolder = sBackupFolder & "\"
      End If
      sFileName = sBackupFolder & sDatabaseName & "." & sBackupFormat
 
     'SQL Lite Speed Backup
      If sBackupFormat = "sls" Then
      
        ' backup the database
        sCommand = "EXEC master.dbo.xp_backup_database @database = '" & sDatabaseName & _
                   "', @filename = '" & sFileName & "', @init = 1"
      
        ' Q279514 explains why no ExecuteWithResultsAndMessages2
        ' so don't capture QueryResult, just let it stream to stdout
        oSourceSQLServer.ExecuteWithResults(sCommand)
      
      End If
      
      'SQL Server Backup
      If sBackupFormat = "bak" Then 'SQL backup

        Set oBackup = CreateObject("SQLDMO.Backup")
        oBackup.Database = oDB.Name
        oBackup.Files = sFileName
        oBackup.Initialize = True
          
        oBackup.SQLBackup oSourceSQLServer
        
      End If
                            
    End If   

    oSourceSQLServer.DisConnect 
    Set oSourceSQLServer = Nothing
    
  End If ' valid db requested

  Main = DTSTaskExecResult_Success

End Function