What LSN syntax to use when selecting from backupset?

Question

I have a SQL Server database where I want to find the TLOG for a specific LSN. All is good, I found a query that could give me that information but I got a syntax error. The query was:

use msdb
go
select  s.backup_set_id,
      s.first_lsn,
      s.last_lsn,
      s.database_name,
      s.type,
      f.physical_device_name
from    backupset s join backupmediafamily f
      on s.media_set_id = f.media_set_id
where   s.database_name = 'PRD' and s.type = 'L' and '00053837:0002C7F8:007A' between first_lsn and last_lsn
go

This threw an error saying:

Incorrect syntax near ':'.

What LSN syntax do I have to use here? Even with quotes it does not accept it when this is the actual LSN.

Thanks!

Answer 1

The query you posted does not have any syntax error, but it dies with a conversion error, since the backupset table has the lsn:s as numeric(25,0).

I will have to admit that I did not know the answer myself, but as I found that a bit embarrassing, I went to Google. I found a post by the late Robert Davis, which seems to explain how to do the conversion: https://www.sqlservercentral.com/blogs/day-11-of-31-days-of-disaster-converting-lsn-formats