Backup history

 

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 

======================