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