Backups Information for Multiple Servers using PowerShell

Backup Information Using Powershell :

For getting our Backup Information here using SQLPS Module which was already installed in my Machine , I heard this SQLPS will get installed along with SQL 2014 client tools installed on the server.

To check if SQLPS Module is installed on your machine we can use Powershell to check this by using below command :

Get-Module -ListAvailable

Once you execute above command in PowerShell you will see all the list of modules installed in your machine

Below is the SQLPS module for SQL 2014

If you have not seen SQLPS module installed on your machine then please download it from Microsoft site and Import them using Import-Module command.

After Importing the Module you need to stop the warning message to be generated because some of the modules that uses unapproved verbs which results in throwing unnecessary warning messages so its better to suppress those warning alerts or messages. Use below command to not getting warning message:

Import-Module -Name SQLPS -DisableNameChecking

The below PowerShell Function gives you the Backup Information of Single or Multiple Instances:

#Requires -Version 3.0
function Get-BackupInfo {
<#
.SYNOPSIS
Returns database backup information for a Microsoft SQL Server database.

.DESCRIPTION
Get-BackupInfo is a function that returns database backup information for
one or more Microsoft SQL Server databases.

.PARAMETER ComputerName
The computer that is running Microsoft SQL Server that you’re targeting to
query database backup information for.

.PARAMETER InstanceName
The instance name of SQL Server to return database backup information for.
The default is the default SQL Server instance.

.PARAMETER DatabaseName
The database(s) to return backup information for. The default is all databases.

.EXAMPLE
Get-BackupInfo -ComputerName sql01

.EXAMPLE
Get-BackupInfo -ComputerName sql01 -DatabaseName master, msdb, model

.EXAMPLE
Get-BackupInfo -ComputerName sql01 -InstanceName SQL1 -DatabaseName master,msdb, model

.EXAMPLE
'master', 'msdb', 'model' | Get-BackupInfo -ComputerName sql01

.INPUTS
String

.OUTPUTS
PSCustomObject
#>

   [CmdletBinding()]
   param (
      [Parameter(Mandatory,
      ValueFromPipelineByPropertyName)]
      [Alias('ServerName','PSComputerName')]
      [string[]]$ComputerName,

      [Parameter(ValueFromPipelineByPropertyName)]
      [ValidateNotNullOrEmpty()]
      [string[]]$InstanceName = 'Default',
    
      [Parameter(ValueFromPipelineByPropertyName)]
      [ValidateNotNullOrEmpty()]
      [string[]]$DatabaseName = '*'

   )

   BEGIN {
      $problem = $false
      Write-Verbose -Message "Attempting to load SQL Module if it's not already loaded"
      if (-not (Get-Module -Name SQLPS)) {
          try {
              Import-Module -Name SQLPS -DisableNameChecking -ErrorAction Stop
          }
          catch {
              $problem = $true
              Write-Warning -Message "An error has occurred.&amp;nbsp; Error details: $_.Exception.Message"
          }
      }
   }

   PROCESS {
       foreach ($Computer in $ComputerName) {
            foreach ($Instance in $InstanceName) {
                Write-Verbose -Message 'Checking for default or named SQL instance'
                If (-not ($problem)) {
                    If (($Instance -eq 'Default') -or ($Instance -eq 'MSSQLSERVER')) {
                       $SQLInstance = $Computer
                    }
                    else {
                       $SQLInstance = "$Computer\$Instance"
                    }
                    $SQL = New-Object('Microsoft.SqlServer.Management.Smo.Server') -ArgumentList $SQLInstance
                }

                if (-not $problem) {
                     foreach ($db in $DatabaseName) {
                         Write-Verbose -Message "Verifying a database named: $db exists on SQL Instance $SQLInstance."
                         try {
                             if ($db -match '\*') {
                                  $databases = $SQL.Databases | Where-Object Name -like "$db"
                             }
                             else {
                                  $databases = $SQL.Databases | Where-Object Name -eq "$db"
                             }
                         }
                         catch {
                             $problem = $true
                             Write-Warning -Message "An error has occurred.&amp;nbsp; Error details: $_.Exception.Message"
                         }
                         if (-not $problem) {
                             foreach ($database in $databases) {
                                  Write-Verbose -Message "Retrieving information for database: $database."
                                  [PSCustomObject]@{
                                      ComputerName = $SQL.Information.ComputerNamePhysicalNetBIOS
                                      InstanceName = $Instance
                                      DatabaseName = $database.Name
                                      LastBackupDate = $database.LastBackupDate
                                      LastDifferentialBackupDate = $database.LastDifferentialBackupDate
                                      LastLogBackupDate = $database.LastLogBackupDate
                                      RecoveryModel = $database.RecoveryModel
                             }
                          }
                     }
                 }
             }
         }
      }
   }
}

Execute above function and call the function using parameters like below :

Getting Backup information for my Default Instance and output will be generating lie below

Get-BackupInfo -ComputerName DESKTOP-02JIB76 |Out-GridView 

We can retrieve Backup Information for multiple Named Instances by using below command just provide InstanceName without servername\

Get-BackupInfo -ComputerName DESKTOP-02JIB76 -InstanceName SQL1,SQL2 |Out-GridView

From above screenshot we can say only Full Backup is happening for the databases there is NO Differential and Logbackups running for databases.

If you want the output to get in PowerShell window itself instead of Gridview then run below command :

Get-BackupInfo -ComputerName DESKTOP-02JIB76 -InstanceName SQL1,SQL2 |Format-Table

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.