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

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites 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 )

Google+ photo

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

Connecting to %s