• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

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?
0
pma111
Asked:
pma111
3 Solutions
 
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
 
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now