Where does master..sqbdata retrieve its data from?

DWolford1234DWolford1234 Posts: 44
edited December 21, 2007 12:40PM in SQL Backup Previous Versions
OR..Where does SQL Backup store its backup history?

I obviously know about the standard backupset / backupfile / backupmediafamily / etc. tables in msdb, but the following queries return data that I can't find in msdb:

EXECUTE master..sqbdata 'SELECT * FROM backuphistory'

EXECUTE master..sqbdata 'SELECT * FROM backuplog'


I traced these from the reporting tool built into SQL Backup. There were a few more, but the main question remains.
Where is the data stored that this Procedure retrieves? I see no fields for Commpression_Ratio, encryption, compression_level, etc. in msdb. It would be great to be able to see the source data and possibly build my own reports or notifictions off of it.

Also..

(I haven't checked the online help..I'm being lazy)..Is there a document that descries all of the functions sqbdata does? (What all can I select on using it)

I really hope the answers aren't going to be, "That's proprietary."

Thank You,
Dan

Comments

  • peteypetey Posts: 2,358 New member
    SQL Backup stores additional information re backup and restore processes in a SQL Server 2005 Compact Edition (CE) database. You can find this file in the <system drive>:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Data\<instance name> folder, named data.sdf.

    When you run sqbdata, you pass a valid SQL Server CE command as the first parameter .e.g
    EXEC master..sqbdata 'SELECT * FROM backuphistory'
    
    The SQL Backup Agent service will then run this command, and pass the result back to SQL Server.

    The easiest way to study the data model would be to connect to the CE database using SQL Server Management Studio.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Thanks Petey!

    Two more questions sprang from your answer. Is there any purging of old data that happens, and where can i find that data.sdf file in a clustered environment. I just checked on one of ours (expected the sdf to have a lot of data), and couldn't find it where you specified. I did find it on a non-clustered install, with no problems, however.

    Thank again,
    Dan
  • peteypetey Posts: 2,358 New member
    I will have to get back to you on the first question.

    As for the 2nd question, in a clustered environment, data.sdf will be located in the <drive>\Program Files\Red Gate\SQL Backup\Data\<instance name> folder, where <drive> is the SQL Server instances' data drive. This is done so that the CE database is stored on a shared drive, which is then accessible from both cluster nodes.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • peteypetey Posts: 2,358 New member
    As for the first question, the data is purged periodically if you instruct SQL Backup to do so. This is done by setting the values in the 'Delete old backup history' options in the Options dialog in the GUI.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Good to know. I see now the NOTE below that options not only speaks of msdb, but also of the 'internal cache'. I had always just assumed it was for msdb only since I didn't know about the CE file.

    Thanks for the answers. I appreciate that Red Gate was open and willing to share that info. Like I said in my first post, this could have easily gone the proprietary route.

    Dan
Sign In or Register to comment.