Article

Powershell + 7zip + SQL Server backups

I needed a way to shrink the backups in SQL Server 2005 (native compression doesn’t exist until the 2008 version). 7zip compresses better than most anything, it’s free (even for commercial use), its stable, and it’s small. Powershell is my favorite tool builder. This is the result:

#set alias to 7zip - adjust path as necessary
set-alias sevenZip "c:program files7-zip7z.exe"

#list of database filenames
$dbdirectories = "db1","db2","db3","db4"

$dbbackupDrive = "x:"                              # this is where SQL Server stores its backups
$dbzdestination = "y:7zipped_backups"             # this is where we will store the 7z version

#Backups location
cd "$dbzdestination"                                # change directory 

#create some file masks based on date

$ybu = get-date (Get-Date).addDays(-1) -format "yyyyMMdd"    # this is the filename mask of yesterday's backups
$yz = get-date (Get-Date).addDays(-1) -format "yyyy.MM.dd"   # this is the filename mask of the zip destination
$yzr = get-date (Get-Date).addDays(-2) -format "yyyy.MM.dd"  # these are the day old 7zips (unused yet)
                                                             # I may want to remove older 7zips after copied offsite
                                                             
#$exclude1 = "-x!*.dif"                             # unused but could be appended to command 
#$exclude2 = "-x!*.7z"                              # unused but could be appended to command 

foreach ($dbdirectory in $dbdirectories)            # looping each db name in our list,
{  

    $backuppath = $dbbackupDrive + $dbdirectory + ""            #build the path to the backup sources
    $backupfiles = $dbdirectory + "_backup_" + $ybu + "*.*"       #build the filename mask
    $input = $backuppath + $backupfiles                          #full path to source files (SQL Server's files)

    $outputfile = $dbdirectory + "_backup_" + $yz + ".7z"        #build the 7z filename (7zip file)

    cd "$dbzdestination$dbdirectory"               #change the path to the backup destination
    
    # command line switch descriptions (see http://dotnetperls.com/7-zip-examples)
    #   -t7z = 7zip type
    #   -v10m = split to 10 meg files
    #   -mmt = multithreading
    
    "7zip a -t7z -v10m -mmt=off $outputfile $input"               # echo out some progress    
    
    sevenZip a -t7z -v10m -mmt=off "$outputfile" "$input"         # 7zip them up

}

# we're all done, let's cleanup
Remove-Item Alias:sevenZip

#set alias to 7zip - adjust path as necessary

set-alias sevenZip "c:program files7-zip7z.exe"

#list of database filenames

$dbdirectories = "db1","db2","db3","db4"

$dbbackupDrive = "x:" # this is where SQL Server stores its backups

$dbzdestination = "y:7zipped_backups" # this is where we will store the 7z version

#Backups location

cd "$dbzdestination" # change directory

#create some file masks based on date

$ybu = get-date (Get-Date).addDays(-1) -format "yyyyMMdd" # this is the filename mask of yesterday's backups

$yz = get-date (Get-Date).addDays(-1) -format "yyyy.MM.dd" # this is the filename mask of the zip destination

$yzr = get-date (Get-Date).addDays(-2) -format "yyyy.MM.dd" # these are the day old 7zips (unused yet)

# I may want to remove older 7zips after copied offsite

#$exclude1 = "-x!*.dif" # unused but could be appended to command

#$exclude2 = "-x!*.7z" # unused but could be appended to command

foreach ($dbdirectory in $dbdirectories) # looping each db name in our list,

{

$backuppath = $dbbackupDrive + $dbdirectory + "" #build the path to the backup sources

$backupfiles = $dbdirectory + "_backup_" + $ybu + "*.*" #build the filename mask

$input = $backuppath + $backupfiles #full path to source files (SQL Server's files)

$outputfile = $dbdirectory + "_backup_" + $yz + ".7z" #build the 7z filename (7zip file)

cd "$dbzdestination$dbdirectory" #change the path to the backup destination

# command line switch descriptions (see http://dotnetperls.com/7-zip-examples)

# -t7z = 7zip type

# -v10m = split to 10 meg files

# -mmt = multithreading

"7zip a -t7z -v10m -mmt=off $outputfile $input" # echo out some progress

sevenZip a -t7z -v10m -mmt=off "$outputfile" "$input" # 7zip them up

}

# we're all done, let's cleanup

Remove-Item Alias:sevenZip

Comments Closed.