One other reason this can happen is if you have the data folder on the root of a drive (i.e. a drive used exclusively for MDF files).
For example, if we had the data drive defaulting to D:\, it may show this as the MDF file when restoring the database:
You may have noticed a missing backslash. which can throw the activation error. Though this is purely SSMS creating an invalid restore script, the solution is to either script the restore manually or click the "Relocate All Files To Folder" checkbox and in the Data option add the backslash after "D:", so it reads:
D:\ <-- add the missing backslash
Just in case anyone else has this issue, this will hopefully save you hours of troubleshooting if you come across this article while googling for an answer.