Posted On September 29, 2021

PowerShell: Get SQL Server Backup Statuses

kimconnect 0 comments
blog.KimConnect.com >> Codes , Database >> PowerShell: Get SQL Server Backup Statuses
$computername='sql01'

function getSqlBackupInfo ($sqlInstanceName=$env:computername, $dbName){
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  
  $location=if($sqlInstanceName.Contains("`\")){
      "SQLSERVER:\SQL\$sqlInstanceName\Databases"
    }else{
      "SQLSERVER:\SQL\$sqlInstanceName\DEFAULT\Databases"
    }
    function getPacificTime($time){
      if($time){
        [System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId($time,'Pacific Standard Time').tostring("MM-dd-yyyy-HH-mm")+'_PST'
      }else{
        [System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId((Get-Date),'Pacific Standard Time').tostring("MM-dd-yyyy-HH-mm")+'_PST'
      }      
    }
    $displayFormat=@{Label='dbName';Expression={$_.Name}},
        @{Label='lastFull';Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {'NA'}
            ELSE {getPacificTime $_.LastBackupDate}}},
        @{Label='lastDifferential';Expression={IF ($_.LastDifferentialBackupDate -eq "01/01/0001 00:00:00") {'N/A'}
             ELSE {getPacificTime $_.LastDifferentialBackupDate}}},
        # @{Label='mostRecentBackupType';Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {'N/A'}
        #     ELSEIF ($_.LastBackupDate -gt $_.LastDifferentialBackupDate) {'FULL'}
        #     ELSE {'DIFF'}}},
        # @{Label='daysSinceLastBackup';Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {"Never Backed Up!"}
        #     ELSEIF ($_.LastDifferentialBackupDate -gt $_.LastBackupDate) {((Get-Date) – $_.LastDifferentialBackupDate).Days}
        #     ELSE {((Get-Date)-$_.LastBackupDate).Days}}},
        @{Label='dbLocation';Expression={$_.PrimaryFilePath}}
  if ($dbName){
    ls -force $location | where-object {$_.Name -eq $DBName; $_.Refresh()} |ft $displayFormat
  }else{
    ls -force $location | where-object {$_.Name -notin 'tempdb','master','model','msdb'; $_.Refresh()} |ft $displayFormat
  }
}

getSqlBackupInfo $computername

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Post

PowerShell: Check RPC Reachability of Remote Computer

# This is an ancient script that would work with PowerShell versions 2 to 5,…

PowerShell: Get IP’s From Computer Names

Resolve from Names to IPs: $names=@( 'TESTVM001', 'TESTVM002', 'TESTVM003' ) foreach($name in $names){ $ips =…

PowerShell: Search for Hyper-V Guest VM That Has Not Been Registered In Cluster

# findVmHost.ps1 $vmName='TESTVM01' function findVmHost($vmName){ try{ Import-Module Hyper-V Import-Module FailoverClusters $allHyperVHosts={(Get-ClusterNode | Where { $_.State…