PowerShell – Backup SQL Server System Databases

I have been lately writing on powershell and didn’t know there are many more scenarios that can be enabled. I was traveling for a consulting assignment over the weekend for a customer location. Though this exercise was around performance tuning their systems, it was amazing to see that they had some of the basics not done either. In this blog we will be discussing about how to backup SQL Server System Databases.

I was looking at the @@VERSION and found this was not updated for the RTM version installed. This was a critical teir-1 system which was catering to heavy transactions. When I asked the question to the DBA, he said he was only a DBA for SQL Server and he expected to take backups every single day. This whole conversation went down south when I had to talk to them about the importance of various backups, keeping systems up-to-date with service packs.

I had to also guide him in this exercise and his first question was, what if the system goes unresponsive because of applying the service packs. That was a good question. He wanted to take a backup of the system databases before the update.

Yes, I helped him with a process of applying this on development environment and use it as a sanity testing for a couple of weeks before applying into production. I shared a script which I felt is worth sharing here.

$SQLInstance = "localhost" 
$BackupFolder = "C:SystemData" 
$tStamp = Get-Date -format yyyyMMddHHmm 

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 

$srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLInstance 
$dbs = New-Object Microsoft.SqlServer.Management.Smo.Database 
$dbs = $srv.Databases 

foreach ($Database in $dbs | where {$_.name -ne "tempdb"} | where {$_.IsSystemObject -eq $True}) 
{ 
$Database.name 
$bk = New-Object ("Microsoft.SqlServer.Management.Smo.Backup") 
$bk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database 
$bk.BackupSetName = $Database.Name + "_backup_" + $tStamp 
$bk.Database = $Database.Name 
$bk.CompressionOption = 1 
$bk.MediaDescription = "Disk" 
$bk.Devices.AddDevice($BackupFolder + "" + $Database.Name + "_" + $tStamp + ".bak", "File") 

TRY 
{$bk.SqlBackup($srv)} 
CATCH  
{$Database.Name + " backup failed." 
$_.Exception.Message} 
} 

This is a modification of an earlier script I used in the blog. The output of the same would look like:

I have made sure to eliminate tempDB from the script else there would be an error while executing the PowerShell. I have seen people not happy when they see an error. If we don’t do the elimination the powershell will look like:

I am sure you will go ahead and change the script to your desired based on your environment needs. I would be more than happy to understand each of these scenarios. Please share the same via comments to help others also learn from you.

Reference: Pinal Dave (https://blog.sqlauthority.com)