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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
# 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)) |