SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jon Morisi / Last Backup Taken, or Not / Latest Posts

RE: Last Backup Taken, or Not

Karl Klingler — Tue, 18 Dec 2012 06:43:27 GMT

... and one more version. This one works on SQL Server versions 2000-2012...[code="sql"]SELECT c.Name AS DatabaseName, c.rm AS database_recovery_model, CASE c.type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' ELSE COALESCE(d.type,'No Backup') END AS BackupType, Isnull(d.LastBackUpTaken, 'Never') AS LastBackUpTakenFROM ( SELECT b.Name, DATABASEPROPERTYEX(b.Name, 'recovery') as rm, a.* FROM (SELECT type='D' UNION ALL SELECT 'L' UNION ALL SELECT 'I') a, master.dbo.sysdatabases b WHERE DATABASEPROPERTYEX(b.Name, 'recovery') IN ( 'FULL', 'BULK_LOGGED') OR a.type IN ( 'D', 'I') ) c LEFT OUTER JOIN ( SELECT database_name, --Isnull(Max(recovery_model),'No Backup Taken') AS backup_recovery_model, type, CONVERT(VARCHAR(17), CONVERT(DATETIME, Max(backup_finish_date), 113)) AS LastBackUpTaken FROM msdb.dbo.backupset GROUP BY database_name, type ) d ON d.database_name = c.name AND c.type = d.typeWHERE NOT ( c.type = 'I' AND d.LastBackUpTaken IS NULL) AND c.name <> 'tempdb'GROUP BY c.Name, c.type, d.LastBackUpTaken, c.rm, d.typeORDER BY 1, c.type[/code]

RE: Last Backup Taken, or Not

Karl Klingler — Tue, 18 Dec 2012 06:21:15 GMT

Hi,i had neglected bulk_logged databases... Here a better script[code="sql"];WITH a AS( SELECT type='D' UNION ALL SELECT 'L' UNION ALL SELECT 'I'), c AS( SELECT b.Name, DATABASEPROPERTYEX(b.Name, 'recovery') as rm, a.type FROM a, sys.sysdatabases b WHERE DATABASEPROPERTYEX(b.Name, 'recovery') IN ( 'FULL', 'BULK_LOGGED') OR a.type IN ( 'D', 'I') ), d AS( SELECT database_name, Isnull(Max(recovery_model),'No Backup Taken') AS backup_recovery_model, type, CONVERT(VARCHAR(17), CONVERT(DATETIME, Max(backup_finish_date), 113)) AS LastBackUpTaken FROM msdb.dbo.backupset GROUP BY database_name, type )SELECT c.Name AS DatabaseName, c.rm AS database_recovery_model, CASE c.type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' ELSE COALESCE(d.type,'No Backup') END AS BackupType, Isnull(d.LastBackUpTaken, 'Never') AS LastBackUpTakenFROM c LEFT OUTER JOIN d ON d.database_name = c.name AND c.type = d.typeWHERE NOT ( c.type = 'I' AND d.LastBackUpTaken IS NULL) AND c.name <> 'tempdb'GROUP BY c.Name, c.type, d.LastBackUpTaken, c.rm, d.typeORDER BY c.Name, c.type[/code]

RE: Last Backup Taken, or Not

DBA Pete — Sun, 16 Dec 2012 21:12:57 GMT

Hi Karl,That works.I also could not find a better way than to use an additional union to get this information.If anyone does come up with a better way then let us know.Thanks,DBA Pete

RE: Last Backup Taken, or Not

Karl Klingler — Wed, 12 Dec 2012 03:07:37 GMT

Hi,this is not so short anymore, but should do the trick...[code="sql"];WITH a AS (SELECT 'Full' AS type UNION ALL SELECT 'Log' UNION ALL SELECT 'Differential'), b AS( SELECT Name, DATABASEPROPERTYEX(Name, 'recovery') AS rm FROM sys.sysdatabases ), c AS( SELECT * FROM a , b WHERE rm = 'FULL' OR (rm = 'SIMPLE' AND type IN ( 'FULL', 'Differential') )), d AS( SELECT database_name, Isnull(Max(recovery_model),'No Backup Taken') AS backup_recovery_model, CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' ELSE COALESCE(type,'No Backup') END AS BackupType, CONVERT(VARCHAR(17), CONVERT(DATETIME, Max(backup_finish_date), 113)) AS LastBackUpTaken FROM msdb.dbo.backupset GROUP BY database_name, type )SELECT c.Name AS DatabaseName, c.rm AS database_recovery_model, c.type AS BackupType, Isnull(d.LastBackUpTaken, 'Never') AS LastBackUpTakenFROM c LEFT OUTER JOIN d ON d.database_name = c.name AND c.type = d.BackupTypeWHERE NOT ( c.type = 'Differential' AND d.LastBackUpTaken IS NULL) AND c.name <> 'tempdb'GROUP BY c.Name, d.BackupType, d.LastBackUpTaken, c.rm, c.typeORDER BY 1, 3[/code]All corrections are welcome... :-)

RE: Last Backup Taken, or Not

Jon.Morisi — Tue, 11 Dec 2012 10:04:01 GMT

I hear what you're saying, but I use this script on a regular basis to identify the issue. I do this by reviewing the output and if I see database name, full recovery, only a full backup and not another line indicating a transaction log backup, I know there's an issue. If you'd like to make an update to the script that inserts a line stating no t-log backup when the above is true, please feel free to contribute.Thanks,Jon

RE: Last Backup Taken, or Not

DBA Pete — Mon, 10 Dec 2012 17:40:58 GMT

Hi Jon,This is what I mean:Create a new database called TEST in full recovery mode. It has never been backed up and shows as:DatabaseName recovery_model BackupType LastBackUpTakenTEST No Backup Taken No Backup Backup the database and this now shows as:DatabaseName recovery_model BackupType LastBackUpTakenTEST FULL Full Dec 11 2012 10:53AMBut what I would find useful is to show that while the database had a full backup, it has never had a transaction log backup.This is important as I have seen many cases of a database set up by vendors where the database is in full recovery mode.It has regular full backups but never a transaction log backup. This often results in the transaction log being larger than the main data file.I think it would be useful to show something like the second row below:DatabaseName recovery_model BackupType LastBackUpTakenTEST FULL Full Dec 11 2012 10:53AMTEST FULL Log Never Regards,DBA Pete.

RE: Last Backup Taken, or Not

Jon.Morisi — Sun, 09 Dec 2012 13:19:39 GMT

That was actually the intent of the original script. You should try that one again.GL,Jon

RE: Last Backup Taken, or Not

DBA Pete — Sun, 09 Dec 2012 02:07:01 GMT

That is a great script Karl Klingler, very compact compared to the original script, but I notice both the original script and your script does not report on the important situation for a database in full recovery mode, where there is a last database backup but never a transaction log backup.Can you see if you can get your script to report on this situation?Also you can exclude the tempdb database.Regards,DBA Pete

RE: Last Backup Taken, or Not

Hardy21 — Wed, 30 May 2012 00:40:02 GMT

@Karl Klingler - Nice. Thanks for sharing.

RE: Last Backup Taken, or Not

Karl Klingler — Tue, 29 May 2012 08:13:50 GMT

Hi,shortening it up a bit...[code="sql"]SELECT T1.Name AS DatabaseName, DATABASEPROPERTYEX(T1.Name, 'recovery') AS database_recovery_model, Isnull(Max(T2.recovery_model),'No Backup Taken') AS backup_recovery_model, CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' ELSE COALESCE(type,'No Backup') END AS BackupType, Isnull(CONVERT(VARCHAR(17), CONVERT(DATETIME, Max(T2.backup_finish_date), 113)), 'Never') AS LastBackUpTaken FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2 ON T2.database_name = T1.name GROUP BY T1.Name, typeORDER BY T1.Name, type [/code]

RE: Last Backup Taken, or Not

jamie.opielski — Thu, 24 May 2012 09:59:19 GMT

Excellent Script, thanks for sharing.

RE: Last Backup Taken, or Not

krowley — Thu, 24 May 2012 07:29:47 GMT

I found a way to do this by addingAND T2.NAME is not null to the where clause of the top select. Still not sure if this is the best way to do this but it seems to be working.

RE: Last Backup Taken, or Not

krowley — Thu, 24 May 2012 07:16:29 GMT

We take image backups of the server partition for system restore purposes along with our SQL Server backups. Is there any way to have this script only consider SQL Server backups and not include the server Image backups?

RE: Last Backup Taken, or Not

gerry.geisel — Thu, 24 May 2012 06:38:10 GMT

Very nice "utility". Thanks!

RE: Last Backup Taken, or Not

Hardy21 — Thu, 24 May 2012 02:08:55 GMT

Nice one. Thanks for sharing

Last Backup Taken, or Not

Jon.Morisi — Wed, 23 May 2012 21:59:07 GMT

Comments posted to this topic are about the item [B]Last Backup Taken, or Not[/B]