The job in question was created through a maintenance plan generated through the SSIS. The “generated” T-SQL for the backup process from SSIS is:
EXECUTE master.dbo.xp_create_subdir N'F:\MSSQL\MSSQL.1\MSSQL\Backup\master'
EXECUTE master.dbo.xp_create_subdir N'F:\MSSQL\MSSQL.1\MSSQL\Backup\model'
EXECUTE master.dbo.xp_create_subdir N'F:\MSSQL\MSSQL.1\MSSQL\Backup\msdb'
BACKUP DATABASE [master] TO DISK = N'F:\MSSQL\MSSQL.1\MSSQL\Backup\master\master_backup_2010_10_06_092757_4477669.bak' WITH NOFORMAT, NOINIT, NAME = N'master_backup_2010_10_06_092757_4477669', SKIP, REWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [model] TO DISK = N'F:\MSSQL\MSSQL.1\MSSQL\Backup\model\model_backup_2010_10_06_092757_4477669.bak' WITH NOFORMAT, NOINIT, NAME = N'model_backup_2010_10_06_092757_4477669', SKIP, REWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [msdb] TO DISK = N'F:\MSSQL\MSSQL.1\MSSQL\Backup\msdb\msdb_backup_2010_10_06_092757_4633920.bak' WITH NOFORMAT, NOINIT, NAME = N'msdb_backup_2010_10_06_092757_4633920', SKIP, REWIND, NOUNLOAD, STATS = 10
I am deriving the connection string from the “Data sources” tab from the “Edit” function of the “Steps” selection from the Job Properties. I don’t know if there is a better way to determine this information. The connection string provides as follows:
“server=’CHIVMC01’;Trusted_Connection=true;Application Name=’Microsoft SQL Server Management Studio’;Packet Size=4096;multipleactiveresultsets=false;
The logs report that the job is completing successfully. Nothing is being written to msdb.dbo.backupset table.
The simplest thing would be to drop and recreate the maintenance plan and see if that corrects the problem. The daily backing up the system dbs for this box is not critical, however, I would like to understand what might be happening here because if it can happen here it can happen elsewhere and the fact the job reports success when the job is not succeeding could be a cause for problems. Fortunately the problem can be detected by querying the backupset table, however I’d rather not have to rely upon that to detect this problem.