PowerShell Script – Backup Every Database In SQL Server

Using scripts can be a powerful way to automate things that a GUI will fail in many cases. I was working with a client who wanted to write a PowerShell script that would help him automate his backups. This is a pretty simple requirement that can be easily achieved using SMO as shown below in my scripts. I have been using these scripts for a number of years at various places. Let us see how we can backup every database in SQL Server.

I really was not having anything in mind, but went the extra mile to understand why the DBA was asking such script as he had maintenance plans already in place. The DBA told me this script was useful because he would use it in places when he needs to do some sort of upgrade to the SQL instances and before applying the hotfixes or service packs, he would take an ethic out of the cycle backup of his databases to make sure he has a fall back option in case the installations failed because of any reason. I personally felt this was a nice use case for such scripts.

In the below script, I have used the default instance running on my machine and feel free to change the same accordingly. Also create a folder where the backups need to land. Make sure you have the required space on the drive where this backup will be taken.

$SQLInstance = "." 
$BackupFolder = "C:\Data\" 

$timeStamp = Get-Date -format yyyy_MM_dd_HHmmss 

[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) 
{ 
 $Database.name 
 $bk = New-Object ("Microsoft.SqlServer.Management.Smo.Backup") 
 $bk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database 
 $bk.BackupSetName = $Database.Name + "_backup_" + $timeStamp 
 $bk.Database = $Database.Name 
 $bk.CompressionOption = 1 
 $bk.MediaDescription = "Disk" 
 $bk.Devices.AddDevice($BackupFolder + "\" + $Database.Name + "_" + $timeStamp + ".bak", "File") 
 
TRY {
 $bk.SqlBackup($srv)
 } 
CATCH 
 {
 $Database.Name + " backup failed." 
 $_.Exception.Message
 } 
} 

When I ran this on the PowerShell ISE window, I got an output with the list of databases.

You can see that the tempDB database will fail with an error, but that is absolutely fine. In this case, we have not done any filter on our list of databases. We will also backup the system databases as shown in the figure below.

With the powershell script displayed in this blog post we can backup every database in SQL Server.

What automation have you done in your environments for this requirement? Do you have something similar running on your servers? Do share the scripts and spread the happiness along. Let us learn together more.

Further reading: Backing Up SQL Server Databases is Easier in PowerShell than T-SQL by Aaron Nelson

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