I'm not well-versed in SSIS but I suspect that the restore operation most likely encountered a failure. The reason why SSIS would probably report success is because your restore script does not raise an error to SQL Server's execution engine. SQL Backup does not do this for you so it's necessary to perform error trapping and handling in your own SQL script task, for instance:
- Code: Select all
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup N'-SQL "... backup command goes here ..."', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode) /* raises error condition */
To check that your restore was successful (or failed) you may want to have a look at the SQL Backup log files in the %allusersprofile%\\application data\\red gate\\sql backup\\log.