Hi Team,
please help me with the query to check my production databses which are in full recovery, but still not part of log backups.
please help me with the query.
Hi Team,
please help me with the query to check my production databses which are in full recovery, but still not part of log backups.
please help me with the query.
The info you need is in the msdb database in backup*** tables. Run the following query:
;WITH cte AS
(
SELECT type ,
database_name ,
MAX(backup_finish_date) LatestBackupTime
FROM msdb.dbo.backupmediafamily f
INNER JOIN msdb.dbo.backupset b ON
f.media_set_id = b.media_set_id
GROUP BY
type, database_name
)
SELECT c1.*
FROM
cte c1
WHERE
c1.type = 'D'
AND NOT EXISTS
(
SELECT *
FROM cte c2
WHERE
c2.database_name = c1.database_name
AND c2.type = 'L'
);
SELECT database_name,
MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS Last_Full_Backup,
MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS Last_Log_Backup,
MAX(CASE WHEN type = 'I' THEN backup_finish_date END) AS Last_Diff_Backup
FROM msdb.dbo.backupset
GROUP BY database_name
ORDER BY CASE WHEN MAX(CASE WHEN type = 'L' THEN backup_finish_date END) IS NULL
THEN 0 ELSE 1 END, database_name
simple recovery databases should not be shown in this. somehow, they are also showing in out put. please help.
SELECT bs.database_name,
MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS Last_Full_Backup,
MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS Last_Log_Backup,
MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS Last_Diff_Backup
FROM msdb.dbo.backupset bs
INNER JOIN sys.databases db ON db.name = bs.database_name
WHERE db.recovery_model_desc <> 'SIMPLE'
GROUP BY bs.database_name
ORDER BY CASE WHEN MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) IS NULL
THEN 0 ELSE 1 END, bs.database_name
it is very close. thank you. but my requirement is:
I want to identify the list of databases, which are not receiving log backups. the Query is giving all log backups. please could you tweak once more.
;WITH cte AS
(
SELECT type ,
database_name ,
MAX(backup_finish_date) LatestBackupTime
FROM msdb.dbo.backupmediafamily f
INNER JOIN msdb.dbo.backupset b ON
f.media_set_id = b.media_set_id
INNER JOIN sys.databases d ON
d.name = b.database_name
WHERE
d.recovery_model_desc NOT IN ( 'SIMPLE' )
GROUP BY
type, database_name
)
SELECT c1.*
FROM
cte c1
WHERE
c1.type = 'D'
AND NOT EXISTS
(
SELECT *
FROM cte c2
WHERE
c2.database_name = c1.database_name
AND c2.type = 'L'
);
Seems it is giving full backups. not log backups
Something like this?
Use msdb;
Go
--==== Get list of databases
With databaseList
As (
Select d.name
From sys.databases d
Where d.recovery_model_desc <> 'SIMPLE'
)
--==== Get Log Backups for all databases
, logBackups
As (
Select bs.database_name
, bs.backup_finish_date
, rowNumber = row_number() over(Partition By bs.database_name Order By bs.backup_finish_date desc)
From dbo.backupset bs
Where bs.[type] = 'L'
)
--==== Identify databases with no log backup in past 24 hours
Select *
From databaseList dl
Left Join logBackups lb On lb.database_name = dl.name
And lb.rowNumber = 1
Where lb.backup_finish_date Is Null
Or lb.backup_finish_date < dateadd(hour, -24, getdate());
No log backups at all? Here it is:
SELECT bs.database_name,
MAX(CASE WHEN bs.type = βDβ THEN bs.backup_finish_date END) AS Last_Full_Backup,
MAX(CASE WHEN bs.type = βLβ THEN bs.backup_finish_date END) AS Last_Log_Backup,
MAX(CASE WHEN bs.type = βIβ THEN bs.backup_finish_date END) AS Last_Diff_Backup
FROM msdb.dbo.backupset bs
INNER JOIN sys.databases db ON db.name = bs.database_name
WHERE db.recovery_model_desc <> 'SIMPLEβ
GROUP BY bs.database_name
HAVING MAX(CASE WHEN bs.type = βLβ THEN bs.backup_finish_date END) IS NULL
ORDER BY CASE WHEN MAX(CASE WHEN bs.type = βLβ THEN bs.backup_finish_date END) IS NULL
THEN 0 ELSE 1 END, bs.database_name
The ORDER BY I used above would list all dbs without a log backup first, then list the ones that did. Thus, if the first row has a log backup date, then there were no dbs without a log backup.
At any rate, are you sure you don't want those with no log backup after the last full backup? That is, where perhaps the most recent log backup(s) is(are) missing, even if a log backup occurred earlier.