introduction

Today i will explain how to monitor your backup is Saved on or no also How to Delete the old backup history from the log

How to Check your Backup log or your backup history

we can Check the backup history by this way but before this step i need from you to Create Databae “backupTest” and take backup from it with 3 types Full,DIFF,LOG

select Database_name,backup_start_date,backup_finish_date,type from msdb.dbo.backupset

you can select all Column (*) to see all information

but i will customize it to help you how to check the FULL backup , DiFF backup , Log backup

********************************Check FULL abckup history************************************

SELECT database_name,backup_start_date,type,recovery_model,server_name,machine_name,is_password_protected

FROM msdb.dbo.backupset where Type=‘D’ ORDER BY backup_set_id DESC

GO

********************************Check DIFF abckup history************************************

SELECT database_name,backup_start_date,type,recovery_model,server_name,machine_name,is_password_protected

FROM msdb.dbo.backupset where Type=‘i’

ORDER BY backup_set_id DESC

********************************Check LOG abckup history************************************

SELECT

database_name,backup_start_date,type,recovery_model,server_name,machine_name,is_password_protected

FROM msdb.dbo.backupset where Type=‘L’

ORDER BY backup_set_id DESC

GO

How to Delete your backup history:-

we can make delete for all backup history for any database by this Code

exec MSDB.DBO.sp_delete_database_backuphistory ‘Database name’

So this Code Delete history to the backup but for one database one by one so i will make Cursore to loop on all Server to get all database name then Delete the backup history for all Database

set ANSI_NULLSON

set QUOTED_IDENTIFIERON

go

Create PROC[dbo].DeletebackupHistory

AS

BEGIN

DECLARE @DB Nvarchar(50)

DECLARE GetDB CURSOR FOR

Select Name from Sys.Databases Where database_id> 4 and state_desc=‘ONLINE’

OPEN GetDB

FETCH NEXT FROM GetDB INTO @DB

WHILE @@FETCH_STATUS= 0

BEGIN

—-Delete backup history

exec MSDB.DBO.sp_delete_database_backuphistory @db

——————————-

FETCH NEXT FROM GetDB

INTO @DB

END— FETCH WHILE

CLOSE GetDB

DEALLOCATE GetDB

END