For some reason, when I ran this query in it's original form, it returned NULL rather than the name of the latest .bak file

I changed the last line of the final select to:

WHERE FileNames like ''+'%'+ @DatabaseName +'%'+''

It seems to be working ok now

a useful code snippet - thanks 🙂