Backup history
SELECT
bup.server_name AS [Server],
bup.user_name AS [User],
bup.database_name AS [Database],
bup.type as [Type],
bup.is_copy_only as [IScopyonly],
bup.description,
bup.backup_size/1024.00 /1024.00 /1024.00 as [SizeGB],
bup.compressed_backup_size /1024.00 /1024.00 /1024.00 as Compressed_backup_size_GB,
bup.backup_start_date AS [Backup Started],
bup.backup_finish_date AS [Backup Finished],
CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%3600/60 AS varchar)+ ' minutes, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds' AS [Total Time]
FROM msdb.dbo.backupset bup
where type='D' ----- I for diff, D for full and L for log
and database_name='latnative'
order by backup_start_date desc
=========================================================
SELECT
bup.server_name AS [Server],
bup.user_name AS [User],
bup.database_name AS [Database],
CASE
WHEN bup.type = 'D' THEN 'Full'
WHEN bup.type = 'I' THEN 'Differential'
WHEN bup.type = 'L' THEN 'Log'
ELSE 'Unknown'
END AS [Type],
bup.is_copy_only as [IScopyonly],
bup.description,
bup.backup_size/1024.00 /1024.00 /1024.00 as [SizeGB],
bup.compressed_backup_size /1024.00 /1024.00 /1024.00 as Compressed_backup_size_GB,
bup.backup_start_date AS [Backup Started],
bup.backup_finish_date AS [Backup Finished],
CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%3600/60 AS varchar)+ ' minutes, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds' AS [Total Time]
FROM msdb.dbo.backupset bup
WHERE (bup.type = 'D' OR bup.type = 'I'OR bup.type = 'L')
AND bup.database_name='VIT_GLOBAL_TOOLBOX'
ORDER BY backup_start_date DESC
===================================
Database Backups for all databases For Previous Week
From <https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/>
---------------------------------------------------------------------------------
--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
=============================================
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
ORDER BY
msdb.dbo.backupset.database_name
=================================================================
Single database history with database backup size details:
select backup_start_date, backup_finish_date, database_name,type,name,user_name,description
,(backup_size /1024.00 /1024.00 /1024.00) as backup_size_GB
,(compressed_backup_size /1024.00 /1024.00 /1024.00) as Compressed_backup_size_GB
from msdb.dbo.backupset
where type='I' ----- I for diff, D for full and L for log
and database_name='siebeldb'
order by backup_start_date desc
=================================================================
Single database backup history of 40 days ………
select backup_start_date, backup_finish_date, database_name,type,name,user_name,description
,(backup_size /1024.00 /1024.00 /1024.00) as backup_size_GB
,(compressed_backup_size /1024.00 /1024.00 /1024.00) as Compressed_backup_size_GB
from msdb.dbo.backupset
where database_name='siebeldb'
and (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 40)
order by backup_start_date desc
============================================
Database history with growth comparison
SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]) order by database_name
==========================================
Script to find full details of database backup history along with media family:
SELECT
bup.server_name AS [Server],
bup.user_name AS [User],
bup.database_name AS [Database],
bup.type as [Type],
bup.is_copy_only as [IScopyonly],
bup.description,
bup.backup_size/1024.00 /1024.00 /1024.00 as [SizeGB],
bup.compressed_backup_size /1024.00 /1024.00 /1024.00 as Compressed_backup_size_GB,
bup.backup_start_date AS [Backup Started],
bup.backup_finish_date AS [Backup Finished],
CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%3600/60 AS varchar)+ ' minutes, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds' AS [Total Time],
bmf.physical_device_name AS [Backup File Path],
bmf.device_type AS [Device Type],
bmf.logical_device_name AS [Logical Device Name],
bmf.media_set_id AS [Media Set ID]
FROM msdb.dbo.backupset bup
INNER JOIN msdb.dbo.backupmediafamily bmf ON bup.media_set_id = bmf.media_set_id
WHERE bup.type='D' -- I for diff, D for full and L for log
AND bup.database_name='master'
ORDER BY bup.backup_start_date DESC;
===============================================================
use msdb
go
-- D = Full, I = Differential and L = Log.
-- There are other types of backups too but those are the primary ones.
SELECT @@servername [SQLSrvrName],backupset.database_name, getdate()[SrvrTimeNow],
MAX(CASE WHEN backupset.type = 'D' THEN backupset.backup_finish_date ELSE NULL END) AS LastFullBackup,
MAX(CASE WHEN backupset.type = 'I' THEN backupset.backup_finish_date ELSE NULL END) AS LastDifferential,
MAX(CASE WHEN backupset.type = 'L' THEN backupset.backup_finish_date ELSE NULL END) AS LastLog
FROM backupset
GROUP BY backupset.database_name
ORDER BY backupset.database_name DESC
=================================================================
Recent database backup history along with age
use msdb
go
-- D = Full, I = Differential and L = Log.
-- There are other types of backups too but those are the primary ones.
SELECT @@servername [SQLSrvrName],backupset.database_name, getdate()[SrvrTimeNow],
MAX(CASE WHEN backupset.type = 'D' THEN backupset.backup_finish_date ELSE NULL END) AS LastFullBackup,
DATEDIFF(hh, MAX(CASE WHEN backupset.type = 'D' THEN backupset.backup_finish_date ELSE NULL END), GETDATE()) AS [FullBak Age (Hours)],
MAX(CASE WHEN backupset.type = 'I' THEN backupset.backup_finish_date ELSE NULL END) AS LastDifferential,
DATEDIFF(hh, MAX(CASE WHEN backupset.type = 'I' THEN backupset.backup_finish_date ELSE NULL END), GETDATE()) AS [DiffBak Age (Hours)],
MAX(CASE WHEN backupset.type = 'L' THEN backupset.backup_finish_date ELSE NULL END) AS LastLog,
DATEDIFF(hh, MAX(CASE WHEN backupset.type = 'L' THEN backupset.backup_finish_date ELSE NULL END), GETDATE()) AS [LogBak Age (Hours)]
FROM backupset
GROUP BY backupset.database_name
ORDER BY backupset.database_name
======================