Restore Wizard hangs at "Retrieving backup history".

sdelmore

I have tested restores from command line and they work fine but when I try to use the GUI for a restore it hangs at "retrieving backup history". I have about 200 databases on my server which are fully backed up nightly and have transaction log backups hourly. Is this generating too much history for the Sql Backup GUI to deal with?

Do I need to somehow purge the history when I am performing my backups (I am using ERASEFILES = 2) on my transaction log backups so I am keeping around 9600 transaction log backups on my server at any time (200DBs * 48 hourly backup files).

For my full nightly backups I am using my own cursor to loop through and backup the databases which I don't think gives me a good place to use the ERASEFILES option so I am deleting them with a powershell script. Do I need to do something else in this step to purge the outdated history or backups I no longer have? The GUI is very useful if I need to do a one off point in time restore, but is of no value to me if it hangs, or even if it does eventually return after an hour. I need to find a way for this to become more responsive. Alternatively, does anyone have a stored procedure or script that can be used to easily generate the restore commands needed to do a point in time restore from the latest full backup plus the appropriate T-Log backups?

Thanks,

Shane

sdelmore

Update: I let it sit for over an hour and the gui finally returned. The history is going back over a month, since I installed SqlBackup Pro. I believe that it is just choking on the number of entries in the backup history. I am going to look for a way to purge the log when I delete the files but if anyone knows off the top of their head how to delete the entries from the log when I delete the physical backup file that would be appreciated.

ChrisAVWood

In the GUI under Tools/Options or Server Options have you set anything under the delete from backup log history and backup history?

Chris

sdelmore

I just looked and this is set to 90 days (must be the default). I can set it to a shorter time frame, but when is it triggered? I don't use the GUI to perform my backups so will this setting still work?

petey

The old entries in the SQL Backup history tables, and also the msdb tables, are deleted every time you run a backup process using the retention period you set. So no, you do not have to use the GUI in order for the deletion to work.

sdelmore

That worked, thanks.