Options
How to update Activity History based msdb restore
dnelson
Posts: 1 New member
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:
Answers
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:
I hope this helps!
Kind regards,
Alex
Have you visited our Help Center?