Red Gate forums :: View topic - msdb.dbo.backupset does not contain Red Gate backup metadata
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

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

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
lculley2011



Joined: 08 Dec 2011
Posts: 6

PostPosted: Fri Apr 27, 2012 9:23 pm    Post subject: msdb.dbo.backupset does not contain Red Gate backup metadata Reply with quote

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.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Sun Apr 29, 2012 3:59 pm    Post subject: Reply with quote

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:
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
Back to top
View user's profile Send private message Send e-mail
lculley2011



Joined: 08 Dec 2011
Posts: 6

PostPosted: Mon Apr 30, 2012 3:06 pm    Post subject: Worked but still confused Reply with quote

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
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Mon Apr 30, 2012 3:46 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group