SQL Backup

GlenLatimer

We are trialling this software currently and one process which we are looking to use this for is to restore some of the databases we back up thyen checkDB them.

On the GUI, the only reporting options are for backups and not restores

Is there a way to add restores reports in or must I query the SQLBackupReportingDatabase and if so can you confirm the query would be select * from SQLBackupReportingDatabase.dbo.restorehistory where code = 821

Thanks

Jessica R

Hi @GlenLatimer,

Thanks for your post!

Sorry to say that there isn't a way to add restore reports at the moment (we do have a feature request here which you can vote on).

To see if we can determine a query that might help, can I just confirm if you are looking to see where dbcc checkdb returns errors? Or are you looking for a full history of restore operations?

GlenLatimer

Hi Jessica

It is a reporrt of where DBCC failed

In the interim, I have created an SSRS report with the dataset as below (which I think is right) but the issue is that the tables do not update unless I go into the GUI, run a multiserver report for backups first

select
server_name
,restore_start
,restore_end
,dbname
,convert(varchar, (restore_end - restore_start ), 108) as totaltime
from SQLBackupReportingDatabase.dbo.restorehistory rh
Join SQLBackupReportingDatabase.dbo.identities i
on server_id = i.id
where code = 821
and restore_type = 'D'

squigley

To my knowledge, the data.sdf file is updated outside of the GUI, it is updated via the SQL Backup Agent service using the various SQL Backup Extended Stored Procedures.

When you open the SQL Backup GUI, your User Activity Cache would need to update and this process occurs automatically.  

Also the table you're attempting to query SQLBackupReportingDatabase.dbo.restorehistory is not a table in the data.sdf file but located in the reporting database called "SQLBackupReportingDatabase".  As the reporting database is a function of the GUI, it will not update until the GUI is opened.

From the query in your update, you're querying the reporting database highlighted in BOLD and  NOT the data.sdf file.

select
server_name
,restore_start
,restore_end
,dbname
,convert(varchar, (restore_end - restore_start ), 108) as totaltime
from SQLBackupReportingDatabase.dbo.restorehistory rh
Join SQLBackupReportingDatabase.dbo.identities i
on server_id = i.id
where code = 821
and restore_type = 'D'


There is a 'restorehistory' table in the data.sdf file.  You can download the CompactView tool from SourceForge using this URL:
https://sourceforge.net/projects/compactview/

However, you cannot connect it directly to the data.sdf file.  You need to make a copy of it and save to a different folder to be able to open it using the CompactView tool.

Let me know if you have any other questions or concerns.