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 ,The PowerShell module for SQL Server is not included by default with the Windows operating system. SQL Server includes PowerShell for SQL Server (sqlps), but you may need to update it to the latest version. PowerShell is not included in SQL Server Management Studio (SSMS). I heard this SQLPS will get installed along with SQL 2014 client tools installed on the server. For our SQL environment, we use a variety of backup tools, including NetApp snap manager, Commvault tape backup, TSM, EMC Avmar backup and even SQL Native backups. There are multiple reasons to use above mentioned backup tools .

Get-BackupInfo is a Microsoft SQL Server function that returns database backup information for one or more databases. Full database backups, transaction log backups, and differential backups are all included. The Microsoft.SqlServer.Management cmdlet is the inspiration for this function. Smo.Backup is a backup class. The properties on the Smo object correspond to the parameters on this class.

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

Author: Sri

Hello Friends, This blog is to help the IT professionals who want to become a professional SQL Server DBA but don't know how and from where to start with. So, I am going to share my experiences and my learning in this blog. Will talk about what are the pre-requisite skills required to become a Professional SQL Server DBA, how much time it takes to be a good DBA and what are the additional skill sets are required to become a good DBA. Apart from that I will also post real time sql server settings on server level and database level, Configuring High Availability. Also will share the Client requirements with real time setups if possible I will provide you with screenshots.

Leave a Reply

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