Check the Last SQL Server Backup Date using Windows PowerShell
By: Edwin Sarmiento | Comments (19) | Related: More > PowerShell
Problem
In a previous tip, Retrieve List of Databases and their Properties using PowerShell, you've seen how you can use Windows PowerShell to audit database properties and use Microsoft Excel to generate a report. Part of the daily SQL Server DBA tasks is to check the backups of all the databases in a SQL Server instance. How do we use Windows PowerShell to check for the last backup date of SQL Server databases and to confirm that they still meet our service level agreement?
Solution
One of the challenges I had when I was starting out as a SQL Server DBA was to check for the last backup date for a database on a daily basis to make sure we meet our service level agreements (SLA). One way to do this is to find out which tables in the msdb database contain the records of the backup history. What's really challenging here is the fact that you would have to look at the tables and their corresponding relationships to build the query joins. Plus, the msdb database will only contain records for databases with backups. What about those that don't? You would need to join tables in the msdb database with those in the master database
For SQL Server 2005/2008, the script below displays the last backup date of all the databases on your SQL Server instance. This script is taken from the MSDN Code Gallery
SELECT
T1.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') AS LastBackUpTaken
FROM master.sys.databases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name
For SQL Server 2000, you can simply replace the sys.databases table with master.dbo.sysdatabases.
With Windows PowerShell, things are a bit easy as you have seen from the previous tips. The Database class represents a SQL Server database object. What we're more concerned about in this tip are the properties that specifically pertain to backups - LastBackupDate and LastLogBackupDate. The properties are self-explanatory. The only thing we've added from the scripts in the previous tips is the last line, adding new properties we've already mentioned, highlighting the power and simplicity of Windows PowerShell from the script.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2000"
$dbs=$s.Databases
#Retrieves the last backup dates - both for FULL and LOG backups
$dbs | SELECT Name,LastBackupDate, LastLogBackupDate | Format-Table -autosize
Notice that those databases that have not been backed up return a value of 1/1/0001 12:00:00AM.
Let's do some Date Math and add the call to the Excel object as we did in the previous tip to format the results. The script above is more than enough for what we need. We only use Excel and the formatting for reporting purposes.
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
#Counter variable for rows
$intRow = 1
#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "D:\SQL_Servers.txt")
{
#Create column headers
$Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
$Sheet.Cells.Item($intRow,2) = $instance
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$intRow++
$Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
$Sheet.Cells.Item($intRow,2) = "LAST FULL BACKUP"
$Sheet.Cells.Item($intRow,3) = "LAST LOG BACKUP"
$Sheet.Cells.Item($intRow,4) = "FULL BACKUP AGE(DAYS)"
$Sheet.Cells.Item($intRow,5) = "LOG BACKUP AGE(HOURS)"
#Format the column headers
for ($col = 1; $col -le 5; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}
$intRow++
#######################################################
#This script gets SQL Server database information using PowerShell
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
# Create an SMO connection to the instance
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$dbs = $s.Databases
#Formatting using Excel
ForEach ($db in $dbs)
{
if ($db.Name -ne "tempdb") #We do not need the backup information for the tempdb database
{
$NumDaysSinceLastFullBackup = ((Get-Date) - $db.LastBackupDate).Days #We use Date Math to extract the number of days since the last full backup
$NumDaysSinceLastLogBackup = ((Get-Date) - $db.LastLogBackupDate).TotalHours #Here we use TotalHours to extract the total number of hours
if($db.LastBackupDate -eq "1/1/0001 12:00 AM") #This is the default dateTime value for databases that have not had any backups
{
$fullBackupDate="Never been backed up"
$fgColor3="red"
}
else
{
$fullBackupDate="{0:g}" -f $db.LastBackupDate
}
$Sheet.Cells.Item($intRow, 1) = $db.Name
$Sheet.Cells.Item($intRow, 2) = $fullBackupDate
#We use the .ToString() Method to convert the value of the Recovery model to string and ignore Log backups for databases with Simple recovery model
if ($db.RecoveryModel.Tostring() -eq "SIMPLE")
{
$logBackupDate="N/A"
$NumDaysSinceLastLogBackup="N/A"
}
else
{
if($db.LastLogBackupDate -eq "1/1/0001 12:00 AM")
{
$logBackupDate="Never been backed up"
}
else
{
$logBackupDate= "{0:g2}" -f $db.LastLogBackupDate
}
}
$Sheet.Cells.Item($intRow, 3) = $logBackupDate
#Define your service-level agreement in terms of days here
if ($NumDaysSinceLastFullBackup -gt 1)
{
$fgColor = 3
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow, 4) = $NumDaysSinceLastFullBackup
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor
$Sheet.Cells.Item($intRow, 5) = $NumDaysSinceLastLogBackup
$intRow ++
}
}
$intRow ++
}
$Sheet.UsedRange.EntireColumn.AutoFit()
cls
Next Steps
- Try converting your T-SQL DBA scripts to Windows PowerShell
About the author

View all my tips
Comments For This Article
Sunday, July 12, 2020 - 5:36:05 PM - bass_player | Back To Top (86126) |
Vinay, What do the backup history tables on msdn tell you? |
Friday, June 26, 2020 - 4:43:05 AM - Vinay | Back To Top (86051) |
I am getting a future date for a particular sqlserver [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
$dbs=$s.Databases | select name,Lastbackupdate
Any help would be helpful, Thanks in advance |
Thursday, December 20, 2018 - 2:01:33 AM - Gerry | Back To Top (78530) |
Hi Edwin, I am facing one issue with this in Always On AG, Whenever the server fails over, The new primary which was secondary will not have the latest backups when the preferred backup preference is primary. I am looking for Can we save the failover XEvents to detect the role changes to save it in another variable and then ignore the new elected primary as it won't have the latest backups. |
Thursday, December 22, 2016 - 8:07:51 AM - Shreyas Rane | Back To Top (45033) |
Hello edwin, Thanks for the Scripts. It really helped me reduced my day to day task. I managed to add backup ,disk space and database properties reports for SQL Server in one sheet. It was working fine in excel 2010. I configured same reports in Excel 2013. It gets overwritten instead of adding another excel tab. Can you please help me to resolve it? Thanks in advanced. Below is my complete script:
$DirectoryToSaveTo = "C:\DBA_Weekly_Report\" $date=Get-Date -format "yyyy-MM-d" $Filename="Weekly_Report_$($date)" $FromEmail="[email protected]" $ToEmail="[email protected]" $SMTPMail="[email protected]"
if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing { New-Item "$DirectoryToSaveTo" -type directory | out-null }
$Computers = Get-Content "C:\DBA_Weekly_Report\Servers\SQL_Servers_WIN.txt"
# before we do anything else, are we likely to be able to save the file? # if the directory doesn't exist, then create it
#Create a new Excel object using COM $Excel = New-Object -ComObject Excel.Application $Excel.visible = $True $Excel = $Excel.Workbooks.Add() $Sheet = $Excel.Worksheets.Item(1) $sheet.Name = 'DiskSpace' $Sheet.Activate() | Out-Null
#Create a Title for the first worksheet $row = 1 $Column = 1 $Sheet.Cells.Item($row,$column)= 'Disk Space Information'
$range = $Sheet.Range("a1","h2") $range.Merge() | Out-Null $range.VerticalAlignment = -4160
#Give it a nice Style so it stands out $range.Style = 'Title'
#Increment row for next set of data $row++;$row++
#Save the initial row so it can be used later to create a border $initalRow = $row
#Create a header for Disk Space Report; set each cell to Bold and add a background color $Sheet.Cells.Item($row,$column)= 'Computername' $Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 $Sheet.Cells.Item($row,$column).Font.Bold=$True $Column++ $Sheet.Cells.Item($row,$column)= 'DeviceID' $Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 $Sheet.Cells.Item($row,$column).Font.Bold=$True $Column++ $Sheet.Cells.Item($row,$column)= 'VolumeName' $Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 $Sheet.Cells.Item($row,$column).Font.Bold=$True $Column++ $Sheet.Cells.Item($row,$column)= 'TotalSizeGB' $Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 $Sheet.Cells.Item($row,$column).Font.Bold=$True $Column++ $Sheet.Cells.Item($row,$column)= 'UsedSpaceGB' $Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 $Sheet.Cells.Item($row,$column).Font.Bold=$True $Column++ $Sheet.Cells.Item($row,$column)= 'FreeSpaceGB' $Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 $Sheet.Cells.Item($row,$column).Font.Bold=$True $Column++ $Sheet.Cells.Item($row,$column)= '%Free' $Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 $Sheet.Cells.Item($row,$column).Font.Bold=$True $Column++ $Sheet.Cells.Item($row,$column)= 'State' $Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 $Sheet.Cells.Item($row,$column).Font.Bold=$True
#Set up a header filter $headerRange = $Sheet.Range("a3","h3") $headerRange.AutoFilter() | Out-Null
#Increment Row and reset Column back to first column $row++ $Column = 1 $critical=0 $warning=0 $good=0
#Get the drives and filter out CD/DVD drives foreach ($computer in $Computers) { $diskDrives = Get-WmiObject win32_LogicalDisk -Filter "DriveType='3'" -ComputerName $computer
#Process each disk in the collection and write to spreadsheet ForEach ($disk in $diskDrives) { $Sheet.Cells.Item($row,1)= $disk.__Server $Sheet.Cells.Item($row,2)= $disk.DeviceID $Sheet.Cells.Item($row,3)= $disk.VolumeName $Sheet.Cells.Item($row,4)= [math]::Round(($disk.Size /1GB),2) $Sheet.Cells.Item($row,5)= [math]::Round((($disk.Size - $disk.FreeSpace)/1GB),2) $Sheet.Cells.Item($row,6)= [math]::Round(($disk.FreeSpace / 1GB),2) $Sheet.Cells.Item($row,7)= ("{0:P}" -f ($disk.FreeSpace / $disk.Size))
$FreeSPACEPERCENTAGE = [Math]::round((($disk.FreeSpace/$disk.Size) * 100))
#Determine if disk needs to be flagged for warning or critical alert
If ($FreeSPACEPERCENTAGE -lt 10) { $Sheet.Cells.Item($row,8) = "Critical" $critical++ #Check to see if space is near empty and use appropriate background colors $range = $Sheet.Range(("A{0}" -f $row),("H{0}" -f $row)) $range.Select() | Out-Null #Critical threshold $range.Interior.ColorIndex = 3 } ElseIf ($FreeSPACEPERCENTAGE -lt 15) { $Sheet.Cells.Item($row,8) = "Warning" $range = $Sheet.Range(("A{0}" -f $row),("H{0}" -f $row)) $range.Select() | Out-Null $warning++ $range.Interior.ColorIndex = 6 } Else { $Sheet.Cells.Item($row,8) = "Good" $good++ }
$row++ } }
#Add a border for data cells $row-- $dataRange = $Sheet.Range(("A{0}" -f $initalRow),("H{0}" -f $row)) 7..12 | ForEach { $dataRange.Borders.Item($_).LineStyle = 1 $dataRange.Borders.Item($_).Weight = 2 }
#Auto fit everything so it looks better
$usedRange = $Sheet.UsedRange $usedRange.EntireColumn.AutoFit() | Out-Null
$critical $warning $good
$sheet = $excel.Worksheets.Item(1)
$row++;$row++
$beginChartRow = $Row
$Sheet.Cells.Item($row,$Column) = 'Critical' $Column++ $Sheet.Cells.Item($row,$Column) = 'Warning' $Column++ $Sheet.Cells.Item($row,$Column) = 'Good' $Column = 1 $row++ #Critical formula $Sheet.Cells.Item($row,$Column)=$critical $Column++ #Warning formula $Sheet.Cells.Item($row,$Column)=$warning $Column++ #Good formula $Sheet.Cells.Item($row,$Column)= $good
$endChartRow = $row
$chartRange = $Sheet.Range(("A{0}" -f $beginChartRow),("C{0}" -f $endChartRow))
##Add a chart to the workbook #Open a sheet for charts $temp = $sheet.Charts.Add() $temp.Delete() $chart = $sheet.Shapes.AddChart().Chart $sheet.Activate()
#Configure the chart ##Use a 3D Pie Chart $chart.ChartType = 70 $chart.Elevation = 40 #Give it some color $sheet.Shapes.Item("Chart 1").Fill.ForeColor.TintAndShade = .34 $sheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5 $sheet.Shapes.Item("Chart 1").Fill.BackColor.TintAndShade = .765 $sheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5
$sheet.Shapes.Item("Chart 1").Fill.TwoColorGradient(1,1)
#Set the location of the chart $sheet.Shapes.Item("Chart 1").Placement = 3 $sheet.Shapes.Item("Chart 1").Top = 30 $sheet.Shapes.Item("Chart 1").Left = 600
$chart.SetSourceData($chartRange) $chart.HasTitle = $True
$chart.ApplyLayout(6,69) $chart.ChartTitle.Text = "Disk Space Report" $chart.ChartStyle = 26 $chart.PlotVisibleOnly = $False $chart.SeriesCollection(1).DataLabels().ShowValue = $True $chart.SeriesCollection(1).DataLabels().Separator = ("{0}" -f [char]10)
$chart.SeriesCollection(1).DataLabels().Position = 2 #Critical $chart.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = 255 #Warning $chart.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = 65535 #Good $chart.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = 5287936
#Hide the data $chartRange.EntireRow.Hidden = $True $Sheet.UsedRange.EntireColumn.AutoFit()
#############################################################2nd Sheet
$Sheet = $Excel.Worksheets.Item(2) $sheet.Name = 'Backup' $Sheet.Activate() | Out-Null
$date = Get-Date -format f
$cells=$Sheet.Cells
#Create a Title for the first worksheet $row = 1 $Column = 1 $Sheet.Cells.Item($row,$column)= "Backup Report $date"
$range = $Sheet.Range("a1","h2") $range.Merge() | Out-Null $range.VerticalAlignment = -4160
#Give it a nice Style so it stands out $range.Style = 'Title'
#Increment row for next set of data $row++;$row++
#define some variables to control navigation $row=3 $col=1
#insert column headings
$cells.item($row,$col)="Instance Name" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="DATABASE NAME" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="RECOVERY MODEL" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="LAST FULL BACKUP" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="LAST Differential BACKUP" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="LAST LOG BACKUP" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="FULL BACKUP AGE(DAYS)" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="DIFF BACKUP AGE(DAYS)" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="LOG BACKUP AGE(HOURS)" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
# Load SMO extension [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
# Get List of sql servers to check $sqlservers = Get-Content 'C:\DBA_Weekly_Report\Servers\SQL_Servers_SQL.txt';
# Loop through each sql server from sqlservers.txt foreach($sqlserver in $sqlservers) { # Create an SMO Server object $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
$dbs = $srv.Databases
# For each jobs on the server foreach($db in $dbs) {
if ($db.Name -ne "tempdb") #We do not need the backup information for the tempdb database
{ $Reco_Model=$db.RecoveryModel.Tostring() $NumDaysSinceLastFullBackup = ((Get-Date) - $db.LastBackupDate).Days #We use Date Math to extract the number of days since the last full backup $NumDaysSinceLastLogBackup = ((Get-Date) - $db.LastLogBackupDate).TotalHours #Here we use TotalHours to extract the total number of hours $NumDaysSinceLastDiffBackup = ((Get-Date) - $db.LastDifferentialBackupDate).Days
if($db.LastBackupDate -eq "1/1/0001 12:00 AM") #This is the default dateTime value for databases that have not had any backups { $fullBackupDate="Never been backed up" $fgColor3="red" } else { $fullBackupDate="{0:g}" -f $db.LastBackupDate } if($db.LastDifferentialBackupDate -eq "1/1/0001 12:00 AM") #This is the default dateTime value for databases that have not had any backups { $DiffBackupDate="Never been backed up" $NumDaysSinceLastDiffBackup="Never been backed up" $fgColor3="red" } else { $DiffBackupDate="{0:g}" -f $db.LastDifferentialBackupDate $NumDaysSinceLastDiffBackup = ((Get-Date) - $db.LastDifferentialBackupDate).Days }
#We use the .ToString() Method to convert the value of the Recovery model to string and ignore Log backups for databases with Simple recovery model if ($db.RecoveryModel.Tostring() -eq "SIMPLE") { $logBackupDate="Simple Recovery Model" $NumDaysSinceLastLogBackup="Simple Recovery Model" } else { if($db.LastLogBackupDate -eq "1/1/0001 12:00 AM") { $logBackupDate="Never been backed up" } else { $logBackupDate= "{0:g}" -f $db.LastLogBackupDate }
}
#Define your service-level agreement in terms of days here if ($NumDaysSinceLastFullBackup -gt 7) { $fgColor = 3 } else { $fgColor = 0 }
# Success is Green $row++ $col=1
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$sqlserver $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$db.Name $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$Reco_Model $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$fullBackupDate $col++
#Set colour of cells for Disabled Jobs to Grey $cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$DiffBackupDate $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$logBackupDate $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$NumDaysSinceLastFullBackup $Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$NumDaysSinceLastDiffBackup $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$NumDaysSinceLastLogBackup
#Set teh Fill Colour for Time Cells
} }
}
$Sheet.UsedRange.EntireColumn.AutoFit()
#############################################################3rd Sheet
$Sheet = $Excel.Worksheets.Item(3) $sheet.Name = 'SQL_Server_Database_Properties ' $Sheet.Activate() | Out-Null
$date = Get-Date -format f
$cells=$Sheet.Cells
$Sheet.Cells.Item($row,$column)= "Database Properties Report $date"
$range = $Sheet.Range("a1","h2") $range.Merge() | Out-Null $range.VerticalAlignment = -4160
#Give it a nice Style so it stands out $range.Style = 'Title'
#Increment row for next set of data $row++;$row++
#define some variables to control navigation $row=3 $col=1
#insert column headings
$cells.item($row,$col)="Instance Name" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="DATABASE NAME" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="COLLATION" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="COMPATIBILITY LEVEL" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="AUTOSHRINK" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="RECOVERY MODEL" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="SIZE (MB)" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
$cells.item($row,$col)="SPACE AVAILABLE (MB)" $cells.item($row,$col).font.size=16 $cells.item($row,$col).HorizontalAlignment = -4108 $col++
# Load SMO extension [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
# Get List of sql servers to check $sqlservers = Get-Content 'C:\DBA_Weekly_Report\Servers\SQL_Servers_SQL.txt';
# Loop through each sql server from sqlservers.txt foreach($instance in $sqlservers) {
# Create an SMO connection to the instance $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$dbs = $s.Databases
#$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink,RecoveryModel, Size, SpaceAvailable
foreach($db in $dbs) {
#Divide the value of SpaceAvailable by 1KB $dbSpaceAvailable = $db.SpaceAvailable/1KB
#Format the results to a number with three decimal places $dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable
#Change the background color of the Cell depending on the AutoShrink property value if ($db.AutoShrink -eq "True") { $fgColor = 3 } else { $fgColor = 0 }
if ($db.RecoveryModel.Tostring() -eq "FULL") { $RecoveryModel="N/A"
} else
{ $RecoveryModel ="not applicabale" }
#Change the background color of the Cell depending on the SpaceAvailable property value if ($dbSpaceAvailable -lt 1.00) { $fgColor = 3 } else { $fgColor = 0 }
# Success is Green $row++ $col=1
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$instance $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$db.Name $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$db.COLLATION $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$db.CompatibilityLevel $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$db.AutoShrink $Sheet.Cells.item($intRow, 5).Interior.ColorIndex = $fgColor $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$db.RecoveryModel.Tostring() $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)="{0:N3}" -f $db.Size $col++
$cells.item($Row,$col).HorizontalAlignment = -4108 $cells.item($Row,$col)=$dbSpaceAvailable $Sheet.Cells.item($intRow, 8).Interior.ColorIndex = $fgColor
#Set teh Fill Colour for Time Cells
} }
$Sheet.UsedRange.EntireColumn.AutoFit()
$filename = "$DirectoryToSaveTo$filename.xlsx"
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$erroractionpreference = "SilentlyContinue" $Sheet.UsedRange.EntireColumn.AutoFit() $Excel.SaveAs($filename) #save as an XML Workbook (xslx) $Excel.Saved = $True $Excel.Close() $Excel.DisplayAlerts = $False $Excel.quit()
Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath) { #initate message $email = New-Object System.Net.Mail.MailMessage $email.From = $emailFrom $email.To.Add($emailTo) $email.Subject = $subject $email.Body = $body # initiate email attachment $emailAttach = New-Object System.Net.Mail.Attachment $filePath $email.Attachments.Add($emailAttach) #initiate sending email $smtp = new-object Net.Mail.SmtpClient($smtpServer) $smtp.Send($email) }
#Call Function
$message = @" Hi Team,
Please find attached excel sheet for Weekly report.
Autogenerated Email!!! Please do not reply.
Thank you.
"@ $date=get-date sendEmail -emailFrom $fromEmail -emailTo $ToEmail -subject "Weekly Reports -$($date)" -body $message -smtpServer $SMTPMail -filePath $filename
Regards, Shreyas K. Rane |
Tuesday, March 15, 2016 - 12:58:53 PM - Greg Robidoux | Back To Top (40943) |
Hi Neo, check out this tip: https://www.mssqltips.com/sqlservertip/1798/checking-sql-server-agent-jobs-using-windows-powershell/
|
Tuesday, March 15, 2016 - 12:28:56 PM - Neo | Back To Top (40941) |
Hi Sarmiento, I need power-shell script to show the status of the job (Run or failed or not scheduled) for the given date. In our environment we weekly do the checks for all the jobs successful for the entire week or any failure on any day. This is consuming lot of time. Can you please write a script which helps me a lot.
|
Wednesday, September 2, 2015 - 6:04:45 PM - Hintsa | Back To Top (38589) |
Hey guys, the PowerShell script is really helpful and we are using it to check missing backup. I have one issue though, for some of our database we take file group backup for the read_write file groups instead of full database backup; and this power shell script doesn't consider the file group backup that is being taken. So, is there any step where we can include to this script to take the file group backup into consideration??
Thanks for your help. |
Thursday, April 16, 2015 - 2:24:20 PM - mashrur shuvo | Back To Top (36952) |
$dbs | SELECT Name,LastBackupDate, LastLogBackupDate - this don't give you the right info for SQL Server hosted on VM and if the host is backed up by VM backup (snapshot) |
Monday, November 10, 2014 - 6:23:14 PM - Thayal | Back To Top (35246) |
select bme.physical_device_name from backupset bs INNER JOIN backupmediafamily bme ON bs.media_set_id = bme.media_set_id
How to get the physical_device_name using PowerShell ?
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)") $db = New-Object Microsoft.SqlServer.Management.Smo.Database $db = $srv.Databases.Item("MB01S") $bk = new-object Microsoft.SqlServer.Management.Smo.Backup
$bk. ???? How to get the hysical_device_name |
Sunday, October 12, 2014 - 1:05:12 AM - VENKATA M K BUMA | Back To Top (34936) |
Dear Edwin, the output A.D.2 for a log backup that has occured. $logBackupDate= "{0:g2}" -f $db.LastLogBackupDate I got correct o/p after change in the script $logBackupDate= "{0:g}" -f $db.LastLogBackupDate Please correct me if i am worng.
|
Wednesday, August 14, 2013 - 10:02:45 AM - RangerEl | Back To Top (26305) | |
Hi everyone. trying to use both versions of the script as above , but get the same output for both. I hope some one can explain the issue to me.
In the LAST LOG BACKUP column on the spread sheet I get the output A.D.2 for a log backup that has occured. The Age in hours column does show when the last log backup was. It is the output for everyone database where the recovery model is not Simple. So for Full or Bulk Logged it records A.D.2 in the excel spreadsheet.
Can anyone tell me what this is and how I correct it ? Thank you. |
Thursday, October 18, 2012 - 10:38:19 AM - Ed - sqlscripter | Back To Top (19980) |
Guys this is GREAT !!! I am so glad I do not have to re write this. I wrote excel \ COM using multi lauguages long ago. I used ASP, VB Script and in some script WMI. This is my version of auto inserting into excel the members of the sysadmin role. There is a line of code at the bottom commented out to set a password on the xls file I found up at the Scripting Guy site and included it. I also did this as part of my SQL PASS Live presenter July Lecture years ago um about 2006 maybe. Since I learned coding on my own this took some effort.
'Found an idea and small amount of code, searched the "Hey Scripting Guy" Website '* Property of : TEAMSQL
Dim i ' making the connection to your sql server objConnection.Open _
' The query goes here objRecordSet.MoveFirst Do Until objRecordset.EOF ' This is setting the column names, font, colors, etc. ' Now we are getting the data and highlighting certain columns
'Below is the SAve path to edit and if you wish to not password protect uncomment the line below Wend ThisFile.Close
|
Wednesday, June 20, 2012 - 3:00:38 PM - Dave Bowers | Back To Top (18137) |
This is a great shell. I just have one question. Is there a way to center the text in a cell?
|
Wednesday, September 21, 2011 - 3:23:01 PM - NJ-DBA | Back To Top (14717) |
The following includes some modifications to use a prod server list and a dev/stage server list with different thresholds for each... just thought I'd share this your code was quite good.
#Create a new Excel object using COM $ErrorActionPreference = “silentlycontinue” $Excel = New-Object -ComObject Excel.Application $Excel.visible = $False $Excel.DisplayAlerts = $false #$Excel.DisplayAlerts = $true $Sheet = $ExcelWorkbooks.Worksheets.Item(1) #$date = get-date -uformat “%Y%m%d” $date = ( get-date ).ToString(‘yyyy/MM/dd’) $save = “C:\Data\backups\DatabaseBackup_Report.xls” #Counter variable for rows $intRow = 2 #Read the contents of the Servers.txt file #foreach ($instance in get-content “serverlist.txt”)
##################Loop in all your sqlserver instances######################### foreach ($instance in get-content “C:\Data\backups\prodserverlist.txt”) { #Create column headers $Sheet.Cells.Item($intRow,2) = $instance $Sheet.Cells.Item($intRow,1).Font.Bold = $True $Sheet.Cells.Item($intRow,2).Font.Bold = $True
$intRow++
$Sheet.Cells.Item($intRow,1) = “DATABASE NAME” $Sheet.Cells.Item($intRow,2) = “LAST FULL BACKUP” $Sheet.Cells.Item($intRow,3) = “LAST LOG BACKUP” $Sheet.Cells.Item($intRow,4) = “FULL BACKUP AGE(DAYS)” $Sheet.Cells.Item($intRow,5) = “LOG BACKUP AGE(HOURS)”
#Format the column headers for ($col = 1; $col –le 5; $col++) { $Sheet.Cells.Item($intRow,$col).Font.Bold = $True $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 50 $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 36 }
$intRow++ ####################################################### #This script gets SQL Server database information using PowerShell
# Create an SMO connection to the instance $s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance
$dbs = $s.Databases #Formatting using Excel ForEach ($db in $dbs) { if ($db.Name -ne “tempdb”) #We do not need the backup information for the tempdb database { #We use Date Math to extract the number of days since the last full backup $NumDaysSinceLastFullBackup = ((Get-Date) – $db.LastBackupDate).Days #Here we use TotalHours to extract the total number of hours $NumDaysSinceLastLogBackup = ((Get-Date) – $db.LastLogBackupDate).TotalHours if($db.LastBackupDate -eq “1/1/0001 12:00 AM”) #This date is a start of Sqlserver infra. $fullBackupDate=”Never been backed up” } else { $fullBackupDate=”{0:g}” -f $db.LastBackupDate $Sheet.Cells.Item($intRow, 1) = $db.Name #Use the .ToString() Method to convert the value of the Recovery model to string and ignore Log #backups for databases with Simple recovery model if ($db.RecoveryModel.Tostring() -eq “SIMPLE”) $logBackupDate=”SIMPLE” } { #if($db.LastLogBackupDate -eq “1/1/2011 12:00 AM”) else { } $Sheet.Cells.Item($intRow, 3) = $logBackupDate #Define your service-level agreement in terms of days here. if ($NumDaysSinceLastFullBackup -gt 0) { $fgColor = 3 } else { $fgColor = 50 } $Sheet.Cells.Item($intRow, 4) = $NumDaysSinceLastFullBackup }
$ExcelWorkbooks.SaveAs($save) <##################################### START SECOND SHEET-DEV/STAGE SERVERS
$Sheet = $ExcelWorkbooks.Worksheets.Item(2) $intRow = 3 #Read the contents of the Servers.txt file #foreach ($instance in get-content “serverlist.txt”)
##################Loop in all your sqlserver instances######################### foreach ($instance in get-content “C:\Data\backups\devstageserverlist.txt”) { #Create column headers $Sheet.Cells.Item(1,1) = “NOTE: CELLS MARKED RED HAVE A LAST BACKUP DATE OLDER THAN THE PRESET THRESHOLD ” $Sheet.Cells.Item($intRow,2) = $instance $Sheet.Cells.Item($intRow,1).Font.Bold = $True $Sheet.Cells.Item($intRow,2).Font.Bold = $True
$intRow++
$Sheet.Cells.Item($intRow,1) = “DATABASE NAME” $Sheet.Cells.Item($intRow,2) = “LAST FULL BACKUP” $Sheet.Cells.Item($intRow,3) = “LAST LOG BACKUP” $Sheet.Cells.Item($intRow,4) = “FULL BACKUP AGE(DAYS)” $Sheet.Cells.Item($intRow,5) = “LOG BACKUP AGE(HOURS)”
#Format the column headers for ($col = 1; $col –le 5; $col++) { $Sheet.Cells.Item($intRow,$col).Font.Bold = $True $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 50 $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 36 }
$intRow++ ####################################################### $s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance
$dbs = $s.Databases #Formatting using Excel ForEach ($db in $dbs) { if ($db.Name -ne “tempdb”) #We do not need the backup information for the tempdb database { #We use Date Math to extract the number of days since the last full backup $NumDaysSinceLastFullBackup = ((Get-Date) – $db.LastBackupDate).Days #Here we use TotalHours to extract the total number of hours $NumDaysSinceLastLogBackup = ((Get-Date) – $db.LastLogBackupDate).TotalHours if($db.LastBackupDate -eq “1/1/0001 12:00 AM”) #This date is a start of Sqlserver infra. $fullBackupDate=”Never been backed up” } else { $fullBackupDate=”{0:g}” -f $db.LastBackupDate $Sheet.Cells.Item($intRow, 1) = $db.Name #Use the .ToString() Method to convert the value of the Recovery model to string and ignore Log #backups for databases with Simple recovery model if ($db.RecoveryModel.Tostring() -eq “SIMPLE”) $logBackupDate=”SIMPLE” } { #if($db.LastLogBackupDate -eq “1/1/2011 12:00 AM”) else { } $Sheet.Cells.Item($intRow, 3) = $logBackupDate #Define your service-level agreement in terms of days here. if ($NumDaysSinceLastFullBackup -gt 7) { $fgColor = 3 } else { $fgColor = 50 } $Sheet.Cells.Item($intRow, 4) = $NumDaysSinceLastFullBackup }
$ExcelWorkbooks.SaveAs($save) |
Thursday, October 22, 2009 - 6:40:43 PM - bass_player | Back To Top (4282) |
The Server object has the Edition property that you can use to retrieve the Edition. You can also use the Version, VersionMajor, VersionMinor and VersionString properties for the version [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null Write-Host "Edition" $s.Edition Write-Host "Version" $s.Version Write-Host "VersionMajor" $s.VersionMajor Write-Host "VersionMinor" $s.VersionMinor Write-Host "VersionString" $s.VersionString
For the disk space, it's a simple call to the Win32_WmiObject.The script below can be written in a single line. If you want to read thru a list of computers and list all the logical drives, you can use the -computer parameter in the Get-WmiObject and read thru a text file or something Get-WmiObject Win32_LogicalDisk -filter "DriveType=3" | Select DeviceID, @{Name=”Size(GB)”;Expression={”{0:N1}” -f($_.size/1GB)}},@{Name=”Free Space(GB)”;Expression={”{0:N1}” -f($_.freespace/1GB)}}
|
Wednesday, August 26, 2009 - 2:29:11 PM - abcdsql | Back To Top (3965) |
hi, i have seen ur code. its really gud. is there any chance you can help me out in adding sql server versions+edition and available disk space to this powershell script...? i need it urgent pls.. can any one help |
Friday, July 31, 2009 - 4:31:47 AM - tadewusi | Back To Top (3823) |
Is it possible to tweak the script for non-english region, especially for excel section? I understand changing the regional settings via the control panel will work, but I looking for another way as I don't want to change this setting on my workstation. |
Friday, July 31, 2009 - 4:27:00 AM - tadewusi | Back To Top (3821) |
The recoverymodel is returning NULL thereby causing the line below to fail. if ($db.RecoveryModel.Tostring() -eq "SIMPLE") But removing ToString() function still returns NULL for the recoverymodel. Is there a workaround for this please? |
Thursday, July 2, 2009 - 8:01:58 PM - Graham Okely | Back To Top (3687) |
This is a nice tip thanks.... however.... I have had a SQL Server installation report that it was backing up but no files were created in the folders designated for the backups. A suggestion: Can you modify the PowerShell script to report the last date of the log and .bak file from the actual files in the folders? Thanks |