1 Attachment(s)
SIMS Tidy Backups - SQL 2008 - Autumn release
Hi All,
As you are all hopefully aware, Capita will be releasing SIMS Autumn release on the 9th November, with any lucky. Included in this is support for MSSQL 2008. There are a number of changes that will affect your setups, such as transaction log being replaced with Simple recovery model, so no more shrinking the log file! However on a more negative point you will lose tidy backups from System Manager and more importantly, compression from the backups. (See Resource: 16028)
I know this may sound bad, however, I think is a positive step. Have you ever had a problem in SIMS and you've had the head or such breathing down your neck to fix it, so you log a call with Capita or your Local Authority only to have them say, it's a known bug and there is a patch. So you decide to do it during the day, only to have dbupgrade take what seems like a lifetime to run a ruddy backup, and it appears to have crashed, at this stage you start to ask yourself, do I quit and use the /nobackup option? Well if you've ever looked into this, Capita did a while ago create a patch that removes compression, and OMG, it does help, I've seen backups take seconds (if the database is loaded in RAM and you have fast discs).
So this is good right? We'll how many of you routinely check you MSSQL\backup folder? Now you need start to look at compression as well. Lovely. Well I didn't like the idea of doing this, so I've created a program that will do this for you. I've uploaded it onto SupportNet, ID 688, failing that, it's attached on here too.
Overview.
It's entirely written in C#, hopefully well. It contains a built-in ZIP library - SharpZip, which in my opinion appears to be quicker then the Microsoft ZIP library. It only requires .NET Framework 2.0 - or it should ;). It's a small little app, 134KB in total, digitally signed to ensure it's unmodified. It will only compress uncompressed MSSQL backups (.bak) and will only delete compressed files (.zip) and MSSQL backups (.bak). It deletes before it compresses, so you don't waste time compressing only to delete. You can set the maximum age of the backup and it will delete them older then the date specified, it uses the date created. Compression is optional, and if you do choice to, it will set the creation date to the same as the MSSQL backup (.bak).
Usage
Download the program, save it to the local disc on the (SIMS) MSSQL server, I generally say create a batch file, but you can do whatever, run from command line…
Tidybackups.exe /PATH:[PATH TO YOUR MSSQL\BACKUP] /DAYS:[NO OF DAYS] [/ARCHIVE]
For example:
Tidybackups.exe /PATH:"C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup" /DAYS:30 /ARCHIVE
This will delete any compressed files (.zip) and MSSQL backups (.bak) over 30 days old, any remaining MSSQL backups will be compressed, and the uncompressed version deleted.
If you want to do logging, you can ofcourse redirect the output to a file, so add >>log.txt
This will write any files that are compressed and\or deleted.
Once you have create a batch file, I would then schedule it to run, daily, just to keep on top of things. Hopefully you'll find this useful, just remember it's not just limited to SIMS!
EDIT: Now on sourceforge at: tidybackups | Get tidybackups at SourceForge.net
Matt
