Powershell - Scripting Backups

Backing up

The Backup-SqlDatabase cmdlet that comes with the SQLPS modules allows you to perform database backups using different options. When you run Get-Help Backup-SqlDatabase, you should get a full list of syntax and examples. The options you get with this cmdlet are similar to the options you have with the BACKUP DATABASE T-SQL command. The following is an example script that performs a full database backup on a timestamped backup file:

Import-Module SQLPS -DisableNameChecking

#current server name
$servername = "ROGUE"

#$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $servername

$dbname = "Chinook"
$currdate = Get-Date -Format yyyyMMddHHmmss
$backupfolder = "C:\BACKUP\"

#generate backup file path and name
$fullbackupfilename = "$($dbname)_Full_$($currdate).bak"
$fullbackupfile = Join-Path $backupfolder $fullbackupfilename

#example filename that gets generated is:
#C:\BACKUP\Chinook_Full_20141023235306.bak

Backup-SqlDatabase -ServerInstance $servername -Database $dbname -BackupFile $fullbackupfile -Checksum -Initialize -BackupSetName "$dbname Full Backup"

Write-Output "Database has been backed up $fullbackupfile"

Creating a differential backup using the Backup-SqlDatabase cmdlet is not much different from a full backup, with the exception of the -Incremental option:

$diffbackupfilename = "$($dbname)_Diff_$($currdate).bak"
$diffbackupfile = Join-Path $backupfolder $diffbackupfilename

Backup-SqlDatabase -ServerInstance $servername -Database $dbname -BackupFile $diffbackupfile -Incremental -Checksum -Initialize -BackupSetName "$dbname Diff Backup"

Write-Output "Database has been backed up $diffbackupfile"

A transaction log backup requires a different BackupAction value:

$logbackupfilename = "$($dbname)_Log_$($currdate).trn"
$logbackupfile = Join-Path $backupfolder $logbackupfilename

Backup-SqlDatabase -ServerInstance $servername -Database $dbname -BackupFile $logbackupfile -BackupAction Log -Checksum -Initialize -BackupSetName "$dbname Txn Backup"

Write-Output "Database has been backed up $logbackupfile"