Post reply

SQL script or application that checks what databases have or have not been backed up.

  • May 9, 2018 at 11:23 am

    #328616

    Hello --

    We  have a fairly large number of user databases that are subject to full, differential, and log backups. The various backup jobs that we have do a good job, via their respective log files, in listing what databases were backed up.
    However, we would also like to be able to more easily determine what databases were not backed up so we can take the necessary steps to correct the problem.
    Is there a recommended script or application that can be used to do this?
    Thanks.

  • May 9, 2018 at 11:49 am

    #1989916

    I doubt you really need an application to do this. Getting the last (full) backup date for a database, for example is quite simple:

    USE msdb;
    GO
    SELECT d.[name] AS DatabaseName,
       MAX(bs.backup_finish_date) AS LastBackup
    FROM sys.databases d
      LEFT JOIN dbo.backupset bs on d.[name] = bs.[database_name]
    WHERE database_id > 4
    GROUP BY d.[name];

    You could easily, for example, add a HAVING clause along the lines of DATEDIFF(DAY,MAX(bs.backup_finish_date),GETDATE()) > 2  or something to limit the dataset to databases that were backed up in the last couple of days. You could then, just as easily, set up that dataset to be emailed to specific people, using the SQL Server's Agent.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • May 9, 2018 at 11:54 am

    #1989918

    Hello Kaplan,

    Pretty much every SQL monitoring tool/application can be set to send alerts for missing database backups. A simple powershell script or an SSIS package can be used to loop through multiple servers and pull the backup information.
    Scott Murray wrote a pretty cool script here : https://www.mssqltips.com/sqlservertip/4164/ssrs-report-for-sql-server-database-backup-status/. He also explains how to create a fancy SSRS report based on his script. The report can be scheduled as required.
    Hope this helps.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply