Thursday, January 14, 2010

Rarring files, Maintenance Plans and powershell

Hi Peeps. Been a while. Happy 2010 and all that.

Right, automation, powershell and maintenance plans. All meant to make our lives easier. Throw in some WinRar (licensed of course ;-)) and maybe some multi server administration (SQL) and you have a seriously powerful mix of stuffs!

Ok, so, first the problem statement: I want to be able to backup up all my SQL databases and then subsequent to that, compress all these backups and remove rar files older than say, 2 days.

Now the solution overview: Create a maintenance plan in SQL Management Studio (SSMS). The 1st task would be a Backup database task to back up all your databases. Then the 2nd maint plan task would be a T-SQL task. This would code would start a SQL Agent job that would run a Powershell script. This PS script would trawl through the SQL backup directory and rar all the backup files. The 3rd maint plan task would also kick off a SQL Agent Job to remove rar files older than say 2 days. Groovy.

This all seems simple enough. The funky bit in the code (below) is to tell Powershell to wait for just enough time for Winrar to run to compress the current file. Thats because winrar is running outside of the PS process. I've divised a very simple strategy here. Take the size of the current file being rarred, divide it by a number that will return an integer value that equates to a wait time just long enough for the rar process to complete. This number will depend on your server hardware, so you may want to tweak it a bit. It works, beeee-yute-ifully!

Here is the PS script:

$WinRAR = 'C:\Program Files\WinRAR\Winrar.exe'
sl $path
$FullBackup = gci $path -recurse -include *.bak
foreach ($File in $FullBackup) {$FileName = $File.Fullname
if ($FileName -ne $Null) {#echo "Compressing $FileName
&$WinRar a -v50000 m "$FileName.rar" $FileName; $ss = $File.Length/2500000; write-host "Rarring $filename with wait time of $ss seconds"; start-sleep $ss}

The above basically gets (Get-ChildItem gci) all the backup files (*.bak) in a specified path ($path). Preceeding this it creates a Winrar object ($Winrar). Now we create a foreach loop to rar each file in our $FullBackup list of files. The funky bit is to take the File size ($File.Length) and divide it by 2500000. This gives us our wait time (start-sleep $ss).

Putting it all together:

I save the above PS script on the SQL Server. I then create a SQL Agent Job that runs this script. I don't set a schedule because it will be called by the Maintenance Plan Job. I then create my Maintenance Plan with the 1st step being my backup database task and my 2nd step being a custom T-SQL Task that executes the below T-SQL code to start a SQL Agent Job. T-SQL Code:

exec msdb..sp_start_job 'Rarfiles'

The last step is to create a 2nd SQL Agent Job that calls a PS script that deletes rar files in the SQL Backup directory that are older than, say, 2 days. This is a one liner:

gci '\\devss165\D$\MSSQL\MSSQL.1\MSSQL\Backup\' -recurse -include *.bak | where {($_.CreationTime -le $(Get-Date).AddDays(-2))} | ri -force

This 2nd SQL Agent will be called from the 3rd task in the Maintenance plan, also a custom T-SQL task, that would run a sp_start_job as well.

Now if you roll this out using a multi server maintenance plan to all your SQL servers you could just save your corporate a heap of disk space and money. :-).