PowerShell – Backup Specific group of SQL Databases


The purpose of the script is to initiate backup only for desired group of databases. The requirement is to refresh databases.


The advantage is that the flexibility of passing a list of specific group of databases for backup.

The backup databases has three mandatory parameters

$SQLServer – Source SQL Server where we have to initiate the backup – For Example HQDBSP18
$BackupDirectory – Backup path for the BAK files – For example – F:\PowerSQL
$dbList – List of databases separated by comma – “rtc,SafetyDB”

Author : Prashanth Jayaram
Requires: Make sure you have all the required admin access on the server


Invoke_SQLDBBackup -SQLServer HQDBSP18 -BackupDirectory f:\PowerSQL -dbList “SafetyDB,rtc,rtcab1


Function Invoke_SQLDBBackup 
param ([Parameter(Mandatory=$true,Position=0)][String]$SQLServer,  
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null 
$BackupDate = get-date -format yyyyMMdd_HHmmss 
$Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLServer 
foreach ($Database in $Server.databases) 
foreach($db in $DbList.split(",")) 
if($Database.Name -eq $db) 
$DatabaseName = $Database.Name 
$DatabaseBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup") 
$DatabaseBackup.Action = "Database" 
$DatabaseBackup.Database = $DatabaseName 
$DatabaseBackup.Devices.AddDevice($BackupDirectory + "\" + $DatabaseName + "_" + $BackupDate + ".BAK", "File") 

Invoke_SQLDBBackup -SQLServer HQDBSP18 -BackupDirectory f:\PowerSQL 
-dbList "SafetyDB,rtc,rtcab1"





About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL powershellsql@gmail.com The articles are published in: http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL and tagged , , , . Bookmark the permalink.

2 Responses to PowerShell – Backup Specific group of SQL Databases

  1. Pingback: PowerShell – SQL Database Refresh -Restore – Multiple Databases | Prashanth Jayaram

  2. Pingback: PowerShell – SQL Database Refresh -Restore – Multiple Databases - SQL Server - SQL Server - Toad World

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s