Powershell Script to Backup Database(s) (Pass database name(s) as array parameter)

#Get values from script
param([parameter(Mandatory=$true)][string[]]$myDBs = $(Throw "Database name is required, please provide a name.")) 
# Test to see if the SQLPS module is loaded, and if not, load it
if (-not(Get-Module -name 'SQLPS')) {
  if (Get-Module -ListAvailable | Where-Object {$_.Name -eq 'SQLPS' }) {
    Push-Location # The SQLPS module load changes location to the Provider, so save the current location
   Import-Module -Name 'SQLPS' -DisableNameChecking  

$dt = Get-Date -Format yyyyMMddHHmmss
$svrname = "MC047012\MSSQL2014"

#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 
$svr.ConnectionContext.StatementTimeout = 0

foreach ( $db in $myDBs ) { 


Backup-SqlDatabase -InputObject $svr -Database $db -BackupFile "$($workdir)\$($db)_db_$($dt).bak" -CompressionOption On -CopyOnly

Write-Host "Database backup finished : " $db
Catch [Exception]
 write-host "Database - $($dbname ): "$_.Exception.Message


Example to call above script:

powershell -noexit 'C:\temp\call_backup_cmdlet_with_parameter.ps1' "mydb1,mydb2,mydb3"


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