Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Check the Last SQL Server Backup Date using Windows PowerShell


By:   |   Read Comments (16)   |   Related Tips: More > PowerShell

Attend these FREE MSSQLTips webcasts >> click to register


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.

#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)

#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}" - $db.LastBackupDate
       }
   
       $Sheet.Cells.Item
($intRow1$db.Name
       $Sheet.Cells.Item
($intRow2$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
($intRow3$logBackupDate
   
       #Define your service
-level agreement in terms of days here
       
if ($NumDaysSinceLastFullBackup -gt 1)
       
{
           $fgColor 
3
       }
       
else
       
{
           $fgColor 
0
       }
   
       $Sheet.Cells.Item
($intRow4$NumDaysSinceLastFullBackup
       $Sheet.Cells.item
($intRow4).Interior.ColorIndex $fgColor
   
       $Sheet.Cells.Item
($intRow5=  $NumDaysSinceLastLogBackup
   
           
       $intRow 
++
   
       
}
   }
   $intRow 
++


}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls

Next Steps

  • Try converting your T-SQL DBA scripts to Windows PowerShell
  • Read more on the SMO Class Library to translate SQL Server objects that we are already familiar with to SMO objects
  • Here are more PowerShell tips
  • Here are more Backup tips


Last Update:


signup button

next tip button



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, December 22, 2016 - 8:07:51 AM - Shreyas Rane Back To Top

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="xyz@email.com" 

$ToEmail="xyz@email.com" 

$SMTPMail="xyz@email.com" 

 

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

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

 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 02, 2015 - 6:04:45 PM - Hintsa Back To Top

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

$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

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

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

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

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
'until I found or figured out how to get many additional properties of the excel.Application
'to work and actually save, timestamp the xls file, password protect, autoclose and make not
'visible and format the sheet to meet my standards.
'As Usual Do not use unless you have tested this completely
'Edward J Pochinski III 07/19/2005
'USAGE: call or execute the vb script
'Edit server name and path to save .xls file to

'* Property of  : TEAMSQL
'* This script should be run by a DBA                     
'* Use at your own Risk Author:Edward J Pochinski III                   
'* email: sqlscripters@hotmail.com                         
'* Usage: Drag and drop a txt file with server names in a single column onto this script
'* The script will generate a xls file with worthy information
'*
'*                                
'*                                                                  
'* THE AUTHOR MAKES NO GUARANTEES OR WARRANTIES, EXPRESS, IMPLIED OR OF ANY     
'* OTHER KIND TO THIS CODE OR ANY USER MODIFICATIONS                            
'* DO NOT USE IN A PRODUCTION ENVIRONMENT UNTIL YOU HAVE TESTED IN A SECURED LAB
'* ENVIRONMENT. USE AT YOUR OWN RISK.                                           
 
Dim Shell
Dim System
Set Shell = CreateObject("Wscript.Shell")
Set System = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1
Const ForWriting = 2
Const adOpenStatic = 3
Const adLockOptimistic = 3
Call doExcel(GetUserInput)
Call Cleanup()


Function GetUserInput
    If Wscript.Arguments.Count = 0 Then
       wscript.echo "You need to drop a text file with anyname containing server names"& vbNewLine _
           &  "in a single column that this script will run against and generate a xls file."& vbNewLine _
           & "      The SQLSCRIPTERS "
    Else
       GetUserInput = Wscript.Arguments(0)
    End If
    If GetUserInput = "" Then Call Cleanup()
    If Not System.FileExists(GetUserinput) Then
       MsgBox GetUserInput & " is not an existing file on" &_
              " your system."
       Shell.Run "wscript """ & Wscript.ScriptFullName & """"
       Call Cleanup()
    End If
End Function


Sub doExcel(Input)
    Dim ThisFile
    Dim NewFile
    Dim txtline
    Set ThisFile = System.OpenTextFile(Input,ForReading)
    While Not ThisFile.AtEndOfStream
txtline = ThisFile.ReadLine
arrinput = Split(txtline, ":")
server = arrinput(0)
user = arrinput(1)
pass = arrinput(2)
app = arrinput(3)

Dim i

' making the connection to your sql server
' change yourservername to match your server
'where source = sqlshark for objConnection.Open property
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = SQLOLEDB;Data Source="&server&";" & _
"UID="&user&";PWD="&pass&";Initial Catalog=master;"
'        "Trusted_Connection=Yes;Initial Catalog=master;"
'The above oLe DB DNS less connection strings are for user id and pass
'and the comment one for trusted connection can be used on the fly and move to under code


' creating the Excel object application
Set objExcel = CreateObject("Excel.Application")
'Change to false to run as background
objExcel.Visible = True
objExcel.DisplayAlerts = FALSE
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Name = "Priveledged Id"

' The query goes here
objRecordSet.Open "select [name],loginname,sysadmin,serveradmin,hasaccess,dbname,Createdate=Convert(varchar,createdate,100),[Updatedate]=Convert(varchar,updatedate,109) from syslogins where sysadmin = '1' or serveradmin = '1'", _
        objConnection, adOpenStatic, adLockOptimistic
i = 1

objRecordSet.MoveFirst

Do Until objRecordset.EOF
 i = i + 1

' This is setting the column names, font, colors, etc.
' This code can be simplified by ranging if desired.
 objExcel.Cells(1, 1).Value = "Name"
 objExcel.Cells(1, 1).Font.Size = 10
 objExcel.Cells(1, 1).Font.Bold = TRUE
 objExcel.Cells(1, 1).Interior.ColorIndex = 6
 objExcel.Cells(1, 2).Value = "loginname"
 objExcel.Cells(1, 2).Font.Size = 10
 objExcel.Cells(1, 2).Font.Bold = TRUE
 objExcel.Cells(1, 2).Interior.ColorIndex = 6
 objExcel.Cells(1, 3).Value = "SysAdmin"
 objExcel.Cells(1, 3).Font.Size = 10
 objExcel.Cells(1, 3).Font.Bold = TRUE
 objExcel.Cells(1, 3).Interior.ColorIndex = 6
 objExcel.Cells(1, 4).Value = "ServerAdmin"
 objExcel.Cells(1, 4).Font.Size = 10
 objExcel.Cells(1, 4).Font.Bold = TRUE
 objExcel.Cells(1, 4).Interior.ColorIndex = 6
 objExcel.Cells(1, 5).Value = "HasAccess"
 objExcel.Cells(1, 5).Font.Size = 10
 objExcel.Cells(1, 5).Font.Bold = TRUE
 objExcel.Cells(1, 5).Interior.ColorIndex = 6
 objExcel.Cells(1, 6).Value = "DbName"
 objExcel.Cells(1, 6).Font.Size = 10
 objExcel.Cells(1, 6).Font.Bold = TRUE
 objExcel.Cells(1, 6).Interior.ColorIndex = 6
 objExcel.Cells(1, 7).Value = "CreateDate"
 objExcel.Cells(1, 7).Font.Size = 10
 objExcel.Cells(1, 7).Font.Bold = TRUE
 objExcel.Cells(1, 7).Interior.ColorIndex = 6
 objExcel.Cells(1, 8).Value = "updatedate"
 objExcel.Cells(1, 8).Font.Size = 10
 objExcel.Cells(1, 8).Font.Bold = TRUE
 objExcel.Cells(1, 8).Interior.ColorIndex = 6
 objExcel.Cells(1, 9).Value = "Application"
 objExcel.Cells(1, 9).Font.Size = 10
 objExcel.Cells(1, 9).Font.Bold = TRUE
 objExcel.Cells(1, 9).Interior.ColorIndex = 6
 objExcel.Cells(1, 10).Value = "Server"
 objExcel.Cells(1, 10).Font.Size = 10
 objExcel.Cells(1, 10).Font.Bold = TRUE
 objExcel.Cells(1, 10).Interior.ColorIndex = 6
 objExcel.Cells(1, 11).Value = "BAM"
 objExcel.Cells(1, 11).Font.Size = 10
 objExcel.Cells(1, 11).Font.Bold = TRUE
 objExcel.Cells(1, 11).Interior.ColorIndex = 6
 objExcel.Cells(1, 12).Value = "Server Type"
 objExcel.Cells(1, 12).Font.Size = 10
 objExcel.Cells(1, 12).Font.Bold = TRUE
 objExcel.Cells(1, 12).Interior.ColorIndex = 6     

' Now we are getting the data and highlighting certain columns
 objExcel.Cells(i, 1).Value = objRecordset.Fields.Item("Name")
 objExcel.Cells(i, 1).Font.Size = 10
 objExcel.Cells(i, 1).Font.Bold = TRUE
 'objExcel.Cells(i, 1).Font.ColorIndex = 49
 objExcel.Cells(i, 1).Interior.ColorIndex = 34
 objExcel.Cells(i, 1).Borders.LineStyle = True
 objExcel.Cells(i, 2).Value = objRecordset.Fields.Item("loginname")
 objExcel.Cells(i, 2).Font.Size = 10
 objExcel.Cells(i, 2).Interior.ColorIndex = 35
 objExcel.Cells(i, 2).Borders.LineStyle = True
 objExcel.Cells(i, 3).Value = objRecordset.Fields.Item("SysAdmin")
 objExcel.Cells(i, 3).Font.Size = 10
 objExcel.Cells(i, 3).Interior.ColorIndex = 35
 objExcel.Cells(i, 3).Borders.LineStyle = True
 objExcel.Cells(i, 4).Value = objRecordset.Fields.Item("ServerAdmin")
 objExcel.Cells(i, 4).Font.Size = 10
 objExcel.Cells(i, 4).Interior.ColorIndex = 35
 objExcel.Cells(i, 4).Borders.LineStyle = True
 objExcel.Cells(i, 5).Value = objRecordset.Fields.Item("HasAccess")
 objExcel.Cells(i, 5).Font.Size = 10
 objExcel.Cells(i, 5).Interior.ColorIndex = 35
 objExcel.Cells(i, 5).Borders.LineStyle = True
 objExcel.Cells(i, 6).Value = objRecordset.Fields.Item("DbName")
 objExcel.Cells(i, 6).Font.Size = 10
 objExcel.Cells(i, 6).Interior.ColorIndex = 35
 objExcel.Cells(i, 6).Borders.LineStyle = True
 objExcel.Cells(i, 7).Value = objRecordset.Fields.Item("CreateDate")
 objExcel.Cells(i, 7).Font.Size = 10
 objExcel.Cells(i, 7).Interior.ColorIndex = 35
 objExcel.Cells(i, 7).Borders.LineStyle = True
 objExcel.Cells(i, 8).Value = objRecordset.Fields.Item("updatedate")
 objExcel.Cells(i, 8).Font.Size = 10
 objExcel.Cells(i, 8).Interior.ColorIndex = 35
 objExcel.Cells(i, 8).Borders.LineStyle = True
 objExcel.Cells(i, 9).Value = (arrinput(3))
 objExcel.Cells(i, 9).Font.Size = 10
 objExcel.Cells(i, 9).Interior.ColorIndex = 35
 objExcel.Cells(i, 9).Borders.LineStyle = True
 objExcel.Cells(i, 10).Value = (arrinput(0))
 objExcel.Cells(i, 10).Font.Size = 10
 objExcel.Cells(i, 10).Interior.ColorIndex = 35
 objExcel.Cells(i, 10).Borders.LineStyle = True
 objExcel.Cells(i, 11).Value = (arrinput(4))
 objExcel.Cells(i, 11).Font.Size = 10
 objExcel.Cells(i, 11).Interior.ColorIndex = 35
 objExcel.Cells(i, 11).Borders.LineStyle = True
 objExcel.Cells(i, 12).Value = (arrinput(5))
 objExcel.Cells(i, 12).Font.Size = 10
 objExcel.Cells(i, 12).Interior.ColorIndex = 35
 objExcel.Cells(i, 12).Borders.LineStyle = True
 
 
 objRecordset.MoveNext
 
 objExcel.Range("A1:K1").Borders.LineStyle = True
 
Loop


' automatically set todays date to append filename
Set objRange = objWorksheet.UsedRange
objRange.EntireColumn.Autofit()
Dim strFilename
DIM CurrentYear
DIM CurrentMonth
Dim CurrentDay
DIM TimeStamp2
DIM DbName
Dim strScriptPath
CurrentYear = DatePart("yyyy", Date)
CurrentMonth = DatePart("M",Date)
Currentday = DatePart("D",Date)
IF CurrentMonth < 10 then
CurrentMonth = "0"&CurrentMonth
TimeStamp2 = CurrentMonth & CurrentDay & CurrentYear
Else
TimeStamp2 = CurrentMonth & CurrentDay & CurrentYear
END If

'Below is the SAve path to edit and if you wish to not password protect uncomment the line below
'and comment out the one below that
objWorkbook.SaveAs("C:\Docs\seminar\July_LivePresenter\exel_automation\"&server&"_PrivId_"&TimeStamp2&".xls")
'objWorkBook.SaveAs"C:\Docs\panasonic\userID_reval\PNA_ID_082006\"&txtline&"_PrivId_"&TimeStamp2&".xls",,"sqlpassword"
'Remove or add comment below to auto close excel or leave open
objExcel.Quit
' cleaning up
objRecordSet.Close
objConnection.Close

Wend
   

    ThisFile.Close
    
    
    Wscript.Sleep 5000
    
End Sub


Sub Cleanup
    Set Shell = Nothing
    Set System = Nothing
    'Set NewFile = Nothing
    Set ThisFile = Nothing
    Wscript.Quit
End Sub

 


Wednesday, June 20, 2012 - 3:00:38 PM - Dave Bowers Back To Top

 

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

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.

 


### Code that can be used to Monitor all you Sql Instances Backups from One Location


##FIRST THING WE DO IS LOAD THE SMO Objects
 [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null

 

#Create a new Excel object using COM

$ErrorActionPreference = “silentlycontinue”

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $False

$Excel.DisplayAlerts = $false
#$Excel.visible = $true

#$Excel.DisplayAlerts = $true
$ExcelWorkbooks = $Excel.Workbooks.Add()

$Sheet = $ExcelWorkbooks.Worksheets.Item(1)
$sheet.name = "Production Servers"
#$MonitorBody = “D:\PowerShell\PScripts\Mail.htm”

#$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(1,1) = “NOTE: CELLS MARKED RED HAVE A LAST BACKUP DATE OLDER THAN THE PRESET THRESHOLD ”
$Sheet.Cells.Item(2,1) = “THRESHOLD IS GREATER THAN 24 HOURS FOR PROD, GREATER THAN 7 DAYS FOR DEV/STAGE ”
$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 = 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.
#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
$fgColor3=”green”

#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”
$NumDaysSinceLastLogBackup=”SIMPLE”

}
else

{

#if($db.LastLogBackupDate -eq “1/1/2011 12:00 AM”)
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 0)

{

$fgColor = 3

}

else

{

$fgColor = 50

}

$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()

$ExcelWorkbooks.SaveAs($save)

<#####################################

START SECOND SHEET-DEV/STAGE SERVERS


#########################################>

$Sheet = $ExcelWorkbooks.Worksheets.Item(2)
$sheet.name = "DEVandSTAGEServers"

$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,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 = 50

$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 36

}

 

$intRow++

#######################################################

 
# 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.
#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
$fgColor3=”green”

#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”
$NumDaysSinceLastLogBackup=”SIMPLE”

}
else

{

#if($db.LastLogBackupDate -eq “1/1/2011 12:00 AM”)
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 7)

{

$fgColor = 3

}

else

{

$fgColor = 50

}

$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()

$ExcelWorkbooks.SaveAs($save)
$Excel.quit()


Thursday, October 22, 2009 - 6:40:43 PM - bass_player Back To Top

 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
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2000"

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

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

 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

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 02, 2009 - 8:01:58 PM - Graham Okely Back To Top

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


Learn more about SQL Server tools