Check your backup in SQL server

We all know that Database backups are the bread and butter task of the database administration job. We cannot have any database running without backups. Without proper and prompt backup it is not possible to recover the database in the event of data corruption and failure.

Therefore, it is critical to monitor the backups continuously.

In This scenario we can add one more step in our Backup User Databases job which monitoring to find any databases that have not been backed up in certain number of hour.

In our admin database we can create one store procedure uspMonitorBackups. Below I have mentioned the code of the stored procedure uspMonitorBackups, which queries the msdb.dbo.bacupset table for the last backup.



USE [admin]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[uspMonitorBackups]‘) AND type in (N’P', N’PC’))
DROP PROCEDURE [dbo].[uspMonitorBackups]
GO
CREATE PROCEDURE uspMonitorBackups @backuptype char(1), @numOfMinutes int
as
–D = Database
–L = Log
–Example: Exec admin.dbo.uspMonitorBackups ‘D’,24

declare @dbname varchar(2000)

select name as Databasename

from master.dbo.sysdatabases

where name not in (‘master’,'model’,'msdb’,'tempdb’,'LiteSpeedLocal’)

and name not in

(select distinct database_name

from msdb.dbo.backupset

group by database_name

having datediff(minute, max(backup_start_date),getdate()) < @numOfMinutes

)

Job step which need to add in Backup User Databases job

Exec admin.dbo.uspMonitorBackups ‘D’,24

The Stored procedure accepts two parameters. The first parameter is type of database backup and second parameter is number of hours