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

lculley2011lculley2011 Posts: 10
edited April 30, 2012 10:46AM in SQL Backup Previous Versions
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.
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    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
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • lculley2011lculley2011 Posts: 10
    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
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.