Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

A Month of PowerShell – Day 21 (Backups)

Welcome to Day 21 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at Please refer to this page to see all of the posts in this series, and to quickly go to them.

If you don’t have a database backup that you can restore, you’re just one disaster away from being unemployed. Let’s try to prevent that from happening (at least because of not having backups) by performing a full backup of all databases on your server:

#clear variables
$Server     = $null
#Assign variables
$Instance   = "localhost\SQL2008"
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
ForEach ($db in $Server.Databases)
    if ($db.Status -EQ 'Normal' -and $db.IsMirroringEnabled -EQ $false -and `
        $db.Name -NE 'tempdb' -and $db.Name -NE 'Verify')
        $Backup = New-Object ("$SMO.Backup")
        $Backup.Action = 'Database'  #'Log' for log backups, 'Files' for specific files
        $Backup.Incremental = $False # False for full backup, True for Differential
        # You can only do full backups of master, so check for this
        IF (!($db.Name -eq "master" -and ($Backup.Incremental -eq $True -or $Backup.Action -ne 'Database')))
            IF ($Backup.Action -EQ 'Log') {$BackupType = 'Log'}
            ELSEIF ($Backup.Action -EQ 'Database' -and $Backup.Incremental -EQ $False) {$BackupType = 'Full'}
            ELSEIF ($Backup.Action -EQ 'Database' -and $Backup.Incremental -EQ $True)  {$BackupType = 'Diff'}
            $Backup.BackupSetDescription = "$BackupType Backup of " + $db.Name
            $Backup.BackupSetName = $db.Name + ' Backup'
            $Backup.Checksum = $true
            IF ($Server.EngineEdition -ne "EnterpriseOrDeveloper") {$Backup.CompressionOption = "Off"}
            ELSE {$Backup.CompressionOption = "On"}
            $Backup.CopyOnly = $False
            $Backup.Database = $db.Name
            $Backup.MediaDescription = 'Disk'
            $dir = $Server.Settings.BackupDirectory + "\" + $db.Name + "\" + $BackupType + "\"
            IF (!(Test-Path -Path $dir)) {New-Item $dir -Type Directory}
            $Backup.Devices.AddDevice($dir + $db.Name + "_" + (Get-Date -format yyyyMMddHHmmss) + '.bak', 'File')
            $Backup.SqlBackup($Server) | Out-Null

By changing the Action property, you can take log or file backups; by changing the incremental property you can take a differential backup.

If you are utilizing SMO from SQL Server 2012, a new method has been added: Backup-SqlDatabase. With this, you could perform a full backup of all databases by:

Get-ChildItem SQLSERVER:\SQL\localhost\SQL2012\Databases | Backup-SqlDatabase

This cmdlet has parameters to control all aspects of the backup.


Leave a comment on the original post [, opens in a new window]

Loading comments...