First off, why use PowerShell to backup your databases. You can create Maintenance Plans, T-SQL scripts or a combination of both. Well, why use tooling like Data Protection Manager or any other 3rd party backup solution. Most of them still use T-SQL under the hood (some use VSS copy).
What backing up your databases with T-SQL doesn’t provide is a centralized backup solution. The connection is scoped to the instance you’re running on. Although it is possible to backup other instances using linked servers it may not be as easy as it seems at first.
Many 3rd party application provide you the possibility to not only backup your complete environment but also restore databases on different instances. The key factor is the centralized point of view, which in this case is the backup, regardless of which instance it came from. By using common T-SQL techniques your point of view is the instance.
With PowerShell you can backup a database in quite a similar way as you’re used to with T-SQL, but with PowerShell you are not limited to the instance. By using Sql Management Objects (SMO) PowerShell will instant familiar.
The Power in PowerShell lies within the reusability. If we create functions and put these functions in modules we can load them in our PowerShell environment where they will be available to us. So, first off let’s create a function to backup a database. See the listing below for the complete function. We will break it down later on.
function Backup-SqlDatabase
{
# This function will backup a SQL Server Database
param (
[string]$server=$(throw "Please specify server."),
[string]$dbname=$(throw "Please specify database."),
[string]$backupdirectory,
[int]$nroffiles=1,
[switch]$copyonly,[switch]$compress,[string][ValidateSet('full','diff','log')]$backuptype="full" )
# Connect to the instance
$s = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $server
if($backupdirectory.Length -eq 0)
{
# backupdirectory is not specified, use the server default
$backupDirectory = $s.Settings.BackupDirectory
}
# Handlers for progress and completed events
$percentEventHandler = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] { Write-Host "Database backed up" $_.Percent "%" }
$completedEventHandler = [Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] { Write-Host $_.Error.Message}
# Create timestamp for backupfile
$timestamp = Get-Date -format yyyyMMddHHmm
# Create backup object
$sqlbackup = New-Object "Microsoft.SqlServer.Management.Smo.Backup")
switch($backuptype)
{
"full" { $sqlbackup.Action = "database"}
"log" { $sqlbackup.Action = "log" }
"diff"
{
if($copyonly) { Write-Warning "CopyOnly parameter is ignored when performing Differential backups."}
$sqlbackup.Incremental = $true
$sqlbackup.Action = "database"
}
}
$sqlbackup.BackupSetDescription = $backuptype + " backup of " + $dbname
$sqlbackup.BackupSetName = $dbname + " Backup by PowerShell"
$sqlbackup.Database = $dbname
$sqlbackup.MediaDescription = "Disk"
$sqlbackup.PercentCompleteNotification = 5
if($nroffiles -eq 1)
{
$sqlbackup.Devices.AddDevice($backupDirectory + "" + $dbname + "_" + $timestamp + ".bak", "File")
}
else
{
$count = 0
while ($count -lt $nroffiles)
{
$sqlbackup.Devices.AddDevice($backupDirectory + "" + $dbname + "_" + $count + "_" + $timestamp + ".bak", "File")
$count++
}
}
if($copyonly -and $server.versionmajor -gt 
{
$sqlbackup.CopyOnly = $true
}
if($compress -and $server.versionmajor -gt 9)
{
$sqlbackup.CompressionOption = 1
}
$sqlbackup.add_PercentComplete($percentEventHandler)
$sqlbackup.add_Complete($completedEventHandler)
$sqlbackup.SqlBackup($server)
}
At first some parameters are defined:
- $server: This is the instance you want to connect to.
- $dbname: The name of the database to be backed up.
- $backupdirectory: The folder where the backup will be placed. If none is specified the default backup directory specified in the instance properties will be used.
- $nroffiles: Number of backup files to create. Use this on large databases, it will speed up the backup.
- $copyonly: A switch parameter to indicate a copyonly backup. This feature was added in SQL 2005.
- $compress: A switch parameter to indicate a compressed backup. This feature was added in SQL 2008 Enterprise edition or SQL 2008 R2 Standard and higher editions. (the script will not check this).
- $backuptype: The type of backup to be made, Full, Incremental or Log.
We start the script with connecting to the instance. I’m using objects from SMO. To use these objects in PowerShell you must load them first; use the scripts in books online http://technet.microsoft.com/en-us/library/cc281962.aspx. When you have a connection to the instance you can get it’s default backup path. this is one of the fields. A complete list can be found in books online (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server_members.aspx).
We will create two event handlers, these are optional. The first one is to get the progress back, the second one is for the result text. This is the text displayed in the messages tab in SSMS. Next step is a handle to the database and a timestamp to put in the backup name.
Now we will create the backup object and set the action. The actions specifies a log or database backup. A differential backup is specified by setting the field Incremental to true. Then we will set some other parameters and finally we will create the backup files, 1 or more.
Next up are the CopyOnly and Compress parameters. We check the minimal database version and if we’re satisfied with the results we will set the parameters. Then the events are set up and all is done. If you add this function to PowerShell (be sure to first load SMO) you can backup a database with a one-liner.
PS C:Usersrobert> Backup-SqlDatabase -server . -dbname msdb
Database backed up 8 %
Database backed up 17 %
Database backed up 26 %
Database backed up 33 %
Database backed up 37 %
Database backed up 40 %
Database backed up 49 %
Database backed up 58 %
Database backed up 66 %
Database backed up 73 %
Database backed up 79 %
Database backed up 81 %
Database backed up 87 %
Database backed up 90 %
Database backed up 95 %
Database backed up 100 %
BACKUP DATABASE successfully processed 1462 pages in 0.765 seconds (14.925 MB/sec).
Nothing special, but also here lies the power within PowerShell. Take a look at the following function.
function Get-SqlDatabases
{
[CmdletBinding(DefaultParametersetName="A")]
param
(
[parameter(Mandatory=$true, ParameterSetName="A",
ValueFromPipelineByPropertyName=$true,
HelpMessage="Instancename to connect to")]
[parameter(Mandatory=$true, ParameterSetName="B",
ValueFromPipelineByPropertyName=$true,
HelpMessage="Instancename to connect to")]
[Alias("s")]
[string]$server,
[parameter(Mandatory=$false, ParameterSetName="A",
ValueFromPipelineByPropertyName=$true,
HelpMessage="Only shows system databases")]
[switch]$system,
[parameter(Mandatory=$false, ParameterSetName="B",
ValueFromPipelineByPropertyName=$true,
HelpMessage="Only shows user databases")]
[switch]$user,
[parameter(Mandatory=$false, ParameterSetName="A",
ValueFromPipelineByPropertyName=$true,
HelpMessage="Only shows databases that can be backed up. This doesn't show tempdb or offline databases.")]
[parameter(Mandatory=$false, ParameterSetName="B",
ValueFromPipelineByPropertyName=$true,
HelpMessage="Only shows databases that can be backed up. This doesn't show tempdb or offline databases.")]
[switch]$isbackuppossible
)
$smo = new-object Microsoft.SqlServer.Management.Smo.Server $server
if(!$system -and !$user)
{
$db = $smo.Databases
}
else
{
if($system)
{
$db = $smo.Databases | where {$_.IsSystemObject -eq $true}
}
else
{
$db = $smo.Databases | where {$_.IsSystemObject -eq $false}
}
}
if($IsBackupPossible)
{
$db | where { $_.ID -ne 2 -and $_.ID -ne 3 -and $_.Status -eq "Normal" -and $_.IsUpdateable -eq $true}
}
else
{
$db
}
}
The complexity is in the definition of the parameter sets. This function has one mandatory parameter, $server, which as in the previous script is the instance to connect to. Then there are two mutually exclusive parameters, $system and $user. You provide either one of them or none of them. They will filter out the system databases or user databases. If you provide none of them the function will display all databases. The final optional parameter is $isbackupossible. You cannot backup every database, eg tempdb cannot be backed up as databases that are in a restoring or offline state. So I added this parameter to filter out databases that can be back upped. Take a look at the following output to see it working.
PS C:Usersrobert> Get-SqlDatabases -s .
Name Status Recovery Model CompatLvl Collation Owner
—- —— ————– ——— ——— —–
BackupTest Normal Full 100 Latin1_General_CI_AS HARTS10robert
Hartjes Normal Full 100 Latin1_General_CI_AS HARTS10robert
master Normal Simple 100 Latin1_General_CI_AS sa
model Normal Full 100 Latin1_General_CI_AS sa
msdb Normal Simple 100 Latin1_General_CI_AS sa
SqlMonitoringDB Normal Full 100 SQL_Latin1_General_CP1_CI_AS HARTS10robert
tempdb Normal Simple 100 Latin1_General_CI_AS sa
Now we can combine this function and the previous one in a true PowerShell one-liner.
foreach($db in Get-SqlDatabases -server . -isbackuppossible) {backup-sqldatabase -server . -dbname $db.Name}
And there you have it. Now if you extend the one-liner to get the instancename from any source you like (AD, text file) you can backup all your databases in one go. Now try that with T-SQL.
