'**********************************************************************
' restore a database from a backup file of the same name
' fail if not sls (sqlliteSpeed) or bak (native) format
' no support for system databases or the admin database
'************************************************************************

Function Main()
 
  Dim sBackupFolder, sTargetSQLServerName, sDatabaseName, sBackupFormat
  Dim oTargetSQLServer, oBackup
  Dim sFileName, sCommand
   
  sBackupFolder = DTSGlobalVariables("Backup Folder")
  sTargetSQLServerName = DTSGlobalVariables("Target SQL Server")
  sDatabaseName = DTSGlobalVariables("Database Name")
  sBackupFormat = DTSGlobalVariables("Backup Format")
  
  If Not (LCase(sBackupFormat) = "bak") And Not (LCase(sBackupFormat) = "sls") Then
    Err.Raise 1, "RestoreDatabase", "Invalid Backup Format - must be sls (SQLLiteSpeed) or bak (native SQL backup)"
  End If
  
  ' no system dbs nor admin
  If Not (LCase(sDatabaseName) = "master") _
  And Not (LCase(sDatabaseName) = "model") _
  And Not (LCase(sDatabaseName) = "msdb") _
  And Not (LCase(sDatabaseName) = "tempdb") _
  And Not (LCase(sDatabaseName) = "admin") Then
        
    Set oTargetSQLServer = CreateObject("SQLDMO.SQLServer2")
    
    oTargetSQLServer.LoginTimeout = 15
    oTargetSQLServer.LoginSecure = True
    oTargetSQLServer.Connect sTargetSQLServerName
                               
    ' set the backup disk device name
    If Right(sBackupFolder, 1) <> "\" Then
      sBackupFolder = sBackupFolder & "\"
    End If
    sFileName = sBackupFolder & sDatabaseName & "." & sBackupFormat
  
    'SQL Lite Speed Restore
    If sBackupFormat = "sls" Then
          
      sCommand = "EXEC master.dbo.xp_restore_database @database = '" & sDatabaseName & _
                 "', @filename = '" & sFileName & "', @filenumber = 1"

      'no executewithresultsandmessages allowed so just stream QueryResult to stdout
      oTargetSQLServer.ExecuteWithResults(sCommand)
        
    End If
        
    'SQL Server Restore
    If sBackupFormat = "bak" Then

      Set oRestore = CreateObject("SQLDMO.Restore")
      oRestore.Database = sDatabaseName
      oRestore.Files = sFileName
      oRestore.ReplaceDatabase = True
        
      oRestore.SQLRestore oTargetSQLServer
        
    End If

    oTargetSQLServer.Disconnect   
    Set oTargetSQLServer = Nothing 

  End If 'OK for this db to be restored

  Main = DTSTaskExecResult_Success

End Function