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 Wednesday, May 23, 2012 9:59 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 280, Visits: 763
Comments posted to this topic are about the item Last Backup Taken, or Not
Post #1305497
Posted Thursday, May 24, 2012 2:08 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:42 AM
Points: 890, Visits: 1,179
Nice one. Thanks for sharing

Thanks
Post #1305580
Posted Thursday, May 24, 2012 6:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 2, Visits: 66
Very nice "utility". Thanks!
Post #1305779
Posted Thursday, May 24, 2012 7:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 8:36 AM
Points: 113, Visits: 415
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?
Post #1305809
Posted Thursday, May 24, 2012 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 8:36 AM
Points: 113, Visits: 415
I found a way to do this by adding
AND 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.
Post #1305825
Posted Thursday, May 24, 2012 9:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 24, 2012 9:58 AM
Points: 1, Visits: 0
Excellent Script, thanks for sharing.
Post #1305980
Posted Tuesday, May 29, 2012 8:13 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 7:49 AM
Points: 476, Visits: 1,826
Hi,

shortening it up a bit...

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, type
ORDER BY T1.Name, type



Best regards
karl
Post #1307801
Posted Wednesday, May 30, 2012 12:40 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:42 AM
Points: 890, Visits: 1,179
@Karl Klingler - Nice. Thanks for sharing.

Thanks
Post #1308121
Posted Sunday, December 09, 2012 2:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 2:09 AM
Points: 60, Visits: 1,489
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



Post #1394386
Posted Sunday, December 09, 2012 1:19 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 280, Visits: 763
That was actually the intent of the original script. You should try that one again.

GL,
Jon
Post #1394414
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse