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 *