November 20, 2008 at 8:59 am
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!
November 20, 2008 at 9:21 am
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
November 20, 2008 at 10:55 am
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
March 6, 2025 at 1:47 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply