April 7, 2012 at 11:00 am
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]
April 7, 2012 at 11:28 am
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" 😉
April 7, 2012 at 11:29 am
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
April 7, 2012 at 6:26 pm
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]
April 8, 2012 at 4:59 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply