Francis - No easy answers to this one. If it was easy, there would be no transfers, ever. The title of your thread is appropriately titled because very few understand just how competitive it is to get ANY playing time hence the high transfer rates out of some schools. As others have suggested the devil is in the details of the specific school, NCAA Division and their respective program. You've got to do a lot of research.
To answer your question directly, the closest it comes to a "D1 fairytale" (your words) is with the Patriot League (Army, Navy, Lafayette, Lehigh, Bucknell, and Holy Cross) as well as the Ivy League (Brown, Columbia, Cornell, Dartmouth, Harvard, Penn, Princeton, and Yale). Very few transfers-out and a very high 4 year participation rate, however challenging in other ways. Signing an NLI is childsplay when you consider what the Army and Navy guys are committing to. Again, it is on a case by case basis.