SQL Query to Get Backup Details in SharePoint

By Veena Sarda Sep 13 2013
This SQL query gives you the details of when was the last backup of a SharePoint SQL database was taken.
    • Like
    • Love It
    • Awesome
    • Interesting
    • It's Okay
    • Thumbs Down
  • 1.8k
  • 0


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

Last backup taken.png