Where does master..sqbdata retrieve its data from?
DWolford1234
Posts: 44
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
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
When you run sqbdata, you pass a valid SQL Server CE command as the first parameter .e.g
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
Thanks
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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