Powershell Full Database And Transaction Log Backup Script Works With Sqlexpress

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
	# 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"
					$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
					# Backup
					# 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-Host ("Finished at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss))