SQL Databases
PowerShell script to create backups of all SQL databases on a device.
Overview
This script uses the SqlServer module to automate the creation of SQL database backups.
Prerequisites:
For this script to run successfully, the user running the script will need to have permission to perform SQL database backups on all databases, including system databases. It is recommended to run the script as SYSTEM
and set permissions on each database using the process below.
Granting backup database permissions to SYSTEM
:
Install & login to Microsoft SQL Server Management Studio (SSMS) on the SQL Server
Navigate to Server\Instance > Security > Logins
Right-click
NT AUTHORITY\SYSTEM
and choose PropertiesUnder Server Roles, make sure
public
is checkedUnder User Mapping, check each database and select the following database role memberships for each:
public
db_backupoperator



After database permissions are set, you can deploy the script via your RMM.
Notes:
Unless changed manually, the default SQL backup location is
C:\Program Files\Microsoft SQL Server\MSSQL.[INSTANCENAME]\MSSQL\Backup
Script
Backups are not performed on databases named tempdb.
<#
.SYNOPSIS
Backup all SQL databases
.DESCRIPTION
Performs a backup of all SQL databases on all SQL instances of localhost.
Backups are stored in the default backup location of the server under the name databasename.bak
.PARAMETER AuditOnly
Switch parameter: when used, recent sql backup history will be displayed. No new backups will be performed.
.EXAMPLE
.\BackupSQL.ps1
.NOTES
Author: Aaron Stevenson
#>
param (
[switch]$AuditOnly # Audit recent SQL backups
)
function Install-PSModule {
<#
.SYNOPSIS
Installs and imports the provided PowerShell Modules
.EXAMPLE
Install-PSModule -Modules @('ExchangeOnlineManagement')
#>
param(
[Parameter(Position = 0, Mandatory = $true)]
[String[]]$Modules
)
Write-Output "`nChecking for necessary PowerShell modules..."
try {
# Set PowerShell to TLS 1.2 (https://devblogs.microsoft.com/powershell/powershell-gallery-tls-support/)
if ([Net.ServicePointManager]::SecurityProtocol -notcontains 'Tls12' -and [Net.ServicePointManager]::SecurityProtocol -notcontains 'Tls13') {
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
}
# Install NuGet package provider
if (!(Get-PackageProvider -ListAvailable -Name 'NuGet' -ErrorAction Ignore)) {
Write-Output 'Installing NuGet package provider...'
Install-PackageProvider -Name 'NuGet' -MinimumVersion 2.8.5.201 -Force
}
# Set PSGallery to trusted repository
Register-PSRepository -Default -InstallationPolicy 'Trusted' -ErrorAction Ignore
if (!(Get-PSRepository -Name 'PSGallery' -ErrorAction Ignore).InstallationPolicy -eq 'Trusted') {
Set-PSRepository -Name 'PSGallery' -InstallationPolicy 'Trusted'
}
# Install & import modules
ForEach ($Module in $Modules) {
if (!(Get-Module -ListAvailable -Name $Module -ErrorAction Ignore)) {
Write-Output "`nInstalling $Module module..."
Install-Module -Name $Module -Force
Import-Module $Module
}
}
Write-Output 'Modules installed successfully.'
}
catch {
Write-Warning 'Unable to install modules.'
Write-Warning $_
exit 1
}
}
function Get-SqlInstances {
try { return (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances }
catch {
Write-Warning 'Unable to detect SQL instances.'
exit 1
}
}
function Get-SqlBackupAudit {
foreach ($Instance in $Instances) {
Write-Output "`nBackup history for localhost\$Instance (past month):"
Get-SqlBackupHistory -ServerInstance "localhost\$Instance" -Since LastMonth | Format-Table -Property 'DatabaseName', 'BackupSetType', 'BackupStartDate', 'BackupFinishDate', 'CompressedBackupSize'
}
Write-Output 'No new backups were performed.'
}
function Start-SqlBackups {
foreach ($Instance in $Instances) {
$Databases = Get-SqlDatabase -ServerInstance "localhost\$Instance" | Where-Object { $_.Name -ne 'tempdb' }
Write-Output "`nDatabases in localhost\$Instance`:"
Write-Output $Databases | Format-Table -Property 'Name', 'Status', 'Size', 'Owner'
Write-Output '' # For output formatting
foreach ($Database in $Databases) {
Write-Output "Performing backup of $Database..."
Backup-SqlDatabase -ServerInstance "localhost\$Instance" -Database $Database.name -Initialize
}
}
Write-Output 'Backup jobs complete.'
}
# Set PowerShell preferences
$ProgressPreference = 'SilentlyContinue'
$ErrorActionPreference = 'Stop'
# Get Sql Instances
$Instances = Get-SqlInstances
# Get modules
Install-PSModule -Modules @('SqlServer')
# Backup SQL databases in all instances on localhost
if ($AuditOnly) { Get-SqlBackupAudit }
else { Start-SqlBackups }
Examples
Example 1
.\BackupSQL.ps1
Checking for necessary PowerShell modules...
Modules installed successfully.
Databases in localhost\SQLEXPRESS:
Name Status Size Owner
---- ------ ---- -----
master Normal 6.00 MB sa
model Normal 16.00 MB sa
msdb Normal 149.56 MB sa
Performing backup of [master]...
Performing backup of [model]...
Performing backup of [msdb]...
Backup jobs complete.
This example creates a backup of all SQL databases on every detected instance, excluding temp databases.
Example 2
.\BackupSQL.ps1 -AuditOnly
Checking for necessary PowerShell modules...
Modules installed successfully.
Backup history for localhost\SQLEXPRESS (past month):
DatabaseName BackupSetType BackupStartDate BackupFinishDate Compressed
BackupSize
------------ ------------- --------------- ---------------- ----------
master Database 2/1/2022 12:15:26 PM 2/1/2022 12:15:26 PM 3825664
master Database 2/1/2022 1:11:16 PM 2/1/2022 1:11:16 PM 3821568
master Database 2/1/2022 1:51:43 PM 2/1/2022 1:51:43 PM 3821568
master Database 2/1/2022 2:07:29 PM 2/1/2022 2:07:29 PM 3821568
model Database 2/1/2022 12:15:26 PM 2/1/2022 12:15:26 PM 2578432
model Database 2/1/2022 1:11:16 PM 2/1/2022 1:11:16 PM 2574336
model Database 2/1/2022 1:51:43 PM 2/1/2022 1:51:43 PM 2574336
model Database 2/1/2022 2:07:29 PM 2/1/2022 2:07:29 PM 2574336
msdb Database 2/1/2022 12:15:27 PM 2/1/2022 12:15:29 PM 126077952
msdb Database 2/1/2022 1:11:16 PM 2/1/2022 1:11:19 PM 126073856
msdb Database 2/1/2022 1:51:43 PM 2/1/2022 1:51:48 PM 126073856
msdb Database 2/1/2022 2:07:29 PM 2/1/2022 2:07:32 PM 126073856
No new backups were performed.
This example displays database backups performed in the last month. It does not perform new backups.
Parameters
-AuditOnly
Optional switch parameter that outputs a history of all database backups for the last month. No additional backups are performed when using this parameter.
Last updated
Was this helpful?