-1

When querying for the last backup_finish_date with the following query (from SQL Server: how to query when the last transaction log backup has been taken?):

SELECT   d.name,
         d.recovery_model_desc,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.databases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
GROUP BY d.name, d.recovery_model_desc
ORDER BY backup_finish_date DESC

The backup_finish_date for all my databases is null, this is for DBs with a recovery mode of BULK_LOGGED, FULL or SIMPLE.

Does this imply that none of these databases has had their transaction log backed up (as suggested by the title of the linked question)?

Community
  • 1
  • 1
Zach Smith
  • 8,458
  • 13
  • 59
  • 133

1 Answers1

2

Yes, You are correct. If your backup_finish_date is null means it was never backed up. However, msdb..backupset can be modified/tempered. You can check last backup of the database with backup type.

SELECT   d.name,
         d.recovery_model_desc,
         b.type, -- type of backup
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.databases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
GROUP BY d.name, d.recovery_model_desc, b.type
ORDER BY backup_finish_date DESC

type Can be:
D = Database OR Full
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NULL.

Refer MSDN

p2k
  • 2,126
  • 4
  • 23
  • 39
  • Thanks. I'm trying to see if its possible to restore a table that I dropped. I'm assuming not? Once transaction logs have been truncated, are the log files saved? – Zach Smith Oct 14 '16 at 21:46
  • You should have the backup available to restore the dropped table. – p2k Oct 15 '16 at 05:57