Viewing backup history with T-SQL

• Sam Greene

  SSCertifiable

  Points: 5252

  More actions

  November 20, 2008 at 8:59 am

  #196102

  There seems to be an sp_help for everything in sql - except sp_helpbackuphistory 'my db'.

  Is there no quick way to do this with a built in function? Must I query the system's backup tables?

  I just can't believe it could be so, since this is an admins #1 duty - making sure databases are backed up.

  I'm trying to use the GUI less and my keyboard more...:cool:

  Thanks!

• homebrew01

  SSC Guru

  Points: 55549

  More actions

  November 20, 2008 at 9:21 am

  #901117

  Here's my homemade version:

  SELECT sysdb.name, bkup.description, bkup.backup_finish_date,

  case

  when type='D' then '** FULL **'

  when type='I' then 'DIFFERENTIAL'

  when type='L' then 'LOG'

  end as Backup_Type,

  (STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',

  ceiling(bkup.backup_size /1048576) as 'Size Meg' ,

  cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',

  server_name, sysdb.crdate

  ,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'

  ,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)

  as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn

  FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name

  where backup_finish_date > DATEADD(DAY, -60, (getdate())) -- Last 60 days

  --AND sysdb.name = 'MY_DB_NAME'

  ORDER BY sysdb.name, bkup.backup_finish_date desc

• Sam Greene

  SSCertifiable

  Points: 5252

  More actions

  November 20, 2008 at 10:55 am

  #901203

  Thanks for that.

  Made a little mod to it to take optional parameters

  example call

  sp_helpbackups 'sql_dba', 'D'

  or

  sp_helpbackups

  ALTER PROCEDURE [dbo].[sp_helpbackups]

  @MYDBNAME VARCHAR(50) = null,

  @BACKUPTYPE CHAR(1) = null

  AS

  SELECT sysdb.name,bkup.[name] AS FileName, bkup.description, bkup.backup_finish_date,

  case

  when type='D' then 'FULL'

  when type='I' then 'DIFFERENTIAL'

  when type='L' then 'LOG'

  end as Backup_Type,

  (STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',

  ceiling(bkup.backup_size /1048576) as 'Size Meg' ,

  cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',

  server_name, sysdb.crdate

  ,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'

  ,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)

  as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn

  FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name

  where backup_finish_date > DATEADD(DAY, -7, (getdate()))

  AND sysdb.name LIKE ISNULL(@MYDBNAME,'%')

  AND [type] LIKE ISNULL(@BACKUPTYPE,'%')

  ORDER BY sysdb.name, bkup.backup_finish_date desc

• Srinivas Merugu

  SSC Enthusiast

  Points: 181

  More actions

  March 6, 2025 at 1:47 pm

  #4552580

  Find below backup history.

   

  Select T2.type, T2.is_copy_only, physical_device_name, user_name, database_name, server_name, backup_start_date, backup_finish_date, backup_size, DATEDIFF(MINUTE, backup_start_date, backup_finish_date) AS Total_Minutes

  --DATEDIFF(HOUR, backup_start_date, backup_finish_date)

  from msdb..backupmediafamily T1, msdb..backupset T2

  where T1.media_set_id = T2.media_set_id

  and T2.backup_start_date > getdate() - 90

  and T2.type  in ('D','I', 'L')

  and database_name = 'Adventureworks_2022'

  order by T2.backup_start_date desc