log file backup query MSSQL

Is there an easy way to query last backup date for log-files for a specific SQL Server instance (i.e. for all databases), but only the log file as opposed the database files? This is a MSSQL 2005 instance.

Also – as log files are for point in time recovery – should you be backing them up regularly, i.e. hourly, or do you just back them up each day – and which point you could restore to any given point during that day?
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

for that you have to opt for differential back up with full recovery mode.

Differential backup will store the backup from last full back up and at the point of time recovery you can get the data from last differential back up.

If the differential back up is hourly then you can get that much point of data by this method
0
pma111Author Commented:
How can you query last backup dates for the logfiles
0
Vikas GargBusiness Intelligence DeveloperCommented:
0
Scott PletcherSenior DBACommented:
>> Is there an easy way to query last backup date for log-files for a specific SQL Server instance (i.e. for all databases), but only the log file as opposed the database files? <<

Yes.  SQL writes a history of all backups to its own internal tables, and it distinguishes the type of backup: full, differential, log, etc..

SELECT
    bs.database_name,
    MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_db_backup,
    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup
FROM msdb.dbo.backupset bs
WHERE
    bs.type IN ('D', 'L') --db or log backup
GROUP BY
    bs.database_name
ORDER BY
    database_name


>> Also – as log files are for point in time recovery – should you be backing them up regularly, i.e. hourly, or do you just back them up each day – and which point you could restore to any given point during that day? <<

You should back them up "regularly".  How often that is depends primarily on how much time's worth of data you can afford to lose if something fatally damages the original db and you have to recover.  If you do a lot of logging, you also might need to consider total log size.  Until the log file is backed up, that log space can't be reused, so the log keeps getting bigger.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
Just to correct any misunderstanding if you want true point-in-time restores you have to use Transaction Log backups and not Differential backups.  You can optionally use a Differential backup to help, but you have to have a Transaction log backup to have true point-in-time restores.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.