Automation with PowerShell

Early in my first year as a DBA, I realized that PowerShell is a valuable asset and immediately began learning and scripting. Over the course of the past year, I have automated many of the tasks that weren’t difficult but were time consuming, such as the quarterly archiving of our production instances. One of the functions used in this script is a graphical date picker that was written by the Scripting Guys and can be found on Microsoft TechNet here. I decided to use the graphical date picker to standardize the date entry and to ensure that the full day was used when looking for the backups. I also call an email function to shown below.

    function EmailResults ([String]$msg)
   {
#Create the message
$emailFrom = “email@server.com”
$emailTo = “email@server.com”
$subject = “Your Subject”
$message = New-Object Net.Mail.Mailmessage
$message.from = $emailFrom
$message.to.Add($emailTo)
$message.Subject = $subject
$message.body = $msg
#Send the email
$smtpServer = “your smtp server”
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
    }
We will be using 7-Zip to compress the archived backups, so it is important to make sure that the software is  installed. Then, we must determine the path of the executable and finally create an alias for easy reference.
#Test for 7-Zip installation 64 or 32 bit version and assign alias for easy reference
If(-not(Test-Path “$env:ProgramFiles\7-Zip\7z.exe”))
{
If(-not(Test-Path “$env:ProgramFiles (x86)\7-Zip\7z.exe”))
{
Write-Warning “$env:ProgramFiles\7-Zip\7z.exe needed”
}
Else
{
Set-Alias sz “$env:ProgramFiles (x86)\7-Zip\7z.exe”
}
}
Else
{
Set-Alias sz “$env:ProgramFiles\7-Zip\7z.exe”
}
Now, let’s move on to the meat and potatoes of the script. Here we initialize all the needed variables, such as $archAfter, which calls the Pick-Date function to allow the user to graphically choose the date to archive. $archBefore is used to create a twenty-four hour window to search for backups run during that time period. The script block defines the work that will take place on the remote machine. In order to find the backups to archive, we recursively search through the source directory for files created during the specified twenty-four hour period, excluding transaction logs and directories. (The exclusion of transaction logs may not be the best fit if you are using the archive for disaster recovery.)
#Variable initialization
$archAfter = Pick-Date
$archBefore = $archAfter.AddDays(1)
$destination = $null
$source = $null
$servers = Get-Content “C:\ServerList.txt”
$finalRestingPlace = “\\local\share”
#This is where the work takes place
$ScriptBlock =
{
If (-not (Test-Path $destination))
{
New-Item $destination -type directory
}
Get-ChildItem $source -Recurse -Exclude *.trn |
Where-Object {$_.Mode -ne “d—-” -and $_.LastWriteTime -gt $archAfter -and 
                                                                $_.LastWriteTime -lt $archBefore} |
ForEach
{
Copy-Item $_.FullName -Destination $destination
}
Start-Sleep 2
Start-Process -FilePath sz -ArgumentList “a”, “-t7z”, “-mx9”, “`”$zipfile`””, “`”$destination`””
Copy-Item $zipfile -Destination $finalRestingPlace
}
In the last part of the script, we loop through a list of servers to archive and assign the source, destination, and zip file paths dynamically. Finally, we execute the script block on each of the remote machines.
#Lets do this
ForEach ($server in $servers)
{
#ServerA and ServerB are in one geographic location
If ($server -eq “ServerA” -or $server -eq “ServerB”)
{
$source = “\\networkpath\$($server)”
$destination = “\\networkpath\archivedestination\$($server)”
$zipfile = “\\networkpath\zipdestination\$($server).7z”
}
#All other servers are in another geographic location
Else
{
$source = “\\networkpath\$($server)”
$destination = “\\networkpath\archivedestination\$($server)”
$zipfile = “\\networkpath\zipdestination\$($server).7z”
}
#Run the commands on the the remote server
Invoke-Command -ComputerName $server -ScriptBlock $ScriptBlock
}
The full script can be downloaded here.

If you’re new to PowerShell or just ready to get scripting, I recommend getting a good scripting environment. There are a lot to choose from, such as PowerShell ISE that comes with PowerShell, and many come with administration tools as well (e.g., PowerShell Plus or PowerGUI). The administration tools are incredibly helpful in administrating your environment regardless of your role.

Advertisements