Since this was beyond our local retention policy, we needed to restore the backup file from the virtual tape library.
Time to make friends with the Backup Administrator...
You want to tell your Backup Administrator the name of the server and the file, along with the date of the backup. This last part is slightly tricky. It is fairly typical for the file you are looking for to be on the following days' backup.
For example, if you wrote a SQL Backup file on January 1st, it probably isn't backed up until January 2nd. You may want to spend some time with your Backup Administrators to understand how your backups work and what the procedure is to get a file restored.
So how do you find out what the backup file was called two weeks ago? Query the msdb database, of course.
-- look for a backup filename from the past...
select top 30
from msdb.dbo.backupset s
join msdb.dbo.backupmediafamily m
on s.media_set_id = m.media_set_id
where s.database_name = 'YOUR_DATABASE_NAME_HERE'
and s.type = 'D'
order by s.backup_start_date desc
This query will return the names of the FULL backup files for the past thirty day. You can modify this query as needed.