Aug 20th, 2012 under Programming PowerShell Gist
Loosely based off of other examples I found, this script will perform full database backups of all databases or tran log backups of all full/bulk-logged databases. In addition, it will utilize the default backup path (if one is not given), and delete old backup files (that conform to the file naming convention) that are older than a given number of retention hours (default to 48 hours): Examples:
Full backup to a given path, retaining only 26 hour old backups on disk
. .\backup.ps1 SQLINSTANCE\Name full 26 "C:\some\path"
Log backup (to the default instance configured backup path), retaining default 48 hours of logs on disk
. .\backup.ps1 SQLINSTANCE\Name log
Assumes that the given windows user has necessary permissions to perform backups. Compression is not specified, so will assume instance default config
# Performs a full or log backup of all databases on a SQL instance
Param(
[Parameter(Position=0,Mandatory=$true)]
[ValidateNotNullorEmpty()]
[string]$sqlserver,
[Parameter(Position=1,Mandatory=$true)]
[ValidateSet('full','log')]
[string]$mode,
[Parameter(Position=2)]
[ValidateNotNullorEmpty()]
[int]$hoursretention=48,
[Parameter(Position=3)]
[string]$path=$null
)
# Load SMO assembly, and SMOExtended and SQLWMIManagement libraries for SQL 2008+
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
}
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver
# Use instance default backup directory if none specified
if ($path -eq "")
{
$path = $srv.Settings.BackupDirectory
}
Write-Host ("Started at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss))
Write-Host ("Server: " + $sqlserver)
Write-Host ("Backup path: " + $path)
Write-Host ("Mode: " + $mode)
# Enumerate all databases
foreach ($db in $srv.Databases)
{
if ($db.Name -ne "tempdb")
{
if ($mode -eq "full" -or ($mode -eq "log" -and ([string]::Compare($db.RecoveryModel,"simple",$True))))
{
Write-Host("Database: " + $db.Name)
# Create db name sub dir
if ((Test-Path (Join-Path $path $db.Name)) -ne $True)
{
New-Item (Join-Path $path $db.Name) -type Directory | Out-Null
}
$backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$backup.Database = $db.Name
$timestamp = Get-Date -format yyyyMMddHHmmss
if ($mode -eq "log")
{
$backup.Action = "Log"
$type = "log"
$ext = "trn"
}
else
{
$backup.Action = "Database"
$type = "backup"
$ext = "bak"
}
$dbpath = Join-Path $path $db.Name
$filename = Join-Path $dbpath ("{0}_{1}_{2}.{3}" -f $db.Name, $type, $timestamp, $ext)
Write-Host("Backing up to: " + $filename)
$backup.Devices.AddDevice($filename, "File")
$backup.BackupSetDescription = ("{0} backup of {1}" -f $mode, $db.Name)
$backup.Incremental = 0
# Start backup
try
{
# Backup
$backup.SqlBackup($srv)
# Delete old backup files
$backups = get-childitem $dbpath -Filter ("*." + $ext)
foreach ($file in $backups)
{
# Parse timestamp
if ($file -match ("{0}_{1}_({2}).{3}" -f $db.Name, $type, "\d{14}", $ext))
{
$timestamp = [datetime]::ParseExact($matches[1],"yyyyMMddHHmmss",$null)
# Check if backup file older than our retention period
if ($timestamp -le (Get-Date).AddHours(-$hoursretention))
{
Write-Host("Removing old backup: " + $file)
Remove-Item (Join-Path $dbpath $file) | Out-Null
}
}
}
}
catch [Exception]
{
Write-Error($_.Exception)
}
}
}
}
Write-Host ("Finished at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss))