Slect DB name , Recovery Model & last back up type

Question

Post reply

Slect DB name , Recovery Model & last back up type

Minnesota - Viking

Hall of Fame

Points: 3469
More actions
April 7, 2012 at 11:00 am

#267139

I have this

SELECT name,cmptlevel,DATABASEPROPERTYEX(name,'Recovery')AS RecoveryModel, DATABASEPROPERTYEX(name,'Status') as Status FROM sysdatabases

This gives me DB name info along with compaitability & Recovery model.

Is it possible to append backup info to it. for example if the last known backup for Master DB is FULL & for AdvWorks is transaction log

My result wud be like

Master .........100.........SIMPLE.......ONLINE.......FULL AdvWorks......100..........FULL..........ONLINE......TranLog

My target is to verify in one query if all DBs thatare in FULL recovery model have transaction logs in place.

Thanks

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 233792 More actions · Answer 1

The backup related tables you require are in the MSDB. Use the following query



Select database_name, Compatibility_level, Recovery_model,

DATABASEPROPERTYEX(database_name,'Status'), Type

from msdb.dbo.backupset

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Sysdatabases is deprecated, should not be used, included only for backward compatibility with SQL 2000, will be removed in a future version.

SELECT name, recovery_model_desc, status_desc from sys.databases

For log backups you can check the msdb backup tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Minnesota - Viking Hall of Fame Points: 3469 More actions · Answer 3

I was able to figure out TSQL for what I want. ThankQ Gila monster & perry Whittle.

here it is



SELECT

database_name [Database Name],

Compatibility_level [Compaitability],

DATABASEPROPERTYEX(database_name,'Status') [Status],

Recovery_model [Recovery Model],

[Backup Type] =

CASE Type

WHEN 'D' THEN 'Full '

WHEN 'I' THEN 'Differential '

WHEN 'L' THEN 'Log'

WHEN 'F' THEN 'File or filegroup'

WHEN 'G' THEN 'Differential file'

WHEN 'P' THEN 'Partial'

WHEN 'Q' THEN 'Differential partial'

ELSE 'Unknown'

END,

MAX(backup_finish_date)[Backup Finish Date],

DATEDIFF(day,MAX(backup_finish_date),GETDATE())AS [Number of days old]

FROM msdb.dbo.backupset

GROUP BY  database_name, recovery_model,compatibility_level, DATABASEPROPERTYEX(database_name,'Status'),type

But here is the problem. If you look at the results in the attached image, the test DB is supposed to show only the latest backup set which should be the latest log backup taken today, but Test DB has 3 columns, what should be edited in my SQL so that I get ONLY latest backupset info.

am I not selecting MAX(backup_finish_date), I'm ..!

Please suggest.

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Because you've been changing the recovery model of the DB and you're grouping by the recovery model and the backup type. Thought you wanted the latest log backup, not the latest of any type.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass