Backup-SqlExpress.ps1
|
<#PSScriptInfo
.VERSION 1.0.2 .GUID c05ffe77-c94d-49ae-b673-3dfe6694d15f .AUTHOR Chris Carter .COMPANYNAME N/A .COPYRIGHT 2018 Chris Carter .TAGS SQL Server Express Backup .LICENSEURI https://creativecommons.org/licenses/by-nc-sa/4.0/legalcode .PROJECTURI https://gallery.technet.microsoft.com/scriptcenter/Backup-SQL-Server-Express-ce689cce .ICONURI .EXTERNALMODULEDEPENDENCIES SQLPS .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES Initial Release #> <# .SYNOPSIS Backs up an instance of SQL Server Express .DESCRIPTION Backup-SqlExpress backs up an instance of SQL Server Express. SQL Server Express does not have access to the SQL Server Agent which schedules backups. This script is designed to be set to run as a Scheduled Task on an hourly basis. It will take a once daily full backup of databases and an hourly incremental transaction log backup of databases using the Full recovery model. It will copy files to external storage, and manage retention of those files. .PARAMETER ServerInstance The server instance that will be backed up. The default is the SQL Server Express instance on the local machine (localhost/sqlexpress). .PARAMETER Destination The location of the external storage to copy backup files to. .PARAMETER Retention The number of days the script will keep full backups. After full backups are deleted, all preceding log backups are also deleted. The default is three days. .PARAMETER FullBackupHour The hour of the day to take full backups expressed as a number between 0 and 23. The default is 0 (midnight). .INPUTS None Backup-SqlExpress does not accept pipeline input. .OUTPUTS None Backup-SqlExpress does not produce any output. .EXAMPLE Backup-SqlExpress -Destination "\\FileServer\Path\To\External\Backups This command will backup the local SQL Server Express instance, move the backup files to the file server specified, keep the files for three days, and take a full backup every night at midnight. .FUNCTIONALITY Backing up SQL Server Express .NOTES The model and master databases will not have transaction log backups taken even if they are in the Full recovery model. This is by design and according to best practices. #> #Requires -Version 3.0 [CmdletBinding()] Param( [String]$ServerInstance='localhost\sqlexpress', [String]$Destination, [Int]$Retention=3, [Int]$FullBackupHour=0 ) #Create external backup directory if it doesn't exist if ($Destination) { if (-not (Test-Path $Destination)) { New-Item -Path $Destination -ItemType Directory } } else { Write-Warning "It is highly recommended to provide a location to copy the backup files to because the default backup directory is on local storage." } #Load SQLPS and set the location to the databases path Import-Module SQLPS -DisableNameChecking Set-Location SQLSERVER:\SQL\$ServerInstance\Databases #We use the SMO to get the server's default backup directory. We will back up to this and then copy the #the files over to the external location $server = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList $ServerInstance $backupDir = $server.Settings.BackupDirectory #Get all databases except tempdb which cannot be backed up $dbs = Get-ChildItem -Force | Where-Object {$_.Name -ne 'tempdb'} #Go through each database foreach ($db in $dbs) { #Build strings for filenames $dbName = $db.Name $dt = Get-Date $dtFormat = '{0:yyyy-MM-ddTHH_mm_ss}' -f $dt #If the current hour matches the time chosen for full backups, take full backups...[1] if ($dt.Hour -eq $FullBackupHour) { #Set the filename to be used $bakFileName = "$($dbName)_db_$dtFormat.bak" #Take backup Backup-SqlDatabase -Database $dbName -BackupAction Database -ServerInstance $ServerInstance ` -ConnectionTimeout 0 -BackupFile $bakFileName } #[1]...otherwise backup transaction logs else { #model, master, and any database not in Full recovery model should not have transaction logs backed up #master can't have log backups even in Full recovery model, and model is not recommended to have log backups. #Any database in Simple will fail if ($dbName -notin 'model','master' -and $db.RecoveryModel -eq "Full") { $bakFileName = "$($dbName)_tlog_$dtFormat.trn" Backup-SqlDatabase -Database $dbName -BackupAction Log -ServerInstance $ServerInstance ` -Incremental -ConnectionTimeout 0 -BackupFile $bakFileName } } #Move to external location for redundancy if supplied if ($Destination) { Copy-Item "$backupDir\$bakFileName" -Destination "FileSystem::$Destination" } } #Delete backups older than retention days Get-ChildItem $backupDir,"FileSystem::$Destination" -Recurse -Filter *.bak | Where-Object {$_.CreationTime -le (Get-Date).AddDays(-$Retention)} | Remove-Item -Force #Get the oldest full backup and remove any trn files older than it since they are useless when their full backup companion is deleted $oldestBakTime = Get-ChildItem $backupDir -Recurse -Filter *.bak | Sort-Object -Property CreationTime | Select-Object -First 1 -ExpandProperty CreationTime Get-ChildItem $backupDir,"FileSystem::$Destination" -Recurse -Filter *.trn | Where-Object {$_.CreationTime -le $oldestBakTime} | Remove-Item -Force |