March 11, 2009 at 3:16 pm
I wanted to check on a regular basis if all the databases we have online are being backed up and devised the following script -
select x.name as 'Databases without backups'
from master.dbo.sysdatabases x left outer join
(
select database_name, count(1) as no_of_backups-- backup_start_date, backup_finish_date
from msdb.dbo.backupset
where backup_start_date >= dateadd(day,-7,getdate())
group by database_name
)y
on x.name = y.database_name
where y.database_name is null and x.name != 'tempdb'
Thoughts for improvement - ideas?
March 11, 2009 at 4:39 pm
leonp (3/11/2009)
I wanted to check on a regular basis if all the databases we have online are being backed up and devised the following script -select x.name as 'Databases without backups'
from master.dbo.sysdatabases x left outer join
(
select database_name, count(1) as no_of_backups-- backup_start_date, backup_finish_date
from msdb.dbo.backupset
where backup_start_date >= dateadd(day,-7,getdate())
group by database_name
)y
on x.name = y.database_name
where y.database_name is null and x.name != 'tempdb'
Thoughts for improvement - ideas?
This is how it is determined in SSMS:
SELECT
dtb.name AS [Name],
(select max(backup_finish_date) from msdb..backupset where type = 'D' and database_name = dtb.name) AS [LastBackupDate],
(select max(backup_finish_date) from msdb..backupset where type = 'L' and database_name = dtb.name) AS [LastLogBackupDate]
FROM
master.sys.databases AS dtb
WHERE (dtb.name=N'yourdbname')
So you are on the right track 😉
I would prefer:
select x.name as 'Databases without backups'
from master.dbo.sysdatabases x
Where not exists
(
select *
from msdb.dbo.backupset BU
where BU.backup_start_date = dateadd(day,-7,getdate())
and BU.database_name = x.name
)
and x.name != 'tempdb'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2009 at 8:17 pm
I use this to show me when the last Full, Diff & Log backups were (or weren't):
SELECT B.name as Database_Name,
ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
case
when status > 16 then 'Check DB Status' -- Alert that DB might be ReadOnly, Offline etc...
else ' '
end as 'DB Status'
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name --AND A.type = 'D'
GROUP BY B.name , a.type, status
ORDER BY B.name , LastBackupDate desc,a.type, status
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply