Monitoring SQL Server Backups

One of the most important tasks for the DBAs is to ensure that there is a maintenance plan to recover data from a given disaster.  As a DBA we need to design a maintenance plan according to our scenario and business requirements. Do we want to be able to recover data at any point of time? How much data loss can we accept? All these questions and many more must be answered before designing the plan. In this post we will assume a basic daily full backup to keep our data safe, we will assume that there is a job performing full backups to our databases every day at midnight.

The next step after we have defined and implemented the maintenance plan is to monitor that all backups are being executed. In order to reach our goal it will be necessary to know whether a backup has been done or not and that could be possible by monitoring the backup job or querying the msdb database metadata among many other options. For this post we will use the second option, we will query msdb to check databases backup information. The main reason why we choose this option is because of the variability of backup maintenance plan definitions. The backup job is defined by every DBA and we cannot assume that all databases are included in the maintenance plan, on the other hand by querying msdb we will know for sure the databases which have been backed up and those that have not been backed up.

Querying msdb database

As it has been explained before querying msdb database will give us the truth about database backups. Running the following query we will know how many days have happened since the last full backup of every database:

Use msdb;

 

with backup_info

as

(

    select

        bck.database_name,

        bck.database_guid,

        bck.backup_start_date,

        bck.backup_finish_date,

        bckmf.physical_device_name as BackupFile_Path,

        BackupType=

        case

            when bck.[type]='I' then 'Differential'

            when type='D' then 'Full'

            when type='L' then 'Log'

            else 'Unknown'

        end

    from backupset as bck

    inner join backupmediafamily as bckmf

        on bck.media_set_id=bckmf.media_set_id

), Last_Backups

as

(

      select *

      from

      (

            select

                  ROW_NUMBER() over (PARTITION BY V.database_guid, V.BackupType order by V.backup_start_date desc) as r,

                  *

            from backup_info as V

      ) as VV

      where VV.r=1 and VV.BackupType='FULL'

),dbs

as

(

      select

    name,database_guid,state_desc

      from sys.databases as dbs

      inner join sys.database_recovery_status as dbrs

            on dbrs.database_id=dbs.database_id

)

select

    name,

    case when V.database_name is null then 365 else DATEDIFF(day,backup_start_date,GETDATE()) end as DaysSinceLastBackup

from dbs

left join Last_Backups as V

    on V.database_guid=dbs.database_guid

where dbs.state_desc='ONLINE' and name <>'TempDB'

order by 2 desc;

 

Notice that databases that never have been backed up will return 365 days as the number of days since the last full backup.

This query returns the desired information like follows:

image

In this case we can see a basic example with system databases with 0 days since last full backup, which means that all databases are up to date with full backups. Another possible result could be:

image

In this case databases last full backup was four days ago. This second example could be a reason to be alarmed because in case of a disaster we only can recover data until four days ago, all changes made during the last four days would be lost.

Notice that databases that never have been backed up will return 365 days as the number of days since the last full backup.

As it was shown before, the query could help us to monitor backups in a single instance but what happen when the DBA has to monitor and manage more than one instance? And what if those instances are from different SQL Server Versions? Things start to get complicated and doing it one by one manually is not an option! I’m currently facing that situation; I’m managing more than 80 SQL Server instances from different versions. Here is when PowerShell comes to help the DBA.

PowerShell Solution

With PowerShell we will be able to query all msdb databases from all the desired SQL Server instances. The solution will have two files:

              1. Xml file with Server information

a.       SQL Server instance, user name, password…

              2. PowerShell script

The idea is to run the query to msdb for every server registered in the xml file. For instance the XML file structure could like follows:

image

For this demonstration we only need to provide the instance name, the SQL Server user name and the password to connect. The reason why I’m using SQL Server authentication is because not all my SQL Server instances are in the same domain so I need to be able to connect to all of them from a single point (where the script is running). Anyway the script can always be modified to connect with integrated authentication easily.

With the xml file ready the only thing missing is the script file which will read the xml file and execute the query for every server. The script looks like follows:

Param(

  [int]$DaysSinceLastBackup=-1,

  [string]$serversPath="C:\tmp\Servers.xml"

 )

 FunctionGet-SQLServer-DataTable ([string]$conn , [string]$query)

 {

     $SqlConnection=New-ObjectSystem.Data.SqlClient.SqlConnection;

     $SqlConnection.ConnectionString=$conn

     $SqlCmd=New-ObjectSystem.Data.SqlClient.SqlCommand;

     $SqlCmd.CommandText=$query;

     $SqlCmd.Connection=$SqlConnection;

     $SqlAdapter=New-ObjectSystem.Data.SqlClient.SqlDataAdapter;

     $SqlAdapter.SelectCommand=$SqlCmd;

     $DataTable=New-ObjectSystem.Data.DataTable;

     $SqlAdapter.Fill($DataTable) |out-Null;

     $SqlConnection.Close() ;

    

     return$DataTable;

}

 

FunctionGet-SQLDatabaseBackupsInfo ([string] $conn)

{

      $query="

            Usemsdb;

           

            withbackup_info

            as

            (

                  select

                        bck.database_name,

                        bck.database_guid,

                        bck.backup_start_date,

                        bck.backup_finish_date,

                        bckmf.physical_device_nameasBackupFile_Path,

                        BackupType=

                        case

                             whenbck.[type]='I'then'Differential'

                             whentype='D'then'Full'

                             whentype='L'then'Log'

                             else'Unknown'

                        end

                  frombackupsetasbck

                  innerjoinbackupmediafamilyasbckmf

                        onbck.media_set_id=bckmf.media_set_id

            ), Last_Backups

            as

            (

                  select*

                  from

                  (

                        select

                             ROW_NUMBER() over (PARTITION BY V.database_guid, V.BackupType order by V.backup_start_date desc) asr,

                             *

                        from backup_info as V

                  ) as VV

                  where VV.r=1 and VV.BackupType='FULL'

            ),dbs

            as

            (

                  select

                  name,database_guid,state_desc

                  from sys.databases as dbs

                  inner join sys.database_recovery_status as dbrs

                        on dbrs.database_id=dbs.database_id

            )

            select

                  @@SERVERNAME as ServerName,

                  nameasDbName,

                  case when V.database_name is null then 365 else DATEDIFF(day,backup_start_date,GETDATE()) end as DaysSinceLastBackup

            from dbs

            left join Last_Backups as V

                  on V.database_guid=dbs.database_guid

            where dbs.state_desc='ONLINE' and name <>'TempDB'

            order by 2 desc;

                  ";

      return Get-SQLServer-DataTable $conn $query;

}

    

 [xml]$xml=Get-Content $serversPath

 $xml.Servers.server|foreach-object{

    $it=$_;

    $instance=$it.InstanceName;

    $user=$it.username;

    $pass=$it.password;

   

    $conn="Server = $instance; Database = master; User=$user;Password=$pass;";

     

      Get-SQLDatabaseBackupsInfo $conn |where-object {$_.DaysSinceLastBackup -gt $DaysSinceLastBackup} | selectServerName, DbName, DaysSinceLastBackup;

 

 }

The script has two parameters:

           DaysSinceLastBackup : A threshold to filter result. The result will show all databases which latest full backups are older than the parameter value. The value by default is -1, a negative value that will make to show all results.

           ServersPath: The path where the XML file with all servers is allocated.

So we can execute the script like follows:

image

The example shown before executes the script passing the two parameters, the first one is the xml file and the second one is the Threshold. In this case we have used the value 2, which means that the script will return all databases which latest full backups are older than 2 days. In this case only the database test matches the condition, the result shows 365 days since the last full backup which means that this database has never been backed up.

On the other hand, if we execute the script without parameters we will see the information of all databases, this is what it will look like :

image

Conclusion

As it has been shown during this post, is very easy to monitor SQL Server Backups over different servers in a very fast and efficient way by using PowerShell. Once we have this script we can implement a scheduled task and use it to generate HTML reports or alarms to notify the DBAs and System administrators about the databases backup status. Once again PowerShell comes up to save the day and make our working life easier.

About the Author

Enrique Puig

Enrique Puig is an expert in relational databases and SQL Server platform. He has a strong Academic background in computer science. During the last five years Enrique has dedicated all his efforts to SQL Server relational databases, datawarehousing, performance tunings, high availability and all kind of projects reated with SQL Server: Writing white papers for Microsoft, delivering sessions at top level Microsoft events and working with leading companies around the world. Currently He works as a SQL Server Database Administrator and he also is a PowerShell enthusiast. In addtion he has a personal bog where he publish about SQL Server weekly (http://www.sqlserverpasion.com)