SQL Clone
SQLServerCentral is supported by Redgate
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 http://blog.waynesheffield.com/wayne/a-month-of-powershell/. 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 [blog.waynesheffield.com, opens in a new window]

Loading comments...