 |
SQL Squeeze - A free tool to compress your backups 
An introduction
I had another phone call from a well known SQL Server application provider trying to sell me SQL Server applications. They always ask how many SQL Servers I manage, followed by a short sucky whistle when I tell them 47. Then they make the hard-sell. Anyway, this time it was backup compression software that was on offer, and I thought actually that could be quite useful. Disk space is mostly cheap now-a-days, but not for high-end systems. Also, we have hundreds of drives for tens of applications and it does add up. Working for local government, it's a nightmare getting more space signed off too and is never instant. I could see the benefits for me as well as the organisation.
I looked at a few solutions available on the web such as that provided by Muthusamy Anantha Kumar. He uses a Winzip Command-Line add-in and calls it using xp_cmdshell. It's an interesting read, but I wasn't too keen on it, as it had no consideration for retaining backups for a specified period. Also it relied on another 3rd party tool that would need purchasing. Really, what I wanted was a neat little package that backed up and then deleted the backup after a set amount of days, just like a regular Maintenance Plan. I also wanted to use something that would work in conjunction with the existing plans. SQL Server 2005 plans use an extended procedure xp_delete_file to delete the backups. However after a little tinkering, it became evident that the proc checks against the master database to see if the backups were genuine, so I wouldn't be able to use it to delete .zip files.
Hmm. At that point, I wondered if there were any freebie .net compression assemblies I could use. There is. SharpZipLib. Together with the Dot Net File Class, creating the above is not difficult.
What the app does...
A console application, it will accept an optional argument, look at a .ini file and take in a number of parameters:
[DurationBeforeZip]
Integer
|
|
[Compression]
0 >Integer < 10
|
[Paths]
String
|
|
Days=1
Hours=0
Minutes=0
|
Days=3
Hours=0
Minutes=0
|
CompLevel=9
|
BackupPath="D:\BackUps"
|
|
[Extensions]
String
|
[LogSizeLimit]
Integer
|
[BlockSize]
Integer
|
[ProcessPriority]
String
|
|
Tran="trn"
Full="bak"
|
MB=1
|
BSLevel=9
|
PriorityLevel="Idle"
|
It will search the specified folder for any files that end with the specified extensions and if they are older than a specified amount of time, zip them up, and then delete the original files. It will then delete any other .zip files older than the specified retention period. The zipping and deleting is recursive. File names are retained after compression. A log file is written to and it's maximum size can be specified. Click below for a simplified flow chart.
Right. The Code
There are six main steps during the whole process. Checking for and reading the optional argument, reading the .ini file, deleting the log file if it is bigger than the specified limit, zipping the files, then deleting the appropriate ones and finally printing when the process completed.
|
Sub Main()
'Read Console Argument
ReadArgument()
'Read Parameters
ReadINI()
'Delete Log
DeleteLog()
'Set the priority of the process
SetPriority()
'Zip the files recursively
Zip()
'Cleanup
RecursiveDelete()
'Sign off logfile
ProcessCompleted()
End Sub
|
The optional argument
The argument comes in the form of a path. This is the path to the ini file. If no argument is specified, then the application will look in the current directory of the executable for the ini file.
|
Private Sub ReadArgument()
'If a path argument is specified then hold on to it
If My.Application.CommandLineArgs.Count > 0 Then
PathArg = My.Application.CommandLineArgs(0).ToString
End If
If PathArg <> Nothing Then
currentDir = PathArg
Else
'Otherwise hold on to the current directory
currentDir = Directory.GetCurrentDirectory
End If
'Now we know the path, lets construct paths to the log and ini file
currentLog = currentDir + "\" + "SQLSqueeze_Log.txt"
currentConfig = currentDir + "\" + "SQLSqueeze_config.ini"
End Sub
|
We then read the ini file. Within the actual file, parameters are created in blocks...
|
[Retention]
Days=3
Hours=0
Minutes=0
[DurationBeforeZip]
Days=1
Hours=0
Minutes=0
[Compression]
CompLevel=9
[Paths]
BackupPath="D:\BackUps"
[Extensions]
Tran="trn"
Full="bak"
[LogSizeLimit]
MB=1
[BlockSize]
BSLevel=9
[ProcessPriority]
ProcessLevel="Idle"
|
and each chunk is read into variables used later.
|
Private Sub ReadINI()
INIsource = New IniConfigSource(currentConfig)
RetentionDays = INIsource.Configs("Retention").Get("Days")
RetentionHours = INIsource.Configs("Retention").Get("Hours")
RetentionMinutes = INIsource.Configs("Retention").Get("Minutes")
ZipDays = INIsource.Configs("DurationBeforeZip").Get("Days")
ZipHours = INIsource.Configs("DurationBeforeZip").Get("Hours")
ZipMinutes = INIsource.Configs("DurationBeforeZip").Get("Minutes")
Path = INIsource.Configs("Paths").Get("BackupPath")
CompressionLevel = INIsource.Configs("Compression").Get("Level")
tranext = INIsource.Configs("Extensions").Get("Tran")
fullext = INIsource.Configs("Extensions").Get("Full")
LogSizeLimit = INIsource.Configs("LogSizeLimit").Get("MB")
BlockSizeLevel = INIsource.Configs("BlockSize").Get("Level")
ProcessPriority = INIsource.Configs("ProcessPriority").Get("ProcessLevel").ToUpper
End Sub
|
Checking the size of the log file
The log file will be written to and time-stamped every time a file is compressed or deleted. I thought this was important as if you are dealing with hundreds of files and something goes wrong, you want to know what has been processed successfully. The file will also be written to if an exception occurs. So, potentially, the log can grow quite a bit in size, and will need to be deleted if its size exceeds that specified by us in the .ini file.
|
Private Sub DeleteLog()
'If the log file already exists, check the size of it
If System.IO.File.Exists(currentLog) Then
Dim LogFile As New FileInfo(currentLog)
Dim ActualLogSize As Integer = LogFile.Length
'If the size is bigger than the limit specified, delete it
If (ActualLogSize / 1000000) > LogSizeLimit Then
System.IO.File.Delete(currentLog)
End If
End If
End Sub
|
Setting the priority of the SQL Squeeze process
|
Private Sub SetPriority()
Select Case ProcessPriority
Case "IDLE"
System.Diagnostics.Process.GetCurrentProcess.PriorityClass = ProcessPriorityClass.Idle
Case "BELOWNORMAL"
System.Diagnostics.Process.GetCurrentProcess.PriorityClass = ProcessPriorityClass.BelowNormal
Case "NORMAL"
System.Diagnostics.Process.GetCurrentProcess.PriorityClass = ProcessPriorityClass.Normal
Case "ABOVENORMAL"
System.Diagnostics.Process.GetCurrentProcess.PriorityClass = ProcessPriorityClass.AboveNormal
Case "HIGH"
System.Diagnostics.Process.GetCurrentProcess.PriorityClass = ProcessPriorityClass.High
Case "REALTIME"
System.Diagnostics.Process.GetCurrentProcess.PriorityClass = ProcessPriorityClass.RealTime
End Select
End Sub
|
Compressing the files
The compression is split up in to sub-stages, as commented below in the code.
|
Public Sub Zip()
'Search and hold on to all the directories in the backup folder
astrDirNames = Directory.GetDirectories(Path, "*.*", SearchOption.AllDirectories)
'Make sure the structure of the directories are correct (see below)...
ValidateFolders()
'Adjust the path so that it points to each directory recursively.
For Each strDir In astrDirNames
Path = strDir + "\"
'Go here for further instructions (see below)...
RecursiveZip()
Next
End Sub
|
So as not to miss any files, the directories must be structured as a folder within another folder for example:
The following checks for this and exits with a message written to the log if this is not the case...
|
Private Sub ValidateFolders()
'The actual files must be in a folder within a folder, otherwise exit
If astrDirNames.Length = 0 Then
My.Computer.FileSystem.WriteAllText(currentLog, Environment.NewLine _
+ "Error: Backup files cannot be placed in a 1st level folder but must be " + _
"placed in a folder within a root folder such as 'Backup_Folder'..." _
+ Environment.NewLine + "No files have been processed", True)
End
End If
'If there are tlog files incorrectly placed in the first level folder, exit
If Directory.GetFiles(Path, "*." + tranext).Length > 0 Then
My.Computer.FileSystem.WriteAllText(currentLog, Environment.NewLine _
+ "Error: Backup files cannot be placed in a 1st level folder but must be " + _
"placed in a folder within a root folder such as 'Backup_Folder'..." _
+ Environment.NewLine + "No files have been processed", True)
For Each strFile In Directory.GetFiles(Path, "*." + tranext)
My.Computer.FileSystem.WriteAllText(currentLog, Environment.NewLine _
+ "Offending file: " + strFile, True)
Next
End
End If
'As above but for the full back up files
If Directory.GetFiles(Path, "*." + fullext).Length > 0 Then
My.Computer.FileSystem.WriteAllText(currentLog, Environment.NewLine _
+ "Error: Backup files cannot be placed in a 1st level folder but must be " + _
"placed in a folder within a root folder such as 'Backup_Folder'..." _
+ Environment.NewLine + "No files have been processed", True)
For Each strFile In Directory.GetFiles(Path, "*." + fullext)
My.Computer.FileSystem.WriteAllText(currentLog, Environment.NewLine _
+ "Offending file: " + strFile, True)
Next
End
End If
End Sub
|
All files are compressed by calling "ZipFile" (as provided with the SharpZipLib libraries mentioned at the beginning of this article) recursively.
|
Public Sub RecursiveZip()
Try
'Retreive the list of tran files at the path specified in the ini file
astrFileNames = Directory.GetFiles(Path, "*." + tranext)
'Extract the filename from the path
For Each strFile In astrFileNames
Dim dFile As New FileInfo(strFile)
'If the file was created after the DurationBeforeZip parameter then zip it
If dFile.CreationTime < DateTime.UtcNow.AddDays(-Zipdays).AddHours _
(-ZipHours).AddMinutes(-ZipMinutes) Then
strFileName = strFile.Substring(strFile.LastIndexOf("\") + 1)
'Compress the file and give it an extention of zip
'Parameters are drawn from the ini file
ZipFile(Path + strFileName, Path + strFileName + ".zip", CompressionLevel, BlockSizeLevel)
'Then write the accomplished to the log file
My.Computer.FileSystem.WriteAllText(currentLog, _
Environment.NewLine + Path + strFileName + " compressed successfully on " _
+ DateTime.UtcNow, True)
End If
Next
Catch ex As Exception
My.Application.Log.WriteException(ex, TraceEventType.Error, _
Environment.NewLine + "An exception has occured when compressing file ")
My.Computer.FileSystem.WriteAllText(currentLog, Environment.NewLine _
+ ex.ToString + ": " + strFileName + Environment.NewLine, True)
End Try
'----------
Try
'As above but for full backup files eg. bak
astrFileNames = Directory.GetFiles(Path, "*." + fullext)
'Extract the filename from the path
For Each strFile In astrFileNames
Dim dFile As New FileInfo(strFile)
'If the file was created after the DurationBeforeZip parameter then zip it
If dFile.CreationTime < DateTime.UtcNow.AddDays(-Zipdays).AddHours _
(-ZipHours).AddMinutes(-ZipMinutes) Then
strFileName = strFile.Substring(strFile.LastIndexOf("\") + 1)
ZipFile(Path + strFileName, Path + strFileName + ".zip", CompressionLevel, BlockSizeLevel)
My.Computer.FileSystem.WriteAllText(currentLog, _
Environment.NewLine + Path + strFileName + _
" compressed successfully on " + DateTime.UtcNow, True)
End If
Next
Catch ex As Exception
My.Application.Log.WriteException(ex, _
TraceEventType.Error, _
Environment.NewLine + "An exception has occured when compressing file ")
My.Computer.FileSystem.WriteAllText(currentLog, Environment.NewLine _
+ ex.ToString + ": " + strFileName + Environment.NewLine, True)
End Try
End Sub
|
The following is the call (ZipFile) to the code that talks to the compression libraries in the above routine and does the actual zipping up. There are four input parameters, the path to the file, what the file will be called after it is zipped, the compression level from 0 to 9 with the higher end being a higher compression rate, and finally the Blocksize, the number of bytes compressed at a time (the block size affects both the compression ratio achieved, and the amount of memory needed for compression and decompression). BlockSize 1 through BlockSize 9 specifies the block size to be 100,000 bytes through 900,000 bytes respectively.
ZipFile(Path + strFileName, Path + strFileName + ".zip", CompressionLevel, BlockSize)
|
'Zips the file
'Check to see if the file exists. Remember we are still usig our variables that hold
'paths to the files
Public Sub ZipFile(ByVal strFileToZip As String, ByVal strZippedFile As String, _
ByVal nCompressionLevel As Integer, ByVal nBlockSize As Integer)
If (Not System.IO.File.Exists(strFileToZip)) Then
Throw New System.IO.FileNotFoundException("The specified file " + _
strFileToZip + "could not be found. Zipping aborted.")
End If
'Prep the file
Dim strmStreamToZip As System.IO.FileStream
strmStreamToZip = New System.IO.FileStream(strFileToZip, System.IO.FileMode.Open, System.IO.FileAccess.Read)
Dim strmZipFile As System.IO.FileStream
strmZipFile = System.IO.File.Create(strZippedFile)
Dim strmZipStream As ZipOutputStream
strmZipStream = New ZipOutputStream(strmZipFile)
Dim myZipEntry As ZipEntry
'The name of the file (strFileName)...
myZipEntry = New ZipEntry(strFileName)
strmZipStream.PutNextEntry(myZipEntry)
strmZipStream.SetLevel(nCompressionLevel)
Dim abyBuffer(nBlockSize) As Byte
Dim nSize As System.Int32
nSize = strmStreamToZip.Read(abyBuffer, 0, abyBuffer.Length)
strmZipStream.Write(abyBuffer, 0, nSize)
Try
While (nSize < strmStreamToZip.Length)
Dim nSizeRead As Integer
nSizeRead = strmStreamToZip.Read(abyBuffer, 0, abyBuffer.Length)
strmZipStream.Write(abyBuffer, 0, nSizeRead)
nSize = nSize + nSizeRead
End While
Catch Ex As System.Exception
Throw Ex
End Try
strmZipStream.Finish()
strmZipStream.Close()
strmStreamToZip.Close()
End Sub
|
So now the files in each directory are compressed. The directory now holds newly created zip files, the original back up and tlog files, and possibly zip files from before. We now need to delete files that were zipped beyond the retention period specified in the .ini file.Then we delete all files ending in the backup and tlog extensions specified in the .ini file.
|
Private Sub RecursiveDelete()
'For each directory in the top directory
For Each strDir2 In astrDirNames
Path = strDir2 + "\"
'Then go here to delete the files
DeleteFiles()
Next
End Sub
|
|
Private Function DeleteFiles() As Integer
Try
'For each zip file in the backup directory
For Each strFile2 In Directory.GetFiles(Path, "*.zip")
'We will want an attribute of that file
Dim dDir As New FileInfo(strFile2)
'If the creation time is greater than that specified in the ini file...
If dDir.CreationTime < DateTime.UtcNow.AddDays(-RetentionDays).AddHours _
(-RetentionHours).AddMinutes(-RetentionMinutes) Then
Delete(strFile2)
My.Computer.FileSystem.WriteAllText(currentLog, Environment.NewLine _
+ strFile2 + " deleted successfully on " + DateTime.UtcNow, True)
End If
Next
Catch ex As Exception
My.Application.Log.WriteException(ex, _
TraceEventType.Error, _
Environment.NewLine + "An exception has occured when deleting file ")
My.Computer.FileSystem.WriteAllText(currentLog, Environment.NewLine _
+ ex.ToString + ": " + strFile2 + Environment.NewLine, True)
End Try
'-----------------------------------------
Try
'Delete uncompressed backup files older than the DurationBeforeZip parameter
For Each strFile In Directory.GetFiles(Path, "*." + fullext)
Dim dFile As New FileInfo(strFile)
If dFile.CreationTime < DateTime.UtcNow.AddDays(-Zipdays).AddHours _
(-ZipHours).AddMinutes(-ZipMinutes) Then
Delete(strFile)
My.Computer.FileSystem.WriteAllText(currentLog, Environment.NewLine + _
strFile + " deleted successfully on " + DateTime.UtcNow, True)
End If
Next
Catch ex As Exception
My.Application.Log.WriteException(ex, _
TraceEventType.Error, _
Environment.NewLine + "An exception has occured when deletng file ")
My.Computer.FileSystem.WriteAllText(currentLog, Environment.NewLine _
+ ex.ToString + ": " + strFile + Environment.NewLine, True)
End Try
'--------------------------------------
Try
'Regardless of creation time, delete all tran files
For Each strFile In Directory.GetFiles(Path, "*." + tranext)
Dim dFile As New FileInfo(strFile)
'Delete uncompressed backup files older than the DurationBeforeZip parameter
If dFile.CreationTime < DateTime.UtcNow.AddDays(-Zipdays).AddHours _
(-ZipHours).AddMinutes(-ZipMinutes) Then
Delete(strFile)
My.Computer.FileSystem.WriteAllText(currentLog, Environment.NewLine + _
strFile + " deleted successfully on " + DateTime.UtcNow, True)
End If
Next
Catch ex As Exception
My.Application.Log.WriteException(ex, _
TraceEventType.Error, _
Environment.NewLine + "An exception has occured when deleting file ")
My.Computer.FileSystem.WriteAllText(currentLog, Environment.NewLine _
+ ex.ToString + ": " + strFile + Environment.NewLine, True)
End Try
End Function
|
Finally, we sign off the log file.
|
Private Sub ProcessCompleted()
My.Computer.FileSystem.WriteAllText(currentLog, _
Environment.NewLine + "------------------------------------------------------------" _
+ Environment.NewLine + "Process Completed at: " + DateTime.UtcNow _
+ Environment.NewLine + "SQL Squeeze. Developed by Drew Salem - www.thebuddingdba.com" _
+ Environment.NewLine + "------------------------------------------------------------" _
+ Environment.NewLine, True)
End Sub
|
And there you have it. A tool that will compress your backup files, and delete old ones as specified by you.
If you would like to download SQL Squeeeeeze and read suggestions on how to use it, you can do by clicking here.
Disclaimer:
The information in this website including this weblog is provided "AS IS" with no warranties, and confers no rights. This weblog does not represent the thoughts, intentions, plans or strategies of my employer. It is solely my own personal opinion. All code samples (if any, ever) and downloads are provided "AS IS" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.
|
 |