Setting Up Automatic Scheduled Sql Server Database Backups using PowerShell script: Zipping the database backups & Backup auto cleanup / Retention policy enforcement using PowerShell – Part II

In the Part I, I have explained how we can set Automatic Scheduled Sql Server Database Backups using PowerShell script . In this post, we will see how we can enhance that backup process (explained in Part I) to reduce disk requirements for storing the backups by zipping the backup files that will enable us to keep more days of backup on the device & to automate the backup cleanup/retention policy i.e. number for days we want to keep a backup (backups older that the retention period will be deleted).

Note: For Part I of this post refer Automatic Scheduled Sql Server Database Backups using PowerShell script

Step 1: Zipping the database backups using PowerShell

Method 1: Using Compress-Archive cmdlet (limitation maximum file size is 2 GB)

The below PowerShell script will:

1. Check “D:\SqlBackups\” folder
2. Get all files that has .bak extensions

$backupPath = "D:\SqlBackups\"
$fileFilterPath = $backupPath + "*.bak"
$files = Get-ChildItem -Path $fileFilterPath -Recurse -File

3. Zip & Delete all filtered files

ForEach ($file in $files) {
$path = $file.fullname + ".zip"
$file | Compress-Archive -DestinationPath $path -Force
$file | Remove-Item
}

Full  PowerShell script for zipping backup files using Compress-Archive cmdlet:

$backupPath = "D:\SqlBackups\"
$fileFilterPath = $backupPath + "*.bak"
$files = Get-ChildItem -Path $fileFilterPath -Recurse -File

ForEach ($file in $files) {
  $path = $file.fullname + ".zip"
  $file | Compress-Archive -DestinationPath $path -Force
  $file | Remove-Item
}

Note: The Compress-Archive cmdlet uses the Microsoft .NET API System.IO.Compression.ZipArchive to compress files. The maximum file size is 2 GB because there’s a limitation of the underlying API.

Method 2: Using 7Zip4Powershell Powershell module (for file size > 2 GB)

Install 7Zip4Powershell  PowerShell Module:

Full  PowerShell script for zipping backup files using 7Zip4Powershell module:

$backupPath = "D:\SqlBackups\"
$fileFilterPath = $backupPath + "*.bak"
$files = Get-ChildItem -Path $fileFilterPath -Recurse -File

ForEach ($file in $files) {
  $path = $file.fullname + ".zip"
    Compress-7Zip -Path $file -Format Zip -ArchiveFileName $path
  $file | Remove-Item
}

Step 2: Backup auto cleanup / Retention policy enforcement using PowerShell

The below PowerShell script will:

1. Check “D:\SqlBackups\” folder
2. Get all files that are older than 15 days ($backupRetentionDays can be set as per requirement. In below script it is set to 15 days)

$backupRetentionDays = 15
$backupPath = "D:\SqlBackups\"
$lastWrite = (get-date).AddDays(-$backupRetentionDays)
$fileFilterPath = $backupPath
$files = Get-ChildItem -Path $fileFilterPath -Recurse -File | Where-Object {$_.LastWriteTime -le $lastWrite}

3. Delete all filtered files

ForEach ($file in $files) {
$file | Remove-Item
}

Full PowerShell script for Backup auto cleanup / Retention policy enforcement:

$backupRetentionDays = 15
$backupPath = "D:\SqlBackups\"
$lastWrite = (get-date).AddDays(-$backupRetentionDays)
$fileFilterPath = $backupPath
$files = Get-ChildItem -Path $fileFilterPath -Recurse -File | Where-Object {$_.LastWriteTime -le $lastWrite}

ForEach ($file in $files) {
  $file | Remove-Item
}

Step 3: Full updated PowellShell script for backing up SQL Server database objects using Backup-SqlDatabase cmdlet of Sql Server PowerShell module, Zipping Code using Compress-Archive cmdlet or 7Zip4Powershell module & Backup auto cleanup / Retention policy enforcement

Sample Script (DatabaseBackup.ps1): In this script, we are creating backups of two databases (DataBase1 & DataBase2) & storing the backup files in “D:\SqlBackups\” location. We have PowerShell’s Get-Date cmdlet to get a DateTime string that is used for database backup file naming.

$backupPath = "D:\SqlBackups\"
$datetime = (Get-Date).ToString('MM-dd-yyyy-hh-mm-ss-tt');
$database1 = $backupPath + "DataBase1_" + $datetime + ".bak";
$database2 = $backupPath + "DataBase2_" + $datetime + ".bak";

Backup-SqlDatabase -ServerInstance KAPIL-KHANDELWA -Database DataBase1 -BackupFile $database1;
Backup-SqlDatabase -ServerInstance KAPIL-KHANDELWA -Database DataBase2 -BackupFile $database2;

$fileFilterPath = $backupPath + "*.bak"
$files = Get-ChildItem -Path $fileFilterPath -Recurse -File

#Use the below code if file size < 2 GB (zipping backup files using Compress-Archive cmdlet)
ForEach ($file in $files) {
  $path = $file.fullname + ".zip"
    $file | Compress-Archive -DestinationPath $path -Force
  $file | Remove-Item
}

#Use the below code if file size > 2 GB (zipping backup files using 7Zip4Powershell module https://www.powershellgallery.com/packages/7Zip4Powershell/2.0.0)
<# ForEach ($file in $files) {
  $path = $file.fullname + ".zip"
    Compress-7Zip -Path $file -Format Zip -ArchiveFileName $path
  $file | Remove-Item
} #>

$lastWrite = (get-date).AddDays(-15)
$fileFilterPath = $backupPath
$files = Get-ChildItem -Path $fileFilterPath -Recurse -File | Where-Object {$_.LastWriteTime -le $lastWrite}

ForEach ($file in $files) {
  $file | Remove-Item
}

That’s it…!!!