How to update Activity History based msdb restore

dnelsondnelson Posts: 1 New member
in SQL Backup Previous Versions
I restored the msdb database from 3 days prior. how can i have the activity history reflect based on that backup. so basically i will only see history of 3 days prior only.
Tagged:
· Share on Twitter

Answers

  • Alex BAlex B Posts: 1,157 Diamond 4
    Hi @dnelson

    First a bit on how it all connects as it may help:

    The information you see in the GUI Activity History and backup history is fed from the User Activity Cache. The User Activity Cache is per user and the cache files are located in the user's profile. In addition to the backup and restore history, GUI connection information is held in these files. The activity cache files are created when you register or add a SQL Instance into the GUI.

    The User Activity Cache obtains the backup and restore history from the SQL Server Compact Edition file (data.sdf) also known as the Local Data Store. The SQL Server Compact Edition is installed as part of the Server Side components. The data.sdf file queries the msdb database to obtain the backup and restore history. Also if the backup or restore task is performed using SQL Backup, additional information is added to the entry in the data.sdf and this then feeds the User Activity Cache.


    Now on to how we can try to address this -

    First off the least amount of history to import is 1 Week, but whether it's 1 week or 1 month it will be imported from the existing msdb database so it will be a weeks worth of history from the restored msdb database.

    If you need to only have the last 3 (or however many) days you can address this in a few ways. Either run sp_delete_backuphistory on the instance you have restored the msdb to before performing the following or afterwards you can select the server in the SQL Backup GUI, choose Tools > Server Options and then set the option at the bottom to delete everything older than 3 (or however many) days and close and reopen the GUI to make it run - then set it back to an appropriate amount of time so you keep more than than many days worth of history.

    To replace the existing Activity History you need to perform these steps:
    1. Remove the SQL Instance from the SQL Backup UI
    2. Stop the SQL Backup Agent-<instanceName> service for the instance which you have restored the msdb database
    3. Delete the data.sdf file located at %ProgramData%\Red Gate\SQL Backup\Data\<instanceName>
    4. Restart the SQL Backup Agent--<instanceName> service which will recreate the data.sdf
    5. Re-add the SQL instance to the SQL Backup GUI ensuring that you choose the 1 week option nmu0is2igdc7.jpg

    I hope this helps!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
Sign In or Register to comment.