Description

In some situations I would like to backup a subset of all existing database of a running Microsoft SQL Server instance. E.g. if I am in a project and the database for this have a main name indicator like HR for a Human Resources, then before I deploy changes I would like to run an additional backup for all concered database.
Or, all our databases for development & testing do have the preset DEV_ and in common those never be backup, because they are copies of productive database. But before doing main changes I run a backup to reduce may neccessary recovery time in case of failure.

For this I create a PowerShell script using a Regulary Expression (RegEx) to filter out those databases I would like to backup.

Script

PowerShell
Skript bearbeiten|{#scriptcode_dlg.remove_script}
<# SYNOPSIS 
      Backup all databases, where the name matches a regex pattern. 
   DESCRIPTION 
      Backup all databases, where the name matches a regex pattern. 
      Works with SQL Server 2005 and higher version. 
   NOTES 
      Author  : Olaf Helper 
      Requires: PowerShell Version 2.0, SMO assembly 
#> 
 
# Configuration variables. 
[string]$server =".\SQLEXPRESS";  # Server instance name. 
[string]$folder ="";       # Folder to backup to. If empty the default backup folder is used. 
[string]$pattern="\w";     # Regex pattern for database names. Use "\w" for all databases. 
[bool]  $withLog=1;        # 1 = Backup Log file, otherwise not. 
 
 
# Reference to SMO 
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO'); 
# Referenced by SMO, so also requiered. 
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo'); 
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Sdk.Sfc'); 
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended'); 
 
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $server; 
 
# If folder isn't defined use the default backup directory. 
If ($folder -eq "") 
{ $folder = $srv.Settings.BackupDirectory + "\" }; 
 
Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Started ..."); 
 
foreach ($db in $srv.Databases) 
{ 
    # Backup only if the database name matches the regex pattern. Non need to backup TempDB. 
    IF ((![regex]::IsMatch($db.Name, $pattern)) -or ($db.Name -eq "tempdb")) 
    { continue; }; 
     
    $ts = (Get-Date -format yyyy-MM-dd_HH-mm-ss); 
    Write-Output ($ts + ": " + $db.Name + " full backup"); 
     
    $bak = New-Object ("Microsoft.SqlServer.Management.Smo.Backup"); 
    $bak.Action = "DATABASE"; 
    $bak.Database = $db.Name; 
    $bak.Devices.AddDevice($folder + $db.Name + "_" + $ts + "_full.bak""File"); 
    $bak.BackupSetDescription = "Full backup of " + $db.Name + " " + $ts; 
     # 0 = full backup. 
    $bak.Incremental = 0; 
 
    # Starting full backup process. 
    $bak.SqlBackup($srv); 
 
 
    # If recovery model = simple, then a log backup isn't possible. 
    If (($db.RecoveryModel -eq 3) -or ($withLog -ne 1)) 
    { continue; } 
     
    # Log backup. 
    $ts = Get-Date -format yyyy-MM-dd_HH-mm-ss; 
    Write-Output ($ts + ": " + $db.Name + " log backup"); 
     
    $log = New-Object ("Microsoft.SqlServer.Management.Smo.Backup"); 
    $log.Action = "LOG"; 
    $log.Database = $db.Name; 
    $log.Devices.AddDevice($folder + $db.Name + "_" + $ts + "_log.trn""File"); 
    $log.BackupSetDescription = "Log backup of " + $db.Name + " " + $ts; 
    # Truncated log after backup. 
    $log.LogTruncation = "TRUNCATE"; 
     
    # Start the log backup process. 
    $log.SqlBackup($srv); 
}; 
 
$srv.Dispose; 
Write-Output ((Get-Date -format  yyyy-MM-dd_HH-mm-ss) + ": Finished");