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

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

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

Postby lculley2011 » Fri Apr 27, 2012 9:23 pm

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.
lculley2011
 
Posts: 6
Joined: Thu Dec 08, 2011 8:55 pm

Postby petey » Sun Apr 29, 2012 3:59 pm

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:

Code: Select all
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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Worked but still confused

Postby lculley2011 » Mon Apr 30, 2012 3:06 pm

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
lculley2011
 
Posts: 6
Joined: Thu Dec 08, 2011 8:55 pm

Postby petey » Mon Apr 30, 2012 3:46 pm

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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests