DECLARE @FileName VARCHAR(255) = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SS2014\MSSQL\DATA\model.mdf'
SELECT REVERSE(LEFT(REVERSE(@FileName), CHARINDEX('\', REVERSE(@FileName)) -1))