May 18, 2010 at 11:53 am
Is there any query to find out if transaction log backup happened for a database.
I checked the maintenance plan etc..and see no t-log backup scheduled.
Could I confirm through query?
M&M
May 18, 2010 at 12:08 pm
Something like this will give you a history of backups. You can specify the database & time period if you want.
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, user_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, -30, (getdate())) -- Last 30 days
AND sysdb.name = 'My_DB_Name'
ORDER BY sysdb.name, bkup.backup_finish_date desc
May 18, 2010 at 12:09 pm
I came across the answer now in SQLServerCentral itself.
http://www.sqlservercentral.com/Forums/Topic731376-110-1.aspx#bm731472
We need to query msdb..backupset and there is a TYPE column where we can see the values for
different kinds of backups.
M&M
May 18, 2010 at 12:10 pm
homebrew01 (5/18/2010)
Something like this will give you a history of backups. You can specify the database & time period if you want.
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, user_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, -30, (getdate())) -- Last 30 days
AND sysdb.name = 'My_DB_Name'
ORDER BY sysdb.name, bkup.backup_finish_date desc
Thanks you, this is cool.
M&M
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply