Backup History Scripts

Database Backups for all databases For Previous Week :

SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name, 
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date

----24 hour full backup
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'D'
ORDER BY backup_set_id DESC
GO

script 2.
:--------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database - Detailed
-------------------------------------------------------------------------------------------
SELECT
   A.[Server],
   B.database_name,
   A.last_db_backup_date,
   B.backup_start_date,
   B.expiration_date,
   B.backup_size,
   B.logical_device_name,
   B.physical_device_name, 
   B.backupset_name,
   B.description
FROM
   (
   SELECT 
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
       msdb.dbo.backupset.database_name,
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
   FROM    msdb.dbo.backupmediafamily
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   WHERE   msdb..backupset.type = 'D'
   GROUP BY
       msdb.dbo.backupset.database_name
   ) AS A
  
   LEFT JOIN

   (
   SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name, 
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  msdb..backupset.type = 'D'
   ) AS B
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
   A.database_name



script :-
------------------------------------------------------------------------------------------
--Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours
-------------------------------------------------------------------------------------------
--Databases with data backup over 24 hours old
SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
   DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM    msdb.dbo.backupset
WHERE     msdb.dbo.backupset.type = 'D'
GROUP BY msdb.dbo.backupset.database_name
HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))

UNION

--Databases without any backup history
SELECT    
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   master.dbo.sysdatabases.NAME AS database_name,
   NULL AS [Last Data Backup Date],
   9999 AS [Backup Age (Hours)]
FROM
   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'
ORDER BY
   msdb.dbo.backupset.database_name



--------------------------------------

Percent....backup completed

SELECT percent_complete, *
FROM sys.dm_exec_requests
WHERE command In ( 'RESTORE DATABASE', 'BACKUP DATABASE' )

-------------------------------------------------------
last backup
SELECT   d.name,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.sysdatabases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
GROUP BY d.name
ORDER BY backup_finish_date DESC

What is in Backup file

There is situation where we have a DB Backup file but not able to determine 

1.       Which Backup exists in Backup file whether it is full, Differential or log.

2.       How to identify logical name of data and log file

So identify it we can use below commands 

Restore headeronly :  

e.g Backup headeronly DBNAME from Disk = ‘Path where backup file’

                Result provide the info like Backup type, 1 for full ,2 for log, 5 for Differential

Or use BackupTypeDiscription column to check it

Also it provides the info like DB name , Server name, LSN info, Recovery model 

Restore Filelistonly

It provide the logical name , physical name of DB Backup file.

e.g Backup filelistonly  DBNAME from Disk = ‘Path where backup file’

It really helps when you are restoring multiple backup like full backup and sequence of log backup.

If any log backup sequence is not getting identify by file name you can identify by LSN sequence