We are looking forward for some more reporting in SQL Backup. Actually it would be nice to have a report with the backup scheduling of a selected server and databases. This way we get an better overview of a our backup strategy at all. It would be nice to now which database it's backuped at which time, something like that.
Reporting is a tricky area, since requirements across a large base of customers are very variable – if you’re not careful, you end up building a generic reporting product within your product. So a common reporting strategy taken by products like SQL Backup Pro, is to ship with some pre-canned reports, and then make it possible for people to roll their own ad-hoc queries for needs that fall outside of the pre-canned reports.
This UserVoice suggestion includes some interesting candidate pre-canned reports: thanks for the ideas; and I’d like to share some information on where data is collected and stored by SQL Backup Pro, which should at least makes ad-hoc querying easier to do.
The data is stored in two locations:
- on each server running the SQL Backup Pro engine, in a SQL CE database (one database for each instance). You can find these at:
C:\ProgramData\Red Gate\SQL Backup\Data\[instance_name]\data.sdf
- on the PC where you run the SQL Backup Pro UI, in a SQL Lite database; this is a local cache of the data collected from each server running the SQL Backup engine. You can find this at:
C:\Users\[username]\AppData\Local\Red Gate\SQL Backup\Server Data\1.dat
There are various tools that can be used to query such databases (for eg, Database .NET, http://fishcodelib.com/database.htm)).
Note that some of the data displayed in the UI is queried from elsewhere, or calculated as needed. For eg, a backup job’s schedule isn’t stored here, since that scheduled could be changed directly in SSMS, so we’d get out of sync. You can directly query this from the msdb database (eg, see https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/).
Please note that these two database schemas are not managed schemas, and whilst it’s unlikely that they’ll change we can’t commit to not making changes that might break any custom integration done against them. Also, sometimes SQL Backup Pro will takes an exclusive lock against the SQL Lite database, so you may want to take a copy before running a query against it.
Hope this is of some help.
Do you could tell us any alternative to create such report with the information (database name, schedule, backup type, paths etc.) So, that we have at least an overview about the backup plan for our databases?
It would be nice if we could get a backup summary report for all db's on a server (i.e. name, schedule, paths, copies kept, etc) ... Some of this info isn't available in sql server that I know of ...
This would be a major + as sql server keeps limited info in msdb and what it does keep is cumbersome
We were looking for this yesterday! Also, the duration of the back up would be helpful.