Options

Can I Update the Backup History Data

brogersbrogers Posts: 8
edited October 20, 2016 4:56AM in SQL Backup Previous Versions
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?
· Share on Twitter

Comments

  • Options
    Dan BDan B Posts: 287 Gold 5
    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
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
    · Share on Twitter
  • Options
    brogersbrogers Posts: 8
    Yes it does. But it does not show up in the restore window for available restore points.
    · Share on Twitter
  • Options
    Dan BDan B Posts: 287 Gold 5
    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?
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
    · Share on Twitter
  • Options
    brogersbrogers Posts: 8
    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
    · Share on Twitter
  • Options
    Dan BDan B Posts: 287 Gold 5
    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
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
    · Share on Twitter
Sign In or Register to comment.