Due to vendor app issues, I've been needing to restore a small (200MB) database several times to a point-in-time. The DB is in full-recovery mode. There are no differentials, no log shipping, but the vendor will run copy-only backups occasionally.
Prior to restoring, I take a full backup in case something goes wrong with the restore, I can at least get back to where I was (admittedly, I'm rethinking this action). Post-restore, I take another full backup to initialize the log for transaction log backups.
The first point-in-time restore always works. If I need to do another, SSMS (17.9 or 18.2) refuses, claiming the LSN chain is broken. It's not, as I manually build the restore script using the last full and the subsequent logs. So I'm looking to build a query that will give me the files needed for restoring to a point-in-time.
I join msdb.dbo.backupset for the backups to msdb.dbo.backupmediafamily for the filenames on media_set_id. But how does one know where to start and stop? I've tried using the database_backup_lsn to gather other log backups with the same, as well as the full that has the same checkpoint_lsn. While that works most of the time, I can get an extra log in there if the automated backups kick in before I get my post-restore full done.
I do have my incorrect SQL I could post, but someone has to already have done this, right?