TROUBLESHOOTING BACKUP ISSUES IN SQL SERVER 2008

When one of my onsite Lead asked me to check the backup status and also he need the status of last months backup history . .I was using this following  backup queries to find the backup details .
Hope it will help you guys.

BACKUP HISTORY OF PARTICULAR DATE :
****************************************************

SELECT * FROM [msdb].[dbo].[backupset] where backup_start_date >'2012-02-01'

SELECT * FROM [msdb].[dbo].[backupset] where backup_start_date between '2012-01-01' and '2012-02-20'



BACKUP HISTORY  OF PARTICULAR DATABASE :
**********************************************************



DECLARE @DBNAME VARCHAR(50)

SET @DBNAME ='MASTER'

SELECT CEILING(((BACKUP_SIZE / 1024) /1024) /1024)  BKup_size_in_GB,
CEILING(((BACKUP_SIZE / 1024) /1024))  BKup_size_in_MB,[TYPE]BKup_type,backup_start_date
StartTime,backup_finish_date
from msdb..backupset
where database_name= @DBNAME
order by backup_start_date desc


I have given 'MASTER' database .You can use which ever database you like .

NOTE :: The value in bkup_type column indicates the type of backup performed. D indicates FULL backup, I indicates Differential and L indicates Log backup.


BACKUP HISTORY OF CURRENT DAY (TODAY's BACKUP)
*****************************************************************

DECLARE @dt as DATE
SET @dt = getdate()
SELECT *
FROM [msdb].[dbo].[backupset]
where backup_start_date>=(select @dt)


BACKUP HISTORY WITH BACKUP START &  END TIME 
******************************************************************

SELECT *
FROM [msdb].[dbo].[backupset]
where backup_start_date>=dateadd(day,datediff(day,0,getdate()),0)


TO CHECK WHICH  DATABASE MISSED FULL BACKUP
*****************************************************************

SELECT NAME FROM sys.databases
WHERE NAME != 'TEMPDB'
AND NAME NOT IN (    SELECT DISTINCT database_name FROM msdb..backupset
WHERE backup_start_date > DATEADD(DAY,-8,GETDATE())
AND  TYPE = 'D' )


After running this i got MSDB database as result which means that Full backup of MSDB was not performed .


When my Onsite lead asked me to check the status of backup completed . .I was using the following query to find the status of backup completed .

STATUS of BACKUP (percentage completed details)
**********************************************************************

SELECT percent_complete , (estimated_completion_time/1000)/60 Estimated_completion_time_Mins , (total_elapsed_time/1000)/60 Total_Elapsed_Time_Mins ,DB_NAME(Database_id) DBName ,* FROM sys.dm_exec_requests WHERE session_id=3576

STATUS OF BOTH BACKUP & RESTORE (percentage completed details)
***********************************************************************



SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [PERCENT Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GETDATE()),20) AS [ETA COMPLETION TIME],
CONVERT(NUMERIC(6,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0) AS [ETA MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(100),(SELECT SUBSTRING(TEXT,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')



***************Thanks*****************