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

Next Webcast - Simple SQL Server Reporting - Click Here to Register
 

Automatic Notifications for SQL Server Databases Without Backups


By:   |   Read Comments (1)   |   Related Tips: 1 | 2 | 3 | More > Backup

Problem

One of the most important tasks of any SQL Server DBA is to validate database backups are occurring in their environments.  When we have an environment with hundreds or thousands of databases supported by dozens of SQL Server instances, it is physically impossible to control the performance of the backup, beyond waiting for notifications from SQL Server Maintenance Plans to alert us.  There may be cases when SQL Server Maintenance Plans or our backup software is not able to determine if the database backup has been completed.  There may be circumstances where our backup software fails, backup disks fill-up or new databases are not added to the SQL Server Maintenance Plan.  How can we prevent this issue?

Solution

By consulting the msdb.dbo.backupset table we can get a list of when the last backup of each database was performed, and if the backup does not match our backup plan, we can receive an email letting us know what databases are affected.

Furthermore, if this query is included in a SQL Server Agent Job step we can determine if all the databases have a full backup within the desired period of time, otherwise the SQL Server Agent Job can send us an email with a list of databases that have no backup.

Currently, our requirements are to have a daily review of the backups to verify each database has a FULL backup in the last day. The [msdb].[dbo].[backupset] table in the MSDB database keeps a log of all backups executed on the instance.

Here's how we can fulfill that requirement:

1: Declare a variable where we can choose the number of days without a backup to trigger the email.

DECLARE @dayswithout int = 1 -- Days without backup for activate send mail.

2: Create a temporary table where insert the databases with a full backup after the number of days specified in the @dayswithout variable.

CREATE TABLE #tablenames (id int NOT NULL IDENTITY (1, 1),name nvarchar (50))

INSERT INTO #tablenames (name)  
SELECT DISTINCT msdb.dbo.backupset.database_name
FROM msdb.dbo.backupset 
WHERE msdb..backupset.type = 'd' 
AND (CONVERT(datetime, msdb.dbo.backupset.backup_finish_date, 102) >= GETDATE() - @dayswithout)

3: Declare a variable for the number of databases without a backup on the instance which are online and are not tempdb will be saved.

DECLARE @number_databases int = 
( 
SELECT count (name) 
from sys.databases 
WHERE state_desc = 'ONLINE' 
AND name <> 'tempdb' 
AND name not in (SELECT name FROM #tablenames)
)

4: Two variables for all the text sent via email for the databases that do not have a backup.

DECLARE @subjectmail NVARCHAR (255) = 'There are '+ ( select convert (nvarchar(10), @number_databases))+' DDBB without backup in ' + (SELECT @@servername)
  
DECLARE @querymail nvarchar (900) = 'set nocount on;

SELECT @@servername [Server Information]
union ALL

SELECT DISTINCT dec.local_net_address 
FROM sys.dm_exec_connections AS dec
WHERE  dec.local_net_address IS NOT NULL 
union ALL

SELECT ''------------------'' 
union ALL

SELECT ''DATABASE WITHOUT BACKUPS''
union ALL

SELECT ''------------------''
union ALL

SELECT name  from sys.databases where name not in
(
SELECT DISTINCT
msdb.dbo.backupset.database_name
FROM msdb.dbo.backupset 
WHERE (CONVERT(datetime,msdb.dbo.backupset.backup_finish_date, 102) >= GETDATE() -'+ ( select convert (nvarchar(10), @dayswithout))+')) 
and state_desc = ''ONLINE'' and name <> ''tempdb'''

5: If there are any databases without a backup during the specified time, then send an email.

IF @number_databases > 0

EXEC msdb.dbo.sp_send_dbmail  

@profile_name = 'Alert DBA', --Your profile name
@recipients = '[email protected]', --Your alert4s mail address
@importance= 'High', 
@subject = @subjectmail, 
@body = 'Attach file with information about DDBB without backup.',
@query = @querymail,
@query_attachment_filename='DATABASES_NO_BACKUP.csv', -- File with info about databases. 
@attach_query_result_as_file = 1 ;  

6: We remove the temporary tables.

DROP TABLE #tablenames

Here is the complete script:

DECLARE @dayswithout int = 1 -- Days without backup to activate email send.

CREATE TABLE #tablenames (id int NOT NULL IDENTITY (1, 1),name nvarchar (50))

INSERT INTO #tablenames (name)  
SELECT DISTINCT msdb.dbo.backupset.database_name
FROM msdb.dbo.backupset 
WHERE msdb..backupset.type = 'd' 
AND (CONVERT(datetime, msdb.dbo.backupset.backup_finish_date, 102) >= GETDATE() - @dayswithout)

DECLARE @number_databases int = 
( 
SELECT count (name) 
from sys.databases 
WHERE state_desc = 'ONLINE' 
AND name <> 'tempdb' 
AND name not in (SELECT name FROM #tablenames)
)

DECLARE @subjectmail NVARCHAR (255) = 'There are '+ ( select convert (nvarchar(10), @number_databases))+' databases without backup in ' + (SELECT @@servername)
  
DECLARE @querymail nvarchar (900) = 'set nocount on;

SELECT @@servername [Server Information]
union ALL
SELECT DISTINCT dec.local_net_address 
FROM sys.dm_exec_connections AS dec
WHERE dec.local_net_address IS NOT NULL 
union ALL
SELECT ''------------------'' 
union ALL
SELECT ''DATABASE WITHOUT BACKUPS''
union ALL
SELECT ''------------------''
union ALL
SELECT name  from sys.databases where name not in
(
SELECT DISTINCT
msdb.dbo.backupset.database_name
FROM msdb.dbo.backupset 
WHERE (CONVERT(datetime,msdb.dbo.backupset.backup_finish_date, 102) >= GETDATE() -'+ ( select convert (nvarchar(10), @dayswithout))+')) 
and state_desc = ''ONLINE'' and name <> ''tempdb'''

IF @number_databases > 0

EXEC msdb.dbo.sp_send_dbmail  
  @profile_name = 'Alert DBA', --Your profile name
  @recipients = '[email protected]', --Your alert email address
  @importance= 'High', 
  @subject = @subjectmail, 
  @body = 'Attach file with information about databases without backup.',
  @query = @querymail,
  @query_attachment_filename='DATABASES_NO_BACKUP.csv', -- File with info about databases. 
  @attach_query_result_as_file = 1 ;  

DROP TABLE #tablenames
 
Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Angel Gomez Angel Gomez is the head of SQL Server Database Administration and works with more than 1000 databases and 15TB of data.

View all my tips





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.



    



Friday, October 28, 2016 - 9:41:18 AM - Jeremy Kadlec Back To Top

Angel,

Congrats on your first tip!

Thank you,
Jeremy Kadlec
Community Co-Leader


Learn more about SQL Server tools