SQL Databases

PowerShell script to create backups of all SQL databases on a device.

Overview

Dev Insight: This script was written to provide SQL backups to clients using SQL Express with a backup solution that doesn't natively support SQL. It is designed to be used on a schedule in tandem with a file/image-based backup solution.

This script uses the SqlServer module to automate the creation of SQL database backups.

SqlServer 22.1.1nuget

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.

Download SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS)docsmsft

Granting backup database permissions to SYSTEM:

1. Install & login to Microsoft SQL Server Management Studio (SSMS) on the SQL Server
2. Navigate to Server\Instance > Security > Logins

3. Right-click NT AUTHORITY\SYSTEM and choose Properties

4. Under Server Roles, make sure public is checked

5. Under User Mapping, check each database and select the following database role memberships for each:

   1. public

   2. 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.

https://github.com/wise-io/scripts/blob/main/scripts/BackupSQL.ps1

<#
  .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.