To check if a database backup has been completed (in this case the backup job has run not longer then 2 days ago) we can create a stored procedure
|
USE [master]
GO
/****** Object: StoredProcedure [dbo].[spLastBackup] Script Date: 3-4-2014 10:22:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spLastBackup]
@DatabaseName nvarchar(50)
AS
BEGIN
set nocount on
declare @numdays int
SELECT @numdays = DATEDIFF(d, MAX(b.backup_finish_date), getdate()) FROM sys.sysdatabases s LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = s.name WHERE s.Name = @DatabaseName
SELECT CASE WHEN @numdays > 1 THEN 'ERROR' ELSE 'ALL-OK' END
END
|
To check you can run in your SQL studio : EXEC spLastBackup ##Database name##
If you just want to check all USER DATABASES
Now just run Exec spLastBacku
|
USE [master]
GO
/****** Object: StoredProcedure [dbo].[spLastBackup] Script Date: 2/7/2020 2:07:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spLastBackup]
AS
BEGIN
set nocount on
DECLARE @dataname varchar(300)
DECLARE @errorInBackup int = 0
DECLARE @dataname_header varchar(75)
DECLARE datanames_cursor CURSOR FOR SELECT name FROM sysdatabases WHERE name not in ('master', 'tempdb', 'model', 'msdb') /* add none checked DB's ! */
declare @numdays int
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END
SELECT @numdays = DATEDIFF(d, MAX(b.backup_finish_date), getdate()) FROM sys.sysdatabases s LEFT OUTER JOIN msdb.dbo.backupset b on b.database_name = s.name where s.name = @dataname and b.name is not null and b.type = 'D'
if @numdays >=2 OR @numdays is null
BEGIN
PRINT @dataname_header
print @numdays
SELECT CASE
WHEN @numdays >= 2 THEN 'ERROR - ' +@dataname + ' backup failure'
WHEN @numdays is null THEN 'ERROR - ' +@dataname + ' no backups present'
END
if @numdays >= 2 SET @errorInBackup = 1
if @numdays is null SET @errorInBackup = 1
END
FETCH NEXT FROM datanames_cursor INTO @dataname
END
if @errorInBackup = 0 SELECT 1
DEALLOCATE datanames_cursor
END
|