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
