PowerShell Sample - Database Backup


The original of this script was used in an automated Jenkins project to backup specified databases in specified environment before executing a database deployment.


 <#
-- =============================================
-- Author: Tracy Hartzler
-- Create date: 7/25/2019
-- Last Modified: 06/26/2020
      -- Refactored for portfolio article on my website
-- Description:
     -- The original of this script was used in an automated Jenkins project to backup specified databases in specified environment before executing a database deployment.
     -- Environment names and database instances have been changed to protect former employer
     -- Have substituted stub values for
          -- $Environment
          -- -ComputerName
          -- $backupName
          -- -ServerInstance
          -- -Database
-- =============================================
#>
[CmdletBinding()]
param(
          [String][parameter(Mandatory=$true)] [ValidateSet("TEST2012R2-1")] $Environment
)
$pstzone = [System.TimeZoneInfo]::FindSystemTimeZoneById("Pacific Standard Time")
$psttime = [System.TimeZoneInfo]::ConvertTimeFromUtc((Get-Date).ToUniversalTime(), $pstzone)
$startTime = $psttime.ToString("yyyy_MM_dd_HH:mm")
Write-Host "--Backups started at " $startTime

if($environment -eq "TEST2012R2-1"){
     Invoke-Command -ComputerName TEST2012R2-1 -Credential $Credential -ScriptBlock {
          try{
               # Convert server's UTC time to Pacific Standard Time and then format string for $filedate
               $pstzone = [System.TimeZoneInfo]::FindSystemTimeZoneById("Pacific Standard Time")
               $psttime = [System.TimeZoneInfo]::ConvertTimeFromUtc((Get-Date).ToUniversalTime(), $pstzone)
               $filedate = $psttime.ToString("yyyyMMdd_HHmm")

               $serverName = (Get-WmiObject -Class Win32_ComputerSystem -Property Name).Name
               $backupName = "sample_DB_1_PreDeploy_$filedate.bak"

               Write-Host -ForeGround Yellow "----Backing up sample_DB_1 as \C:\Backups\Deployment\$backupName"
               Backup-SqlDatabase -ServerInstance "TEST2012R2-1" -Database "sample_DB_1" -CompressionOption On -BackupFile "C:\Backups\Deployment\$backupName"
               Write-Host -ForeGround Green "----Completed backup of $backupName"
          }
          catch{
                    $serverName = (Get-WmiObject -Class Win32_ComputerSystem -Property Name).Name
                    Write-Host -ForeGround Red "Was trying to back up sample_DB_1 database $backupName on server "$serverName
          }
     }
}
else{
          Write-Host -ForeGround Red "Not a valid environment to backup database in."
}
$pstzone = [System.TimeZoneInfo]::FindSystemTimeZoneById("Pacific Standard Time")
$psttime = [System.TimeZoneInfo]::ConvertTimeFromUtc((Get-Date).ToUniversalTime(), $pstzone)
$endTime = $psttime.ToString("yyyy_MM_dd_HH:mm")
Write-Host "--Backup completed at " $endTime


File name: backup_sample_DB_1_PreDeployment_TEST20212R2-1.ps1