Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Last Backup Taken, or Not Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 5:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:09 AM
Points: 61, Visits: 1,555
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 LastBackUpTaken
TEST No Backup Taken No Backup

Backup the database and this now shows as:

DatabaseName recovery_model BackupType LastBackUpTaken
TEST FULL Full Dec 11 2012 10:53AM

But 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 LastBackUpTaken
TEST FULL Full Dec 11 2012 10:53AM
TEST FULL Log Never

Regards,

DBA Pete.



Post #1394828
Posted Tuesday, December 11, 2012 10:04 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 10, 2014 4:15 AM
Points: 293, Visits: 816
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
Post #1395223
Posted Wednesday, December 12, 2012 3:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:23 AM
Points: 477, Visits: 1,892
Hi,

this is not so short anymore, but should do the trick...

;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 LastBackUpTaken
FROM c LEFT OUTER JOIN d
ON d.database_name = c.name AND c.type = d.BackupType
WHERE NOT ( c.type = 'Differential' AND d.LastBackUpTaken IS NULL) AND c.name <> 'tempdb'
GROUP BY c.Name, d.BackupType, d.LastBackUpTaken, c.rm, c.type
ORDER BY 1, 3

All corrections are welcome...


Best regards
karl
Post #1395539
Posted Sunday, December 16, 2012 9:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:09 AM
Points: 61, Visits: 1,555
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



Post #1397072
Posted Tuesday, December 18, 2012 6:21 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:23 AM
Points: 477, Visits: 1,892
Hi,

i had neglected bulk_logged databases... Here a better script

;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 LastBackUpTaken
FROM c LEFT OUTER JOIN d
ON d.database_name = c.name AND c.type = d.type
WHERE NOT ( c.type = 'I' AND d.LastBackUpTaken IS NULL) AND c.name <> 'tempdb'
GROUP BY c.Name, c.type, d.LastBackUpTaken, c.rm, d.type
ORDER BY c.Name, c.type



Best regards
karl
Post #1397747
Posted Tuesday, December 18, 2012 6:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:23 AM
Points: 477, Visits: 1,892
... and one more version.
This one works on SQL Server versions 2000-2012...

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 LastBackUpTaken
FROM ( 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.type
WHERE NOT ( c.type = 'I' AND d.LastBackUpTaken IS NULL) AND c.name <> 'tempdb'
GROUP BY c.Name, c.type, d.LastBackUpTaken, c.rm, d.type
ORDER BY 1, c.type



Best regards
karl
Post #1397764
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse