How can we help you today? How can we help you today?

Where does master..sqbdata retrieve its data from?

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
DWolford1234
0

Comments

7 comments

Sort by
  • petey
    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.
    petey
    0
  • DWolford1234
    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
    DWolford1234
    0
  • petey
    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.
    petey
    0
  • DWolford1234
    Got it!

    Thanks
    DWolford1234
    0
  • petey
    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.
    petey
    0
  • DWolford1234
    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
    DWolford1234
    0
  • mdanielov
    here you go.
    mdanielov
    0

Add comment

Please sign in to leave a comment.