If you’ve ever seen a full folder path, and file name in the MSDB system tables; one of the first things you need to do is get just the path, or just the file name. There’s a few ways you can get these returned, but this is the way I use.
-- get only the file names to the right of the slash. select right(physical_device_name, charindex('\', reverse('\' + physical_device_name)) - 1) from msdb..backupmediafamily -- get the path to the left of the last slash. basically gives you the full path to where the file resides. select reverse(right(reverse(physical_device_name), len(physical_device_name) - charindex('\',reverse(physical_device_name),1) + 1)) from msdb..backupmediafamily