'**************************************************************
' Backup all databases on a server 
'
' backup type of "sls" for SQL Lite Speed "bak" for SQL Backup
'
' Keep the previous copy of all backup files
'**************************************************************

Option Explicit

Function Main()
  Dim sSourceSQLServerName
  Dim oSourceSQLServer, oDB, oBackup, oRestore
  Dim sBackupPath, sFileName, sOldFileName
  Dim oFileSystem, oFile
  Dim i
  Dim bFailed
  Dim sCommand, sMessages, oQueryResults
  Dim sType
    
  sBackupPath = DTSGlobalVariables("Backup Share") '"\\<file server>\<share>\<folder>\"
  sSourceSQLServerName = DTSGlobalVariables("Source SQL Server") '"<SQL Server Name>"
  sType = LCase(DTSGlobalVariables("Backup Type")) '"sls" ' SQL Lite Speed backup
  
  bFailed = False
  
  If sType = "sls" Or sType = "bak" Then
    
    Set oSourceSQLServer = CreateObject("SQLDMO.SQLServer2")
  
    Set oFileSystem = CreateObject("Scripting.FileSystemObject")

    oSourceSQLServer.LoginTimeout = 15
    oSourceSQLServer.LoginSecure = True
    oSourceSQLServer.Connect sSourceSQLServerName
    
    'Don't fail if made it this far, just inform if something goes wrong
    On Error Resume Next
              
    ' backup all databases
    For Each oDB In oSourceSQLServer.Databases
    
      ' Add any dbs to be excluded from backup here
      If LCase(oDB.Name) <> "tempdb" _
        And LCase(oDB.Name) <> "pubs" _
        And LCase(oDB.Name) <> "northwind" Then 

        ' delete/archive the backup files
        sFileName = sBackupPath & oDB.Name & "." & sType
                              
        ' tape backup should have removed all except .bak or .sls from folder
        ' if any do exist the tape process may be off line, so do not overwrite
        ' just use the lowest number that does not exists in folder as the backup file extension
        ' either the .1 or the .bak/.sls could be in use by backup
        ' .bak/.sls could also be in use by a restore
        i = 1
        While oFileSystem.FileExists(sBackupPath & oDB.Name & "." & CStr(i))
          i = i + 1
        Wend
    
        sOldFileName = oDB.Name & "." & CStr(i)
          
        If (oFileSystem.FileExists(sFileName)) Then
          Set oFile = oFileSystem.GetFile(sFileName)
          oFile.Name = sOldFileName
          Set oFile = Nothing
        End If
                           
        'SQL Lite Speed Backup
        If sType = "sls" Then
      
          ' backup the database
          sCommand = "EXEC master.dbo.xp_backup_database @database = '" & oDB.Name & _
                     "', @filename = '" & sFileName & "', @init = 1"
      
          'Q279514 explains why no ExecuteWithResultsAndMessages2
          Set oQueryResults = oSourceSQLServer.ExecuteWithResults(sCommand)
      
        End If
      
        'SQL Server Backup
        If sType = "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
    Next 'oDB
       
    oSourceSQLServer.DisConnect
   
    Set oFileSystem = Nothing
   
    Set oSourceSQLServer = Nothing
    
    On Error GoTo 0

  End If ' valid type requested

  If Not (bFailed) Then
    Main = DTSTaskExecResult_Success
  Else
    Main = DTSTaskExecResult_Failure
  End If

End Function