SQL Query to Get Backup Details in SharePoint

This SQL query gives you the details of when was the last backup of a SharePoint SQL database was taken.

SELECT 

    d.[name] as DatabaseName,

    ' Last Backed Up: ' + COALESCE(cast(b.backup_finish_date AS varchar(10)) , 'Never') AS Finding

   FROM    master.sys.databases d

    LEFT outer JOIN msdb.dbo.backupset b ON d.name = b.database_name

   WHERE   d.database_id <> 2 

      AND d.state <> 1 /* Not currently restoring, like log shipping databases */

      AND d.is_in_standby = 0 /* Not a log shipping target database */

      AND d.source_database_id IS NULL /* Excludes database snapshots */

   GROUP BY d.name, b.backup_finish_date

Administrators need to be aware of all the SharePoint database backup details. In case the documentation is not up-to-date or the administrator needs a sneak view on when was the last backup taken, the above query can get it.

You will get output as shown below