My first powershell script – move and delete SQL backup files

Remember my first Sony? I had a walkman and it was the first electronic device I took apart and put back together when I was around 11 years old. After that it was held together by some masking tape but I managed to at least get it back together and it could still play tapes. Well, I managed to create my first powershell script by deconstructing other scripts I found and doing some basic tutorials. My script just moves SQL bak files with “full” in the name from a backup folder to an archive folder then cleans up the archive folder. For testing I have it in that order, but in production you’ll want to clean the archive folder first.

The first thing I needed to do was move some files based on date. Moving files is really easy, moving them based on date not so much. But I managed to find a script that I deconstructed and tailored to what I needed. After getting the files to move I realized it would move ALL files, not just my full backups. It took me a couple hours to find, but it’s actually just a simple -filter switch. The final step was to make sure the archive folder only had one set of backups in it. That one was a lot simpler and just involved one line, which I added to so I could create some variables. Below is the final product.

<#
Title:?? ??? ?Move and Delete SQL BAK files.
Description:?? ?Moves files that are more than 15 days old from SQL backup folder to archive folder.
Author: ?? ?Mike Kauspedas
Version: ?? ?1.0
Date Created:?? ?10/25/12
Date Modified:?? ?10/25/12
#>
<# VARIABLES #>
$BackupPath = “E:\SQL1\Backups” ?? ??? ??? ??? ?#Path of the folder original backups go into.
$ArchivePath = “E:\SQL1\Archive” ?? ??? ??? ??? ?#Path to archive folder that you will be moving to and deleting from.
$DeleteAge = “-19” ?? ??? ??? ??? ??? ??? ?#The amount of days in age you want to delete files. IE -2 would be 2 day old files.
$ArchiveAge = “-15”?? ??? ??? ??? ??? ??? ?#Age in days to move files
$CurrentDate = Get-Date ?? ??? ??? ??? ??? ?#Don’t change.
$DatetoDelete = $CurrentDate.AddDays($DeleteAge)?? ??? ?#Don’t change.
$files = Get-ChildItem $BackupPath -recurse -Filter “*FULL*”?? ?#Don’t change.
foreach ($item in $files)
{
if ($item.CreationTime -lt ($(Get-Date).AddDays($ArchiveAge)))
{
if ($item.attributes -ne “Directory”)
{
move-Item $item.FullName $ArchivePath -force
}
}
}
#Time to delete files!
Get-ChildItem $ArchivePath -Recurse -include *.txt, *.bak| Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item

1 Comment

 Add your comment
  1. Thanks Mike. This is just what I needed. I don’t know why I’m being such a wimp with PowerShell, but I am having a hard time just getting started with it. If I follow enough examples like yours, I may actually break out of my mental block.

    Cheers!

Leave a Comment

Your email address will not be published.