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}) 
$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") 
{$Database.Name + " backup failed." 

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

PowerShell - Backup SQL Server System Databases system-database-01

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:

PowerShell - Backup SQL Server System Databases system-database-02

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)

Powershell, SQL Backup, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Change Color for System Object Name in Text Editor (SSMS 2016)
Next Post
SQL SERVER – Understanding FAILOVERCLUSTERROLLOWNERSHIP with SQL Server Cluster Rolling Upgrade

Related Posts

2 Comments. Leave new

  • Hi Pinal,
    I am keen to learn powershell for SQLSERVER DBA, any suggestion where to start and how to go about it.
    Many Thanks,Rish

  • How would you pass a username and password to this script


Leave a Reply