Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 14, 2016 9:23 AM
Points: 537, Visits: 1,134
Comments posted to this topic are about the item Last Backup Taken, or Not
Post #1305497
Posted Thursday, May 24, 2012 2:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 8, 2015 11:32 PM
Points: 1,132, Visits: 1,395
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: Thursday, July 14, 2016 12:16 PM
Points: 2, Visits: 104
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: Monday, May 16, 2016 9:57 AM
Points: 113, Visits: 427
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: Monday, May 16, 2016 9:57 AM
Points: 113, Visits: 427
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: Thursday, July 21, 2016 2:12 AM
Points: 482, Visits: 2,043
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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 8, 2015 11:32 PM
Points: 1,132, Visits: 1,395
@Karl Klingler - Nice. Thanks for sharing.

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

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 21, 2016 11:17 PM
Points: 63, Visits: 2,110
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 9, 2012 1:19 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 14, 2016 9:23 AM
Points: 537, Visits: 1,134
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