PowerShell – Backup Specific group of SQL Databases

<#
.SYNOPSIS

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

.DESCRIPTION

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”

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

.EXAMPLE

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

.LINK
#>

Function Invoke_SQLDBBackup 
{ 
 
param ([Parameter(Mandatory=$true,Position=0)][String]$SQLServer,  
[Parameter(Mandatory=$true,Position=1)][String]$BackupDirectory, 
[Parameter(Mandatory=$true,Position=2)][String]$dbList) 
 
[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") 
$DatabaseBackup.SqlBackup($Server) 
} 
} 
} 
}

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

 

BackupSpecificDatabases

 

 

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek 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