July 21, 2011 at 2:08 am
Is there any query to find the last FULL and DIFFERENTIAL backup ?
SELECT Database_Name,
CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup,
DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM MSDB.dbo.BackupSet
WHERE Type = 'd'
GROUP BY Database_Name
ORDER BY 3 DESC
Type 'd' denotes Database and 'I' denotes differential backup. Is it correct?Please correct me if am wrong.
July 21, 2011 at 2:20 am
If you're using SQL server 2008 you can use policy-based management to evaluate last backup time (full, diff, or transaction). Use the database facet and property for which backup type you need. I'd post example but I'm at nasatweetup right now. I can post example later today.
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
July 21, 2011 at 2:23 am
Books online has it all : http://msdn.microsoft.com/en-us/library/ms186299.aspx
Your assumptions are correct.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 21, 2011 at 2:29 am
Okei thanks!!!just to confirm, the following would list the last FULL backup datetime?
SELECT Database_Name,
CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup,
DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM MSDB.dbo.BackupSet
WHERE Type = 'd'
GROUP BY Database_Name
ORDER BY 3 DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply