msdb.dbo.backupset does not contain Red Gate backup metadata

lculley2011

For certain databases I backed up using red gate backup outside of a sql job and for the rest I have them in a regularly scheduled job. For those backed up inside of a job, I get a record in msdb.dbo.backupset. For those dbs backed up in SSMS via the same syntax as in the job, I do not get a row in backupset. Why is this and where can I see my backup metadata for my non-job Red Gate backups? I have verified that the backup files exist btw.

Why do I need this information? I'm trying to create some scripts in order to see if there are any dbs that have not been backed up and should be and noticed that some dbs were not showing in backupset and therefore look like they are not backed up but in fact are.

petey

The backup metadata is created and stored in msdb..backupset by SQL Server itself, regardless of the application used to perform the backup. What is the result for the second query when you run the following in SSMS:

EXEC master..sqlbackup '-sql "BACKUP DATABASE model TO DISK = [<AUTO>]"'
GO
SELECT TOP 1 * FROM msdb..backupset WHERE database_name = 'model' ORDER BY backup_finish_date DESC
GO

lculley2011

That did work but when I had run the backup previously using the following, the data was not saved in backupset. I just tried again using the syntax below and did see data saved in backupset. A bit confused now.

DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [model] TO DISK = ''\\MIAUTP01\sqlbackup\MIASQP01\<DATABASE>\<AUTO>.sqb'' WITH ERASEFILES = 2, MAILTO_ONERRORONLY = ''group@company.com'', DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, THREADCOUNT = 3, VERIFY"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

petey

The data in backupset, backupmediaset, and backupmediafamily is created by SQL Server whenever a backup is performed. SQL Backup then updates some values in that data, to provide additional details re. the backup process and compressed file(s).

If you do not find any records in those tables, you should check the SQL Server error log for any errors pertaining to SQL Server failing to write the backup history records.