Search This Blog

SQL Server db backup history-Part3

How to get SQL Server Database backup history? Or when was my SQL Server database last backed up?

 It’s important to keep track of all the backups happening to make sure we have a latest backup available and all the required databases are backed up regularly. Use below scripts to check backup history and other backup related details.
1: Script to get backup history of all the databases. With a little modification as indicated in script you can easily get below details:
  • Script for Backup History of only one database.
  • Script for Backup history of one/ all the databases between particular dates
  • Script for only Full/Diff/Log backups of all/ Particular database, complete history or between particular dates.
SELECT p.database_name AS DatabaseName,
p.backup_start_date AS 'Backup Start Time',
p.backup_finish_date AS 'Backup Finish Time',
CAST((DATEDIFF(MINUTE, p.backup_start_date, p.backup_finish_date)) AS varchar)+ ' min  '+ CAST((DATEDIFF(ss, p.backup_start_date, p.backup_finish_date)) AS varchar) + ' sec ' AS [Total Time] ,
CASE p.type
WHEN 'D' THEN 'Full '
WHEN 'I' THEN 'Diffrential'
WHEN 'L' THEN 'Log'
END AS 'Backup Type',
Cast(p.backup_size/1024/1024 AS numeric(10,2)) AS 'Backup Size(MB)' ,
a.physical_device_name AS 'Physical File location'
FROM msdb..backupmediafamily a,
msdb..backupset p
WHERE a.media_set_id=p.media_set_id

-- UNCOMMENT BELOW LINE AND REPLACE <DATABASE NAME> WITH DB YOU WANT TO CHECK BACKUP HISTORY
--AND P.DATABASE_NAME='DATABASE NAME'

-- UNCOMMENT BELOW LINE AND REPLACE START AND END DATES WITH DATES YOU WANT TO CHECK HISTORY
--AND P.BACKUP_START_DATE>'2013-01-20' AND P.BACKUP_START_DATE<'2013-01-25 23:59:59'

--UNCOMMENT BELOW LINE TO SEE ONLY THE FULL BACKUPS, REPLACE WITH 'I' TO CHECK DIFFRENTIAL AND 'L' TO CHECK ONLY LOG BACKUPS.
--AND P.TYPE='D'

ORDER BY p.backup_start_date DESC

 output:
DatabaseName|BackupStartTime|BackupFinishTime|Total TimeBackup|Type| Size(MB) Physical File location
model 2014-01-22 17:32:58.000 2014-01-22 17:32:58.000 0 min  0 sec Full 1.32 D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\model_backup_2014_01_22_173257_4531250.bak
msdb 2014-01-22 17:32:58.000 2014-01-22 17:32:59.000 0 min  1 sec Full 11.08 D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\msdb_backup_2014_01_22_173257_4531250.bak

master 2014-01-22 17:32:57.000 2014-01-22 17:32:57.000 0 min  0 sec Full 3.01 D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\master_backup_2014_01_22_173257_4375000.bak

2: Script to get name of databases are never backed up:


SELECT name AS 'Database Name',
       NULL AS [last backup]
FROM sys.sysdatabases
WHERE name NOT IN
    (SELECT database_name
     FROM msdb..backupset)
  AND name <> 'tempdb'


Output:
Database Name last backup
ReportServer NULL
ReportServerTempDB NULL
TestDB NULL

3: Latest Full/Differential/Log backup of one/all databases with duration:

SELECT Database_name,
       MAX(Backup_finish_date) AS 'latest Backup Date',
       CAST((DATEDIFF(hh, MAX(Backup_finish_date), GETDATE())) AS varchar) AS [Total Time]
FROM msdb..backupset
--REPLACE 'D' WITH 'I' TO CHECK LATET DIFF BACKUP AND 'L' TO CHECK LATEST FULL BACKUP
WHERE TYPE='D'
--UNCOMMENT BELOW LINE AND REPLACE DATABASE NAME YOU WANT TO CHECK LATEST FULL BACKUP
 --AND DATABASE_NAME='MASTER'
GROUP BY database_name

output:
master 2014-01-22 17:32:57.000 0
model 2014-01-22 17:32:58.000 0
msdb 2014-01-22 17:32:59.000 0
Important Points:
These scripts can esaily be used in a scheduled job and scheduled to run so that you always know which databases are missing from backup schedules and if all of them are backed up properly.