June 20, 2007 at 4:02 am
Hi,
I'm messing with some scripts for information about databases that my manager would like reports on regularly. One of them will be used to determine if our backup team is actually backing up things they're supposed to be backing up when they say they are.
I've written a simple query using the msdb database and backupset table to tell me when the last full backup and last log backup occurs. The problem is that it's returning two rows per database because it's insisting that I put the type column into a group by clause. How do I get around that?
SELECT server_name AS ServerName,database_name AS DatabaseName,(CASE WHEN type='D' THEN MAX(backup_finish_date) END) AS DatabaseBackup,(CASE WHEN type='L' THEN MAX(backup_finish_date) END) AS LogBackup
FROM backupset
GROUP BY server_name,database_name,type
ORDER BY server_name,database_name
Thanks!
June 20, 2007 at 4:50 am
I figured it out in the end. I had tried a union query but it wasn't working and then I read that I need to use an alias if I wanted to select from a select statement and that worked:
SELECT ServerName,DatabaseName,MAX(DatabaseBackup) AS DatabaseBackup, MAX(LogBackup) AS LogBackup
FROM (SELECT server_name AS ServerName,database_name AS DatabaseName,MAX(backup_finish_date) AS DatabaseBackup, NULL AS LogBackup
FROM backupset
WHERE type='D'
GROUP BY server_name,database_name
UNION
SELECT server_name AS ServerName,database_name AS DatabaseName,NULL AS DatabaseBackup, MAX(backup_finish_date) AS LogBackup
FROM backupset
WHERE type='L'
GROUP BY server_name,database_name) BH
GROUP BY ServerName,DatabaseName
ORDER BY ServerName,DatabaseName
June 20, 2007 at 4:51 am
select convert(varchar(20),database_name)as 'Database name',
case convert(varchar(30),backup_finish_date,101)
when convert(varchar(30),getdate()-1,101) then 'Current Backup'
else 'Old backup'
end as 'Backup Status',
convert(varchar(20),backup_finish_date,101) as 'Backup File Date',
CASE type
WHEN 'D' THEN 'Full Database backup'
WHEN 'I' THEN 'Differential backup'
WHEN 'L' THEN 'Log'
ELSE 'File or Filegroup'
END as 'Backup Type'
from backupset
where backup_finish_date = (select max(backup_finish_date) from msdb..backupset where database_name = 'dbname')
You can extend this query to your requirements.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply