$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
September 29, 2021September 29, 2021
0 Comments