In my previous post we talked about setting up your local environment to use SQL Server and PowerShell together, so now let’s put this into practice. The trick is finding something worth utilizing PowerShell for within SQL Server. And while the last thing you want to do is make something that can be done in T-SQL alone more complex than it has to be, sometimes there’s a good reason. So before we get to the code, allow me to share a quick story how this project came to be.
At the company where I work, the decision was made to eliminate tape backup drives. Which is fine, and is a trend most IT shops are embracing now that bandwidth and co-location has made off-site replication or storage more attractive. However, in doing so, that compromised our current SQL backup and retention strategy: each night, backups were written from disk to tape, and the tapes changed daily. This meant our last backup was written to a tape and shipped offsite before the next backup. Tapes were also maintained for 30 days before they were rotated back to us for re-use, so we had a comfortable cushion of 30 days worth of backups of the unimaginable happened. Of course, this scenario doesn’t account for a total server loss, or the time it might take for a tape to be retrieved, but it was what the business wanted, so we ran with it.
But with no tape backups, our process, if left unchanged, would just be overwriting each night’s backups with the newest one. This might have been fine if we needed to go back up to 24 hours, and served our purposes for non-production database refreshes, but we were exposed if something happened to the data and it wasn’t caught before we overwrote our backups. Not a good thing.
We worked with the system and storage engineers on how and where we’d be storing our new backups. The DBAs were given a long-term storage device that had oodles of storage, and the vendor also had the capability to replicate the data stored on it to a remote data center with another of the same type of device.
The next problem became, how can we back-up multiple day’s worth of data to different file names each day? And what about retention? Previously we relied on backup devices inside SQL server to do the trick, but I went… a slightly different route.
Since I didn’t want to try to automate the deletion and recreation of backup devices each night, I wrote a PowerShell script to handle my SQL backups. The requirements were as follows:
And once I got started, there were a couple little “nice to haves” that became features too:
For examples on how to execute it, and some explanation on what it does, keep reading below. You should also bear in mind that while this code works, it’s very much a work in progress, and I plan on maintaining it either here or GitHub. There’s also some undocumented features that I am experimenting with.
And, it bears repeating but just in case this is the first time you’ve ever copied and pasted code from the internet…
DISCLAIMER: Okay look, you probably already know this but this code you’re about to use was written by me. It works for me as described, but computers are hard and sometimes things don’t work right. Therefore, I’d like to casually remind you that you probably shouldn’t load this script up in your production environment all willy-nilly. As with any new software or functionality, test it first on your local instance or in a development environment.
<#
.SYNOPSIS
Starts a BACKUP DATABASE/LOG session on a specified server for all databases on a given SQL Server
.DESCRIPTION
This script will attempt to automate the backup of databases on a SQL Server, while also managing the directory structure of where you backup to. The script can either backup all the databases, or some of the databases through the use of the EXCLUDEDATABASES parameter and/or SYSTEMOBJECTS parameter.
.PARAMETER servername
The hostname or FQDN of the server you want the backup to run on. This is a required parameter.
.PARAMETER instancename
The instance name of the SQL server you want the backup to run on, defaults to "DEFAULT" for non-named instances. This is optional parameter.
.PARAMETER databasename
The name of the database you want to back up. This is an optioanl parameter. If not supplied, all non-system databases will be backed up.
.PARAMETER excludedatabases
A comma-seperated list of databases you DO NOT want to be backed up. This is an optional parameter.
.PARAMETER backuptype
Options: Full, Log, or Differential
The type of backup to be performed. This is a required parameter.
.PARAMETER backupLocation
The path where the database backup will be stored. Can be a local path or a UNC path. This is a required parameter.
.PARAMETER backupFileName
The name of the backup file. This is an optional parameter, and it CANNOT be used unless the databasename parameter is provided. If not supplied, the backups will be named as follows: DatabaseName_BackupType_YYYYMMDD_HHMMSS.extension
.PARAMETER copyOnly
Sets the COPY_ONLY flag during the backup operation, so differential chains do not become broken. This is an optional parameter.
.PARAMETER splitFiles
Specifices if the backup should be "split" (spanned) across seperate files. This is an optional parameter. This "may" increase the speed of the backup operation. If used, a folder will be created in the supplied backuplocation parameter named "BackupCollection_YYYYMMDD_HHMMSS" and each file will be named as follows: DatabaseName_BackupType_YYYYMMDD_HHMMSS_FileNumber.extension
.PARAMETER systemObjects
If supplied, master, msdb, and model will be backed up as part of your backup operation. This is an optional paramater. TempDB and distribution (if it exists) will not be backed up.
.PARAMETER seperateDirectories
This parameter will handle creating seperate directories per database and per backup type at the supplied backuplocation parameter. For example, if your database name is "somedatabaes" and your backup location is C:\Backups and you use this parameter, the following folder structure will be used: C:\Backups\SomeDatabase\Full or C:\Backups\SomeDatabase\Logs depending on the type of backup performed. This is an optional parameter.
Developer note: If you plan on using scripted restores, this parameter will make automating your restores 1000x easier!
.PARAMETER certificateLocation
This parameter will specify where your databases' certificates will be backed up to. This is an optional parameter.
.PARAMETER privateKeyPassword
This paramater specifices the password you use to encrypt your database certificates. If not provided, one will be generated for you. This is an optional parameter.
.PARAMETER privateKeyLocation
This parameter will specific where your database private keys will be written to a plain text file. This parameter is required if you are backing up your certificates!
.PARAMETER sendKeyTo
.
.PARAMETER smtpServer
.
.PARAMETER ssisdb
If provided this flag will tell the script to also backup your SSISDB. This also requires you to specify the -ssisdbPassword paramater as the database certificate will be backed up.
.PARAMETER ssisdbKeyPassword
.EXAMPLE
.
.OUTPUTS
.
.NOTES
.
.CHANGELOG
.
#>
param(
[Parameter(Mandatory=$true)] [string] $servername,
[Parameter(Mandatory=$false)] [string] $instanceName = "DEFAULT",
[Parameter(Mandatory=$false)] [string] $databaseName,
[Parameter(Mandatory=$false)] [string[]] $excludeDatabases,
[Parameter(Mandatory=$true)] [validateset('Full','Differential','Log')] [string] $backupType,
[Parameter(Mandatory=$true)] [string] $backupLocation,
[Parameter(Mandatory=$false)] [string] $backupFileName,
[Parameter(Mandatory=$false)] [switch] $copyOnly,
[Parameter(Mandatory=$false)] [int] $splitFiles = 1,
[Parameter(Mandatory=$false)] [switch] $systemObjects,
[Parameter(Mandatory=$false)] [switch] $seperateDirectories,
[Parameter(Mandatory=$false)] [string] $certificateLocation,
[Parameter(Mandatory=$false)] [string] $privateKeyPassword,
[Parameter(Mandatory=$false)] [string] $privateKeyLocation,
[Parameter(Mandatory=$false)] [string] $sendKeyTo,
[Parameter(Mandatory=$false)] [string] $smtpServer,
[Parameter(Mandatory=$false)] [switch] $ssisdb,
[Parameter(Mandatory=$false)] [string] $ssisdbKeyPassword
)
function Make-StrongPassword([int]$length, [int]$nonalphanumericchars)
{
if ($length -lt $nonalphanumericchars) { $nonalphanumericchars = $length }
[Reflection.Assembly]::LoadWithPartialName("System.Web") | Out-Null
return [System.Web.Security.Membership]::GeneratePassword($length,$nonalphanumericchars)
}
$whoami = ([adsisearcher]"(samaccountname=$env:USERNAME)").FindOne().Properties.mail
Write-Verbose "This script is running as $whoami"
if ($certificateLocation -and !$privateKeyLocation)
{
Throw "If you're backing up a certificate, you should be backing up with an encrypted private key."
}
if ($certificateLocation -and !$privateKeyPassword)
{
$privateKeyPassword = Make-StrongPassword 20 10
Write-Warning "User did not provide an encryption password, using automatically generated password..."
}
$keyAction = $null
if ($sendKeyTo)
{
if (Test-Path $sendKeyTo)
{
Write-Verbose "Private key passwords will be written to a file at the following location: $privateKeyLocation"
$keyAction = "file"
} else {
try
{
$toAddress = New-Object Net.Mail.MailAddress($sendKeyTo)
Write-Verbose "Private key passwords will be emailed to: $privateKeyLocation"
$keyAction = "email"
}
catch
{
Throw "Unable to resolve email address or file location for private key passwords. Please check your 'privateKyLocation' parameter and try again."
}
}
if ($keyAction -eq "email" -and !$smtpServer)
{
throw "You have requested private keys be emailed to $sendKeyTo but you didn't provide an smtp server to send the email. Please add -smtpserver to your command and try again."
}
}
if ((Test-Path $backupLocation) -eq $false)
{
Throw "Unable to resolve backup location. Stopping."
}
if (!$databaseName -and $backupFileName)
{
Throw "You can't use the backup file name parameter without specifying a database."
}
$sqlPath = "SQLSERVER:\SQL\" + $servername + "\" + $instanceName
if ($systemObjects)
{
$dbs = Get-ChildItem ($sqlPath + "\Databases") -Force | Where-Object {$_.Name -ne "tempdb" -and $_.Name -ne "distribution"}
} else {
$dbs = Get-ChildItem ($sqlPath + "\Databases")
}
if (!$ssisdb)
{
$dbs = $dbs | Where-Object {$_.Name -ne "SSISDB"}
}
if ($excludeDatabases)
{
foreach ($e in $excludeDatabases)
{
$dbs = $dbs | Where-Object {$_.Name -ne $e}
}
}
if ($databaseName)
{
$dbs = $dbs | Where-Object {$_.Name -eq $databaseName}
}
foreach ($d in $dbs)
{
$d.Refresh()
}
if ($backupType -eq "Log")
{
$dbs = $dbs | Where-Object {$_.RecoveryModel -eq "Full"}
}
if ($databaseName)
{
$dbs = $dbs | Where-Object {$_.Name -eq $databaseName}
}
if ($dbs.Length -eq 0)
{
throw "Nothing to backup!"
}
foreach ($d in $dbs)
{
$timestamp = Get-Date -UFormat "%Y%m%d_%H%M%S"
$currentDBName = $d.Name
$fullBackupLocation = $backupLocation
if ($seperateDirectories)
{
$fullBackupLocation = $backupLocation + "\" + $servername + "\" + $currentDBName + "\" + $backupType
if ((Test-Path $fullBackupLocation) -eq $false) {New-Item -ItemType Directory -Force -Path $fullBackupLocation | Out-Null }
}
Write-Verbose "Creating $backupType backup of database $currentDBName..."
if ($backupType -eq "Full")
{
if ($d.DatabaseEncryptionKey.EncryptionState -eq "Encrypted")
{
Write-Verbose "Database is encrypted!"
if ($certificateLocation)
{
$certificateName = $d.DatabaseEncryptionKey.EncryptorName
$certificateLocation = $certificateLocation
$privateKeyLocation = $privateKeyLocation
if ($seperateDirectories)
{
$certificateLocation = $certificateLocation + "\" + $servername + "\" + $currentDBName + "\Certificates"
$privateKeyLocation = $privateKeyLocation + "\" + $servername + "\" + $currentDBName + "\Keys"
}
if ((Test-Path $certificateLocation) -eq $false) {New-Item -ItemType Directory -Force -Path $certificateLocation | Out-Null }
if ((Test-Path $privateKeyLocation) -eq $false) {New-Item -ItemType Directory -Force -Path $privateKeyLocation | Out-Null }
Write-Verbose "Backing up database certificate $certificateName to $certificateLocation"
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $servername
$srv.Databases["master"].ExecuteNonQuery("backup certificate " + $certificateName + " to file = '" + ($certificateLocation + "\" + $certificateName + "_" + $timestamp + ".cert") + "' WITH PRIVATE KEY (FILE = '" + ($privateKeyLocation + "\" + $certificateName + "_" + $currentDBName + "_" + $timestamp + ".key") + "', ENCRYPTION BY PASSWORD = '" + $privateKeyPassword + "')")
if ($keyAction -eq "email")
{
send-mailmessage -to $sendKeyTo -body "Hello, this is an automated email from $servername/$instancename. The $currentDBName database backup is encrypted, and requires a certifcate with private key to restore. The certifcate was backed up to $certificateLocation and the private key was saved to $privateKeyLocation. The private key was encrypted with the following password: $privateKeyPassword" -From $whoami -SmtpServer $smtpServer -Subject "Backup of server certificate and key for encrypted database $currentDBName from $servername/$instancename"
}
if ($keyAction -eq "file")
{
Write-Verbose "Encrypting private key with the following password: $privateKeyPassword"
"This is an automated message from $servername/$instancename. The $currentDBName database backup is encrypted, and requires a certifcate with private key to restore. The certifcate was backed up to $certificateLocation and the private key was saved to $privateKeyLocation. The private key was encrypted with the following password: $privateKeyPassword" | Out-File -FilePath ($sendKeyTo + "\" + $currentDBName + "_" + $timestamp + "_PrivateKey.txt")
}
} else {
$certLastBackup = (Get-ChildItem -Path ($sqlPath + "\databases\master\certificates") | Where-Object {$_.Name -eq $d.DatabaseEncryptionKey.EncryptorName}).LastBackupDate
Write-Warning "This database is encrypted and you didn't specify a certifcate path!"
Write-Warning "I strongly, STRONGLY suggest you take care of that..."
}
}
if ($d.Name -eq "SSISDB")
{
Write-Verbose "Backing up Integration Services Catalog: SSISDB..."
Write-Verbose "... but is it really an integration services catalog?"
$ssisConnectionString = "Data Source=$servername;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
try
{
$ssis = New-Object "Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices" $sqlConnection
if ($ssis.catalogs[$d.Name].name -eq $d.name)
{
Write-Verbose "Yup, looks like an Integration Services catalog to me..."
$ssisBackupLocation = $backupLocation
if ($seperateDirectories)
{
$ssisBackupLocation = $backupLocation + "\" + $servername + "\" + $currentDBName + "\IntegrationServices"
}
if ((Test-Path $ssisBackupLocation ) -eq $false) {New-Item -ItemType Directory -Force -Path $ssisBackupLocation | Out-Null }
Write-Verbose "Scripting ##MS_SSISServerCleanupJobLogin## user to..."
(Get-ChildItem -Path ($sqlPath + "\Logins") | Where-Object {$_.Name -eq "##MS_SSISServerCleanupJobLogin##"}).Script() | Out-File -FilePath ($ssisBackupLocation + "\ssisobjects_" + $timestamp + ".sql") -append
Write-Verbose "Scripting dbo.sp_ssis_startup stored procedure to..."
(Get-ChildItem -Path ($sqlPath + "\Databases\Master\StoredProcedures") | Where-Object {$_.Name -eq "sp_ssis_startup" -and $_.Schema -eq "dbo"}).Script() | Out-File -FilePath ($ssisBackupLocation + "\ssisobjects_" + $timestamp + ".sql") -append
Write-Verbose "Backing up master database key to..."
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $servername
$srv.Databases[$d.Name].ExecuteNonQuery("backup master key to file = '" + ($ssisBackupLocation + "\databasemasterkey_" + $timestamp + ".key") + "' encryption by password = '" + $ssisdbKeyPassword + "'")
Write-Verbose "Saving password to ... because you'll probably forget it otherwise"
$ssisdbKeyPassword | Out-File -FilePath ($ssisBackupLocation + "\databasemasterkey_password_" + $timestamp + ".txt") -append
} else {
Write-Warning "Could not find suitably-named Integration Services catalog to back up... skipping this step"
}
} catch {write-host "can't connect to ssis catalog"}
}
$fullbackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
if ($backupFileName)
{
$fullbackup.Devices.AddDevice(($fullBackupLocation + "\" + $backupFileName), "File")
}
else
{
if ($splitFiles -gt 1)
{
if ((Test-Path ($fullBackupLocation + "\BackupCollection_" + $timestamp)) -eq $false) {New-Item -ItemType Directory -Force -Path ($fullBackupLocation + "\BackupCollection_" + $timestamp) | Out-Null }
For ($devices = 1; $devices -le $splitFiles; $devices++)
{
$fullbackup.Devices.AddDevice(($fullBackupLocation + "\BackupCollection_" + $timestamp + "\" + $currentDBName + "_" + $backupType + "_" + $timestamp + "_" + $devices + ".bak"), "File")
}
}
else
{
$fullbackup.Devices.AddDevice(($fullBackupLocation + "\" + $currentDBName + "_" + $backupType + "_" + $timestamp + ".bak"), "File")
}
}
$parameters = @{
Path = $sqlPath
MediaDescription = ("Full Backup from " + $servername + " of " + $currentDBName + " on " + (Get-Date))
BackupDevice = $fullbackup.Devices
Database = $currentDBName
BackupAction = "Database"
CompressionOption = "On"
FormatMedia = $true
Initialize = $true
SkipTapeHeader = $true
}
if ($copyOnly)
{
$parameters.Add("CopyOnly",$true);
}
Backup-SqlDatabase @parameters
}
if ($backupType -eq "Differential")
{
Backup-SqlDatabase -Path $sqlPath -MediaDescription ("Differential Backup from " + $servername + " of " + $currentDBName + " on " + (Get-Date)) -BackupFile ($fullBackupLocation + "\" + $currentDBName + "_" + $backupType + "_" + $timestamp + ".dif") -Database $currentDBName -BackupAction Database -Incremental -CompressionOption On -FormatMedia -Initialize -SkipTapeHeader
}
if ($backupType -eq "Log")
{
Backup-SqlDatabase -Path $sqlPath -MediaDescription ("Transaction Log Backup from " + $servername + " of " + $currentDBName + " on " + (Get-Date)) -BackupFile ($fullBackupLocation + "\" + $currentDBName + "_" + $backupType + "_" + $timestamp + ".trn") -Database $currentDBName -BackupAction Log -CompressionOption On -FormatMedia -Initialize -SkipTapeHeader
}
Write-Verbose "Backup completed!"
}
Let’s say you wanted to take a full backup of all your SQL databases on one server. It’s as simple as this:
./Start-SQLBackup.ps1 -servername localhost -backuptype full -backuplocation C:\Backups -systemObjects -seperatedirectories -verbose
Simply run the script and provide the -servername, -backuptype, and -backuplocation parameters. It will handle the rest. To backup your system databases, the -systemdatabases switch will force (-force, literally) the system objects to be backed-up as well, and the -seperatedirectories switch will force a new directory for each database, and inside it will create a backup type (full, log, differential) folder and store the backups within them. The example above then will just connect to your local instance, and conduct a full backup of each database and maintain the folder structure. To get detailed status messages, this script also supports -verbose.
This script utilizes a couple key PowerShell features and modules; first and foremost, it requires the SQLPS module. And it uses the Backup-SQLDatabase cmdlet. It’s mostly straightforward, but it accepts all kinds of neat parameters, including a backup set object if you wanted to split or stripe your backups across multiple files. Otherwise, it’s just a big series of Get-ChildItem cmdlets mixed with some string concatenation and some if statements and conditional logic.
Of course, the real question is: why take something so basic as backups and use PowerShell? Well, as you’ll see in the next part when we need to restore databases, just PowerShell is good at keeping your file structures organized when you backup, you can also leverage that same functionality to automatically manage your restores!