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

 

Script to obtain most recent SQL Server backup information for all databases


By:   |   Last Updated: 2018-10-22   |   Comments (1)   |   Related Tips: More > Backup

Problem

SQL Server database backups are one of the most important pillars within our profession. Since they hold the most valuable assets of our customers, their information, we must be able to be certain of their availability in case they are eventually required.

Remember that a database backup has a wide variety of uses:

  • To recover from a disaster
  • To refresh a development/test environment with production data
  • For auditing purposes
  • To have the ability to retain information, for whatever reason

In this tip we look at a script you can use to make sure your databases are being backed up in a timely manner.

Solution

I will be presenting a script that helps you visualize important information for all the databases in the SQL Server instance under your care, so you can build a custom solution around it.

Script to obtain most recent database backup information for a SQL Server instance

I created a couple of CTEs in the code below to gather the information.  Here is some information on what these do.

  • MostRecentBackupsCTE
    • In here I simply build a result set that contains the classic backups trident (Full, Differential and Transaction Log) for each database.
    • For each backup type, for each database, only the latest one is returned (that’s why I apply a MAX function on the backup_finish_date).
  • BackupsWithSize CTE
    • From the result set obtained from the MostRecentBackups CTE, I simply append the size of each particular backup (because eventually it is a very important piece of information to have).

To present the information in a legible format, I simply append the information from the BackupsWithSize CTE to the list of databases returned by the sys.databases table.  This excludes the tempdb and any existing database snapshot, for the obvious reason that neither can’t be backed up.

The backup size is shown in GBs, but you can tweak the code below if you want to change the output.

WITH MostRecentBackups
   AS(
      SELECT 
         database_name AS [Database],
         MAX(bus.backup_finish_date) AS LastBackupTime,
         CASE bus.type
            WHEN 'D' THEN 'Full'
            WHEN 'I' THEN 'Differential'
            WHEN 'L' THEN 'Transaction Log'
         END AS Type
      FROM msdb.dbo.backupset bus
      WHERE bus.type <> 'F'
      GROUP BY bus.database_name,bus.type
   ),
   BackupsWithSize
   AS(
      SELECT mrb.*, (SELECT TOP 1 CONVERT(DECIMAL(10,4), b.backup_size/1024/1024/1024) AS backup_size FROM msdb.dbo.backupset b WHERE [Database] = b.database_name AND LastBackupTime = b.backup_finish_date) AS [Backup Size]
      FROM MostRecentBackups mrb
   )
   
   SELECT 
      SERVERPROPERTY('ServerName') AS Instance, 
      d.name AS [Database],
      d.state_desc AS State,
      d.recovery_model_desc AS [Recovery Model],
      bf.LastBackupTime AS [Last Full],
      DATEDIFF(DAY,bf.LastBackupTime,GETDATE()) AS [Time Since Last Full (in Days)],
      bf.[Backup Size] AS [Full Backup Size],
      bd.LastBackupTime AS [Last Differential],
      DATEDIFF(DAY,bd.LastBackupTime,GETDATE()) AS [Time Since Last Differential (in Days)],
      bd.[Backup Size] AS [Differential Backup Size],
      bt.LastBackupTime AS [Last Transaction Log],
      DATEDIFF(MINUTE,bt.LastBackupTime,GETDATE()) AS [Time Since Last Transaction Log (in Minutes)],
      bt.[Backup Size] AS [Transaction Log Backup Size]
   FROM sys.databases d
   LEFT JOIN BackupsWithSize bf ON (d.name = bf.[Database] AND (bf.Type = 'Full' OR bf.Type IS NULL))
   LEFT JOIN BackupsWithSize bd ON (d.name = bd.[Database] AND (bd.Type = 'Differential' OR bd.Type IS NULL))
   LEFT JOIN BackupsWithSize bt ON (d.name = bt.[Database] AND (bt.Type = 'Transaction Log' OR bt.Type IS NULL))
   WHERE d.name <> 'tempdb' AND d.source_database_id IS NULL

In the end, you will see an output that has the following shape. Since the result set is a bit wide I will present 2 images, but it is the exact same result set.

latest database backup information
latest database backup information

It is very important to consider the fact that this will all be useless if there is a purging mechanism over your msdb database that removes information from the backupset table and wipes the entire record set. It will work just fine if there is a defined retention period that leaves the information untouched for at least what is needed for the latest backups.

*This script has been tested in the following SQL Server versions: 2005, 2008, 2008 R2, 2012, 2014, 2016 and 2017.

Bonus PowerShell Method to get backup information

Here’s a PowerShell script that you might be able to use to connect to a set of SQL Server instances under your care, fetch the backups information from each and store it in a central database you own/manage for a quick overview of the backups within your whole environment.

  • Just remember to substitute XXX with the values that fit to your particular case.
  • You can also change the name of the variables as you wish.
  • In the final insert of the collected information, I specify the timestamp when the backup data is being collected (it might be useful to know from when is this information).
    • Just so you take it into account when creating the table to store the information.
$labServer = "XXX"
$inventoryDB = "XXX"

#Clean the BackupStatus table
#This is where the information of all the backups from all the instances will be stored
#You can omit the TRUNCATE statement if you would like to keep all the information every single time you collect it from each instance.
Invoke-Sqlcmd -Query "TRUNCATE TABLE XXX" -Database $inventoryDB -ServerInstance $labServer

#Fetch all the instances with the respective SQL Server Version
$instanceLookupQuery = /* Put in here the query that will return the list of instances under your support, ideally you already have a Master Server List to work with */

$instances = Invoke-Sqlcmd -ServerInstance $labServer -Database $inventoryDB -Query $instanceLookupQuery

$backupsQuery = "
WITH MostRecentBackups
   AS(
      SELECT 
         database_name AS [Database],
         MAX(bus.backup_finish_date) AS LastBackupTime,
         CASE bus.type
            WHEN 'D' THEN 'Full'
            WHEN 'I' THEN 'Differential'
            WHEN 'L' THEN 'Transaction Log'
         END AS Type
      FROM msdb.dbo.backupset bus
      WHERE bus.type <> 'F'
      GROUP BY bus.database_name,bus.type
   ),
   BackupsWithSize
   AS(
      SELECT mrb.*, (SELECT TOP 1 CONVERT(DECIMAL(10,4), b.backup_size/1024/1024/1024) AS backup_size FROM msdb.dbo.backupset b WHERE [Database] = b.database_name AND LastBackupTime = b.backup_finish_date) AS [Backup Size]
      FROM MostRecentBackups mrb
   )
   
   SELECT 
      SERVERPROPERTY('ServerName') AS Instance, 
      d.name AS [Database],
      d.state_desc AS State,
      d.recovery_model_desc AS [Recovery Model],
      bf.LastBackupTime AS [Last Full],
      DATEDIFF(DAY,bf.LastBackupTime,GETDATE()) AS [Time Since Last Full (in Days)],
      bf.[Backup Size] AS [Full Backup Size],
      bd.LastBackupTime AS [Last Differential],
      DATEDIFF(DAY,bd.LastBackupTime,GETDATE()) AS [Time Since Last Differential (in Days)],
      bd.[Backup Size] AS [Differential Backup Size],
      bt.LastBackupTime AS [Last Transaction Log],
      DATEDIFF(MINUTE,bt.LastBackupTime,GETDATE()) AS [Time Since Last Transaction Log (in Minutes)],
      bt.[Backup Size] AS [Transaction Log Backup Size]
   FROM sys.databases d
   LEFT JOIN BackupsWithSize bf ON (d.name = bf.[Database] AND (bf.Type = 'Full' OR bf.Type IS NULL))
   LEFT JOIN BackupsWithSize bd ON (d.name = bd.[Database] AND (bd.Type = 'Differential' OR bd.Type IS NULL))
   LEFT JOIN BackupsWithSize bt ON (d.name = bt.[Database] AND (bt.Type = 'Transaction Log' OR bt.Type IS NULL))
   WHERE d.name <> 'tempdb' AND d.source_database_id IS NULL
"

#For each instance, grab the backups information for all the databases
foreach ($instance in $instances){
   #Go grab the backups information for all the databases in the instance
   Write-Host "Fetching Backups information for databases on instance" $instance.instance
   $results = Invoke-Sqlcmd -Query $backupQuery -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30

   #Perform the INSERT in the BackupStatus table only if it returned at least 1 row
    if($results.Length -ne 0){

      #Build the insert statement
      $insert = "INSERT INTO XXX VALUES"
      foreach($result in $results){
            if($result['Last Full'].ToString().trim() -eq [String]::Empty){$LastFull = "''"} else{$LastFull = $result['Last Full'] }
            if($result['Time Since Last Full (in Days)'].ToString().trim() -eq [String]::Empty){$TimeSinceLastFull = "''"} else{$TimeSinceLastFull = $result['Time Since Last Full (in Days)']}
            if($result['Full Backup Size'].ToString().trim() -eq [String]::Empty){$FullBackupSize = "''"} else{$FullBackupSize = $result['Full Backup Size']}
            if($result['Last Differential'].ToString().trim() -eq [String]::Empty){$LastDifferential = "''"} else{$LastDifferential = $result['Last Differential'] }
            if($result['Time Since Last Differential (in Days)'].ToString().trim() -eq [String]::Empty){$TimeSinceLastDifferential = "''"} else{$TimeSinceLastDifferential = $result['Time Since Last Differential (in Days)']}
            if($result['Differential Backup Size'].ToString().trim() -eq [String]::Empty){$DifferentialBackupSize = "''"} else{$DifferentialBackupSize = $result['Differential Backup Size']}
            if($result['Last Transaction Log'].ToString().trim() -eq [String]::Empty){$LastTransactionLog = "''"} else{$LastTransactionLog = $result['Last Transaction Log'] }
            if($result['Time Since Last Transaction Log (in Minutes)'].ToString().trim() -eq [String]::Empty){$TimeSinceLastTransactionLog = "''"} else{$TimeSinceLastTransactionLog = $result['Time Since Last Transaction Log (in Minutes)']}
            if($result['Transaction Log Backup Size'].ToString().trim() -eq [String]::Empty){$TransactionLogBackupSize = "''"} else{$TransactionLogBackupSize = $result['Transaction Log Backup Size']}

         $insert += "
         (
         '"+$result['Instance']+"',
         '"+$result['Database']+"',
         '"+$result['State']+"',
         '"+$result['Recovery Model']+"',
         '"+$LastFull+"',
            "+$TimeSinceLastFull+",
         "+$FullBackupSize+",
            '"+$LastDifferential+"',
            "+$TimeSinceLastDifferential+",
         "+$DifferentialBackupSize+",
            '"+$LastTransactionLog+"',
            "+$TimeSinceLastTransactionLog+",
         "+$TransactionLogBackupSize+",
            GETDATE()
         ),
         "
            $insert = $insert -replace "''",'NULL'
            $insert = $insert -replace "NULLNULL",'NULL'
      }

      #Store the results in the table from your central database where you will have the information from all the instances.
      Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $labServer -Database $inventoryDB
   }				}

Write-Host "Done!"
Next Steps
  • You can modify the script to include more types of backups if you like (File or Filegroup, Partial, etc.).
  • You can also modify it if you don’t want just the most recent ones, but a broader result set.
  • You can take a look at Microsoft’s documentation on the backupset table, in case you want to go deeper.
  • With this result set, you can build a custom alert mechanism around it to notify you for cases like the following:
    • There have been more than 7 days since the last full backup for a particular database.
    • There have been more than 24 hours since the last differential backup for a particular database.
    • There have been more than X minutes since the last transaction log backup for a particular database.
    • If a particular backup type has never been taken for a database that does meet the conditions for that type of backup.
      • For instance, you don’t want alerts for transaction log backups on databases under the SIMPLE recovery model.


Last Updated: 2018-10-22


next webcast button


next tip button



About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, October 22, 2018 - 7:07:32 AM - Adel Yousuf Back To Top

Nice Article 


Learn more about SQL Server tools