Configure your Red Gate SQL Backup log folder
I stumbled across an interesting problem today. One of our production clusters was starting to run out of space on the C:\. I went in and expected to find the usual suspects: $kbuninstall$ folders in C:\Windows\, crap in C:\Temp\, people downloading huge installation files and leaving them in C:\ root or on their desktop… no, it was none of these. I had 9 GB of data inside C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\ …probably a year and a half's worth of tiny, 3 KB log files. Because of the sheer quantity it took a long time to delete, so in the meantime I fired up the SQL Backup GUI to figure out why things were going here.
Apparently this is the default location, and from browsing a few threads over on the Red Gate forums, it seems this is intentional. Had I known it was going to log everything it does, I may have looked into it sooner; as it stands, there is nothing during setup that prompts me to change the location or even lets me know what the process is going to be. And of course after the product is installed on the server, I handle everything with command line and calling master.dbo.sqlbackup, so I hadn't been in the GUI long enough to even notice that such a setting existed.
If you launch the SQL Backup GUI, go to Tools | Options and you will find a tab called File Management (it should be highlighted by default).
Customize the path to somewhere more appropriate than the system drive. If you have SQL Backup installed on a cluster, you should probably use a shared drive for this (in my case I moved it to Y:\). If you use a local disk, unless you ensure that the same folder structure exists on all nodes, the logging will stop working in the event of a failover. Here you can also configure how much trailing history you want to keep.
If you are already using SQL Backup, this may be a known issue to you. Or it may be brain dead obvious. However, if you weren't aware of this issue, I would check your settings and configure them correctly now, rather than next Saturday when you get paged at 4 AM. Also keep it in mind if you are planning to deploy SQL Backup to new servers in the future.
I don't understand how I can view the backup history into SQL Management Studio if I make a backup with Redgate.
Help me, please !!!
Yep, killed me once. I now have a batch job that manages these things as it generates a log file for each database backup. We do full backups every night and transaction logs every hour. The number of files was monstrous to manage from the interface as it had to walk through each of the files and decide to delete them. As we have SOX requirements and other internal requirements on how long we keep logs, we found it easier to have a batch job that moved the files over 1 day old to another share located on our NetApp appliance. This gave us the best combo for performance and reportability and sustainability.
Hi Aaron,
SQL Backup uses the local profile (C:\Documents and Settings\All Users\Application Data) for logging and temporary data as recommended by Microsoft in its application development guidelines. This is particularly relevant now that these are quite strictly enforced by UAC in Windows Server 2008 and Vista. We will be introducing a step in the installer where you are prompted to customise the logging path. This should be present in SQL Backup 5.5 (estimated release date Q1 2009) onwards.
As you pointed out, we do provide an option to purge the backup logs. We would recommend this is enabled, but we don’t enable it by default as this would need to be evaluated on user by user basis to ensure that it fits their operations and log retention policies.
Thanks for highlighting this issue,
—
Daniel KJ
Software Engineer – DBA Tools
Red Gate Software
Thank you man.
I really do like Red Gate's stuff but it will be nice to have that feature built-in if and when we ever get the approval to migrate to SQL Server 2008. They're talking about upgrading Visual Studio for everyone (still on 2005 in most cases) so there is hope yet. That's a different order of magnitude in license costs though when you don't have software assurance. :-\
I have SP3 on a client's cluster. One weird issue is that turned off SQL authentication and left it in Windows auth only. This did not happen on any of my testing in dev/qa environments, my own machine, etc. So maybe there were other circumstances. I'm already confident with the level of code, just don't trust setup all that much. I have build 4028 running on another production cluster and during our maintenance window tonight I will be applying SP3 and the post-SP3 CU. I will post back with thumbs up or down, if I remember. I am sure you will prod me about it if I don't. 🙂
Hi Aaron
Thanks, we are about to start using Red-Gate backup tiool. BTW , do you have SP3 installed on the cluster ? 🙂