'**************************************************************
' Backup all databases on a server and restore the desired
' user databases to another server (i.e. Primary -> Fallback)
'
' 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, sTargetSQLServerName
  Dim oSourceSQLServer, oTargetSQLServer, oDB, oBackup, oRestore
  Dim sBackupPath, sFileName, sOldFileName
  Dim oFileSystem, oFile
  Dim i
  Dim bFailed
  Dim sCommand, sMessages, oQueryResults
  Dim sType
    
  sBackupPath = DTSGlobalVariables("Backup Share") 
  sSourceSQLServerName = DTSGlobalVariables("Source SQL Server")
  sTargetSQLServerName = DTSGlobalVariables("Target SQL Server")
  sType = DTSGlobalVariables("Backup Type") '"sls" ' SQL Lite Speed backup
  
  bFailed = False
  
  If sType = "sls" Or sType = "bak" Then
    
    Set oSourceSQLServer = CreateObject("SQLDMO.SQLServer2")
    Set oTargetSQLServer = CreateObject("SQLDMO.SQLServer2")
  
    Set oFileSystem = CreateObject("Scripting.FileSystemObject")

    oSourceSQLServer.LoginTimeout = 15
    oSourceSQLServer.LoginSecure = True
    oSourceSQLServer.Connect sSourceSQLServerName
    
    oTargetSQLServer.LoginTimeout = 15
    oTargetSQLServer.LoginSecure = True
    oTargetSQLServer.Connect sTargetSQLServerName
    
    'Don't fail if made it this far, just inform if something goes wrong
    On Error Resume Next

    ' backup all databases, restore user dbs except admin
    For Each oDB In oSourceSQLServer.Databases
    
      If oDB.Name <> "tempdb" Then ' Add any dbs to be excluded from backup here
        ' 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 exists 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
                            
        ' restore to fallback machine no restore if backup failed system databases  are system objects
        ' admin always skipped
        ' customer is under log shipping so restore skipped by default
        ' DM is under replication so restore skipped by default 
        If Err.Number = 0 _
           And Not(oDB.Name = "admin") _
           And Not(oDB.Name = "customer") _ 
           And Not(oDB.Name = "DM") _
           And Not(oDB.SystemObject) Then
                  
          'SQL Lite Speed Restore
          If sType = "sls" Then
          
            sCommand = "EXEC master.dbo.xp_restore_database @database = '" & oDB.Name & _
                       "', @filename = '" & sFileName & "', @filenumber = 1"

            Set oQueryResults = oTargetSQLServer.ExecuteWithResults(sCommand)
        
          End If
        
          'SQL Server Restore
          If sType = "bak" Then

            Set oRestore = CreateObject("SQLDMO.Restore")
            oRestore.Database = oDB.Name
            oRestore.Files = sFileName
            oRestore.ReplaceDatabase = True
        
            oRestore.SQLRestore oTargetSQLServer
        
          End If
            
          If Err.Number <> 0 Then
            bFailed = True
          End If
      
        End If
      End If
    Next 'oDB
       
    oTargetSQLServer.DisConnect
    oSourceSQLServer.DisConnect
   
    Set oFileSystem = Nothing
   
    Set oSourceSQLServer = Nothing
    Set oTargetSQLServer = 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