Can I Update the Backup History Data

brogers

The History for one of my servers using Red Gate SQL Backup has about 2 weeks of the data that is missing for some reason. It is showing the current data and data from prior to Sept 13 but nothing in between. The data in the Activity History tab is fine but it is the data that shows up when restoring using the backup history is where it is missing. Is there a way to refresh that data, or will I have to just do with out the data?

Dan B

Hi brogers,

Thanks for posting on the Redgate forums.

Redgate SQL Backup should pull in the history from the MSDB database. Does the data you are missing in SQL Backup still appear in the MSDB database? The below query should pull this back.

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 40)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

brogers

Yes it does. But it does not show up in the restore window for available restore points.

Dan B

Hi brogers.

I apologize for the delay in coming back to you.

Is this a cluster setup? Was the cluster failed over between the 2 dates mentioned? Sept 13th to Oct 3rd?

brogers

Dan, this is not clustered, so there wasn't a failover. I looked and didn't see anything unusual except that the history is not showing up when selecting the restore.

~Bill Rogers

Dan B

Hi brogers,

What you could try is to recreate data.sdf file following the steps below. Please let me know if this repopulates the data.

* Close the SQL Backup GUI
* Stop the SQL Backup Agent Service for the SQL Instance in question
* Delete or rename the data.sdf file in the (ProgramDataRed GateSQL BackupData(local) or SQL Instance folder
* Restart the the SQL Backup agent service, this action will create a new data.sdf file
* Reopen the GUI, it may take a few seconds / minutes longer than normal to populate everything