Home > Uncategorized > Check your backup in SQL server

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]


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]


CREATE PROCEDURE uspMonitorBackups @backuptype char(1), @numOfMinutes int


–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

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: