June 3, 2015 at 12:17 pm
Here's my statement below. What I'm trying to get is joining the name column in master.sys.databases with a sub query for the database name, file location and backup start date from the MSDB database. The reason for this, if a new database has never been backed up, It should be returning as a NULL value, which is my goal. However, I'm getting multiple results for the backups. Any help or suggestions would be greatly appreciated.
select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date)
from master.sys.databases a
left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name
from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on c.media_set_id=c.backup_set_id
where c.type='D'
group by c.database_name,b.physical_device_name) b
on a.name=b.database_name
where a.name not in ('tempdb','model')
and a.name not in ('msdb','master')
and a.state_desc = 'ONLINE'
group by a.name,b.backup_start_date,b.physical_device_name
order by b.backup_start_date desc
June 3, 2015 at 1:08 pm
I just realized the join (from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on b.media_set_id=c.backup_set_id) was not correct, bad oversight on my part...
select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date)
from master.sys.databases a
left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name
from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on b.media_set_id=c.backup_set_id
where c.type='D'
group by c.database_name,b.physical_device_name) b
on a.name=b.database_name
where a.name not in ('tempdb','model')
and a.name not in ('msdb','master')
and a.state_desc = 'ONLINE'
group by a.name,b.backup_start_date,b.physical_device_name
order by b.backup_start_date desc
June 3, 2015 at 1:31 pm
First, what are you planning on using this query to do? I'm just curious because you said the goal was to return NULL for new DBs that have not been backed up, but the query seems to be trying to do a lot more than that.
If indeed you are only trying to see new databases that have no backups, then it could be done much more simply.
I note that while I was typing this up, you fixed the join piece, so I've omitted that part of my analysis 🙂
After that, the grouping for MAX uses the physical_device_name from backupmediafamily, which means for each database, there will be a row for every location used for one of that DB's full backups.
So, if my Test DB has had full backups to 40 different locations/physical devices, I'll have one MAX for each of those database name/physical location pairs for Test.
That issue exists for both the subquery and the outerquery. Additionally, the outer query is getting the MAX(backup_start_date), but is also grouping by backup_start_date, so the MAX isn't really doing anything.
At this point, I'd say let's get clear about the purpose of this query, and then we can rework it. It'll probably be much simpler than this.
Cheers!
June 4, 2015 at 1:27 am
Have a look into my blog post:
http://www.sql-aus-hamburg.de/en/tsql-66-monitoring-backup-solution-backup-history/
It might be something like a hint to your solution...
Give it try...
You might change this part to fit your requirement:
CASE WHEN bs.backup_start_date > DATEADD(dd,-1,getdate())
THEN 'Backup is current within a day'
WHEN bs.backup_start_date > DATEADD(dd,-7,getdate())
THEN 'Backup is current within a week'
ELSE '*****CHECK BACKUP!!!*****'
END as [Message],
June 4, 2015 at 3:28 am
Jacob and Northern DBA, thanks for answering my post, your time is very much appreciated. I'll explain what I'm looking to get.
Say I have a SQL instance. It has 6 user databases including 3 system databases for a total of 9. Someone creates a new database = 10 databases, so it won't return a backup date because the maintenance plan was selected for specific user databases to be backed up instead of all user databases. This creates a possible variable for the last backup date.
I'll run this on my instance, it picks up the NULL for the new database and I'd like it to return 1 value for 1 FULL backup record per database including master, model and MSDB for a total of 10 last database backups.
The reason why I'm using the MAX, which you may have fixed a big part of my failed query (Thanks!). Let's say I have an instance that has a failed backup plan, someone forgot to setup alerts on the failed job and I'm running this against Central Management Server (CMS). I'd like to see if that failed plan or "Last backup date" could possibly be beyond the 7 days, which is why I wasn't using the -7 or -1 date range. If the plan fails for 8 days, this would get missed.
I know there are a lot of variables in environment, yes all job's should have alerts, all databases should be backed up. However, this is a system that's had many DBA's. Many different companies managing the different servers and I'm slowly gaining access to many more systems. I'm still reading T-SQL fundamental's from Itzik Ben-Gan while working on fixing this so my research is still on-going. If I get this fixed I'll share in case it can help someone else out that may be in the same boat I'm in, which may be many new or DBA's in new jobs etc...
Thanks again for looking and replying to the post, I'll update this if I get a solution.
June 4, 2015 at 9:27 am
Ok, if the main point here is to get the most recent full backup times for databases (excluding tempdb), with NULL for databases that have not yet been backed up, something like this should do the trick:
WITH RecentBackups AS (
SELECT
MostRecentBackup=MAX(backup_start_date),
database_name
FROM msdb.dbo.backupset
WHERE type='d'
GROUP BY database_name)
SELECT
d.name,
rb.MostRecentBackup
FROM master.sys.databases d
LEFT JOIN RecentBackups RB
ON RB.database_name=d.name
WHERE d.name!='tempdb'
ORDER BY MostRecentBackup ASC
Cheers!
June 4, 2015 at 9:47 am
I'm sure you have had your questions answered, but if it helps, I use the following query to check my backups are running ok:
SELECT DISTINCT
D.database_id AS Database_Id ,
D.name AS Database_Name ,
D.recovery_model_desc ,
ISNULL(CONVERT(VARCHAR(25), MAX(F.backup_start_date) OVER ( PARTITION BY F.database_name ), 113),
'NEVER') AS Last_Full_Backup_Date ,
CASE D.recovery_model_desc
WHEN 'SIMPLE' THEN 'N/A'
ELSE ISNULL(CONVERT(VARCHAR(25), MAX(L.backup_start_date) OVER ( PARTITION BY L.database_name ), 113),
'NEVER')
END AS Last_Log_Backup_Date ,
ISNULL(CONVERT(VARCHAR(25), MAX(I.backup_start_date) OVER ( PARTITION BY I.database_name ), 113),
'NEVER') AS Last_Diff_Backup_Date
FROM sys.databases D
LEFT OUTER JOIN msdb..backupset F ON F.database_name = D.name
AND F.type = 'D'
LEFT OUTER JOIN msdb..backupset L ON L.database_name = D.name
AND L.type = 'L'
LEFT OUTER JOIN msdb..backupset I ON I.database_name = D.name
AND I.type = 'I'
WHERE D.name != 'tempdb'
ORDER BY D.database_id;
It shows the database name, the last full backup, last log backup and last differential backup date and time. I am sure you could adapt it to add any more data you require.
I then incorporate this into a procedure, which I call from a sql agent job each morning to make sure all backups have completed. I get an email if any databases are missing a full backup, missing a log backup when in full recovery, or the log backup hasn't happened for over a day for full recovery. I also check for differential backups if the databases uses them:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BackupCheckNotification] Script Date: 04/06/2015 16:43:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BackupCheckNotification]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @BackupsNotDone AS TABLE
(
ID INT IDENTITY(1, 1) ,
DatabaseID INT ,
DBName NVARCHAR(128) ,
RecoveryMode NVARCHAR(10) ,
LastFullBackupDate NVARCHAR(25) ,
LastLogBackupDate NVARCHAR(25) ,
LastDiffBackupDate NVARCHAR(25)
);
DECLARE @Body VARCHAR(MAX);
DECLARE @TableHead VARCHAR(MAX);
DECLARE @TableTail VARCHAR(MAX);
DECLARE @mailitem_id AS INT;
DECLARE @statusMsg AS VARCHAR(MAX);
DECLARE @Error AS VARCHAR(MAX);
DECLARE @Note AS VARCHAR(MAX);
DECLARE @mailSubject VARCHAR(500);
SET @mailSubject = 'Backups Missing On Server: ' + @@SERVERNAME;
SET @mailitem_id = NULL;
SET @statusMsg = NULL;
SET @Error = NULL;
SET @Note = NULL;
SET @TableTail = '</table></body></html>';
INSERT INTO @BackupsNotDone
SELECT BackupResults.Database_Id ,
BackupResults.Database_Name ,
BackupResults.recovery_model_desc ,
BackupResults.Last_Full_Backup_Date ,
BackupResults.Last_Log_Backup_Date ,
BackupResults.Last_Diff_Backup_Date
FROM ( SELECT DISTINCT
D.database_id AS Database_Id ,
D.name AS Database_Name ,
D.recovery_model_desc ,
ISNULL(CONVERT(VARCHAR(25), MAX(F.backup_start_date) OVER ( PARTITION BY F.database_name ), 113),
'NEVER') AS Last_Full_Backup_Date ,
CASE D.recovery_model_desc
WHEN 'SIMPLE' THEN 'N/A'
ELSE ISNULL(CONVERT(VARCHAR(25), MAX(L.backup_start_date) OVER ( PARTITION BY L.database_name ), 113),
'NEVER')
END AS Last_Log_Backup_Date ,
ISNULL(CONVERT(VARCHAR(25), MAX(I.backup_start_date) OVER ( PARTITION BY I.database_name ), 113),
'NEVER') AS Last_Diff_Backup_Date
FROM sys.databases D
LEFT OUTER JOIN msdb..backupset F ON F.database_name = D.name
AND F.type = 'D'
LEFT OUTER JOIN msdb..backupset L ON L.database_name = D.name
AND L.type = 'L'
LEFT OUTER JOIN msdb..backupset I ON I.database_name = D.name
AND I.type = 'I'
WHERE D.name != 'tempdb'
AND D.name != 'model'
) AS BackupResults
WHERE ( BackupResults.recovery_model_desc = 'FULL'
AND BackupResults.Last_Log_Backup_Date = 'Never'
)
OR ( BackupResults.recovery_model_desc = 'FULL'
AND BackupResults.Last_Log_Backup_Date < CONVERT(VARCHAR(25), DATEADD(DAY,
-1, GETDATE()), 113)
)
OR ( BackupResults.Last_Full_Backup_Date < CONVERT(VARCHAR(25), DATEADD(DAY,
-1, GETDATE()), 113)
AND BackupResults.Last_Diff_Backup_Date < CONVERT(VARCHAR(25), DATEADD(DAY,
-1, GETDATE()), 113) -- If no full backup AND no diff backup.
);
--HTML layout--
SET @TableHead = '<html><head>'
+ '<H2 style="color: #000000">Databases with no full or log Backup in the last 24 hrs:</H2>'
+ '<style>'
+ 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} '
+ '</style>' + '</head>'
+ '<body><table cellpadding=0 cellspacing=0 border=0>'
+ '<tr bgcolor=#F6FAFF>'
+ '<td align=center><b>Database ID</b></td>'
+ '<td align=center><b>Database Name</b></td>'
+ '<td align=center><b>Recovery Mode</b></td>'
+ '<td align=center><b>Last Full Backup</b></td>'
+ '<td align=center><b>Last Log Backup</b></td>'
+ '<td align=center><b>Last Differential Backup</b></td></tr>';
--Select information for the Report--
SELECT @Body = ( SELECT [@BackupsNotDone].DatabaseID AS
,
[@BackupsNotDone].DBName AS
,
[@BackupsNotDone].RecoveryMode AS
,
[@BackupsNotDone].LastFullBackupDate AS
,
[@BackupsNotDone].LastLogBackupDate AS
,
[@BackupsNotDone].LastDiffBackupDate AS
FROM @BackupsNotDone
FOR
XML RAW('tr') ,
ELEMENTS
);
SET @Body = REPLACE(@Body, '_x0020_', SPACE(1));
SET @Body = REPLACE(@Body, '_x003D_', '=');
SET @Body = REPLACE(@Body, '<tr><TRRow>1</TRRow>',
'<tr bgcolor=#C6CFFF>');
SET @Body = REPLACE(@Body, '<TRRow>0</TRRow>', '');
SET @Body = @TableHead + @Body + @TableTail;
-- return output--
-- SELECT @Body;
-- Only send if the table contains anything:
IF EXISTS ( SELECT 1
FROM @BackupsNotDone )
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients = N'<enter Email Addresses or use an operator>',
@subject = @mailSubject, @body = @Body,
@profile_name = '<mail profile name>', @body_format = HTML;
END;
END;
I've used the HTML formatting code from another thread on here. I can't remember who originally posted it, so credit to them.
June 4, 2015 at 10:12 am
Jacob, Northern DBA and Maddave, many thanks to all of you for contributing on finding a solution. I have more than enough info here to get what I'm looking for. Before I hit the button as a solution, can I mark a few since all of you have contributed to the solution? I'd like to make sure you all get credit for helping!
And thanks again for your time you have spent on answering this question!
June 4, 2015 at 1:03 pm
No problem. I'm glad we could help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply