GUI slow when selecting backups during a restore

qcjims

I have found that SQL Backup 6.x is very slow when it comes to picking backup files to use during a restore.

This slow performance occurs on any instance of SQL we have where we have 10 or more databases. Our typical backup scheme is full backup every other day, diffs every 8 hours and incrementals every 15 mins.

We have configured SQL Backup to only keep 60 days of backup history, and that made a big improvement in overall speed. However, it is still very slow.

Any ideas?



-js

peter.peart

Hi there,

Many thanks for your post.

We are aware that some users are experiencing this issue with the GUI, and we are looking into this. One thing to check though is whether you have any AV software which on access check which is looking at the below locations:

C:\Documents and settings\<user_name>\Application Data\Red Gate\SQL Backup\Servers.dat - Where <user_name> is the log in name of user

C:\Documents and settings\<user_name>\Local Settings\Application Data\Red Gate\SQL Backup\Server Data\ - Where <user_name> is the log in name of user

If so, you may wish to exclude those locations from the scan and see how you get on.

Pete

DonFerguson

I have said it before, but I will say it again.

This is one of those things that would be greatly improved upon if Red Gate were to ditch the use of SQL CE and local client caching. A simple restore history query directly to the SQL tables is lightning fast by comparison. I would recommend that the SQLBackup tables currently stored in the CE database be stored in the MSDB database instead. And there is really no need to waste local storage and CPU cycles by syncing data to the desktops local harddrive. This is extreemly painful for customers that have lots of instances with frequent backups.

tyanni

Same problem here - my gui is almost unusable. It fails to load the activity history for 30+ minutes, and selecting backups is almost as bad. I am at my wits end, and ready to switch to a new product.

peter.peart

Another thing to bear in mind with this is the amount of backup history that is being kept by the tool.

We have found that customers haven't set a purge policy, therefore the activity history is loading every single backup job that the server / database has ever performed. If you have a number of databases on the server, each performing transaction log backups every 15 minutes this soon mounts up to an enormous figure.

In an ideal world you would be able to retain all the history you could ever want, along with having the activity history and required backups for a restore appear very quickly, however unfortuantely that isn't possible at this time although it is very much on our radar.

Until this is something that is improved though, you may wish to look at implementing a purge policy on the server. This can be done by going to Tools > Server options from within the GUI and then selecting to delete backup history after X amount of time.

Hope that helps!

Pete

tyanni

Peter -

If we do purge old entries, how can we prove that a backup was done on a particular day? Is there a way to restore the old entries, or dump them to an archive database/file/etc? I have a mandate from management that I be able to prove backups were taken on a particular day, so I can't just purge old entries.

Also, I should note that I agree with the poster above who commented that if Red Gate back-ended to a better database (SQL Server, perhaps) performance would probably be a LOT better.

Thanks
Tim

peter.peart

Hi Tim,

Backup history is kept in the MSDB as well, so there isn't any reason why you couldn't, prior to deletion, extract the backup history information out of there in report format.

Alternatively, you could set up an inbox specifically for backup e-mail notifications, notify on any outcome and retain the e-mails of all successfully completed backup jobs. Both aren't ideal, although would go some way to resolving your issue I hope in the meantime.

There is also of course whether the original backup file exists in the first place. That would give definitive proof that a backup had been taken as the file names, by default, are date / time stamped. You may not have enough storage space for that though, however, thinking along those lines, I am also not sure if I understand what the value would be in proving if a backup was taken 100 days ago is if you are deleting backup files that are more than 90 days old.

Pete

tyanni

Peter -

Thanks, we are keeping emails, but I didn't realize that the backup history in the compact edition database is also kept in MSDB. That's really good to know, since it is easily queried.

As far as the backup history and purging goes, we purge our backups after 35 days. However, we write to a network share directly, not locally, and the share is backed up also. The tapes are kept for a year, so even if the file doesn't exist on the share, it can be retrieved from tape if needed.

Tim

peter.peart

Hi Tim,

I could be wrong, however as far as I am aware the compact database pretty much stores information on whether the backup was taken use RG SQL Backup or not.

You can actually delete the data.sdf file, and it will be recreated when a GUI next reconnects to that server. In the activity history, you will still see all of the backup history, as that is pulled from MSDB, but you won't be able to see the icon that denotes if a backup was taken using RG SQL Backup.

If you want to query the MSDB you can, all the info you require should be in the dbo.backupset table. Please note though that if you do set a purge, the information is wiped from MSDB too, so although you may have backups of that as well you will probably want to run the report prior to the deletion.

Pete

DonFerguson

I am very glad to hear that fixing this problem is on Red Gate's radar.

I do have a purge policy of 30 days. But due to the number of databases and frequency of transaction log backups on some instances, it just isn't enough. If I need to perform a restore with the GUI, I almost always have to restore directly from file as the restore history is just too slow. One interesting note, even if I query history from the CE database directly results often come back quickly compared to the GUI. I think the bigest issue is the whole caching logic. For reasons that I don't understand, the client GUI wants to copy all the data in the CE databases to the local desktop and then present the restore history from local cache. The problem with that logic is that it takes a long time to copy all the data. While I believe that ditching the CE is important, ditching local client caching is far more critical to improving GUI performance.

jerryhung

I almost never use the GUI to find backup history

I write queries to query MSDB database, and collect information I need (file path, duration, sizes, db, date started & ended.....)


To query with RedGate commands, this is what I use

/*
master..sqbdata 'SELECT * FROM backuphistory'
master..sqbdata 'SELECT * FROM backupfiles '
*/

master..sqbdata '
SELECT  dbname
	   ,backup_start, backup_end
       ,backup_type
       ,encryption
       ,compression_ratio
       ,compression_level
       ,duration AS [seconds]
       ,BH.size
       ,BH.compressed_size
       ,speed
       ,BF.name AS filename
       ,logfilename
FROM    backuphistory BH
        INNER JOIN backupfiles BF ON BH.id = BF.backup_id
WHERE 1=1
	AND DATEDIFF(d,backup_start, getdate()) < 2	-- less than X days old
ORDER BY BH.id DESC     
'

DonFerguson

Thanks for the query, I likewise work around the problem by quering msdb records and using other custom restore scripts. But sometimes it's just easier to use the GUI. The main drawback of course to using the GUI is performance.