Short answer: you can obtain some very good results, but only under certain conditions and absolutely not even close to what is shown in the linked video clip.
My company, Amped Software, develops image and video processing software for forensic and intelligence applications, so basically we are the real world counterpart of the CSI software.
With reference to the general problem of quality enhancement, I can tell you that for our market it is a huge problem to live up to the expectations created by TV series and Hollywood movies. You can see on our samples page that sometimes the results we are able to get are really amazing, but it is important to understand that we can obtain them only under some conditions: if there is information that is covered by disturbs, but it is there, we are able to recover it. If there is no information, we can't and we must not recreate it. In this particular application is essential not only getting the results from a visual point of view, but also following a scientific workflow that must be accepted by the court.
In 2010 I presented a research describing issues and results on almost 200 cases I've worked on and the final result was the following:
- in more than 50% of the cases there is nothing to do (for example recovering a license plate that is 5x2 pixels is completely impossible with any software on the world);
- in about 30% of the cases we can get some little result (for example restoring some letter of a license plate or improve the overall appearance of a face);
- in 10% of the cases you get good results (you get most of the license plate, for example).
Please note that all these cases had severe quality issues. If their quality was good, we weren't asked to work on them.
For what regards specifically resolution enhancement:
- when you zoom on an image you are interpolating missing pixels: from a single image you can improve visually the appearance of the image but you will not add any real detail;
- super resolution techniques may yield good results under certain conditions: you should have enough frames, shifted by a non integer amount of pixels and preferably with few compression artifacts. In the best case you can expect good results within 2x and 3x zoom.
What is shown in the video clip can be possible only if the original video has been shoot at several megapixels and then you will have the resolution to zoom very close (more or less like you do on Google Maps). Of course, at that point there still would be other problems, like the right focus, low light condition, the fact that the perspective of the eye is different from that of the whole subject in the video, just to mention a few.
Edit 2015-01-01: you can read a more in depth explanation of this on our blog here: http://blog.ampedsoftware.com/2014/12/15/the-untold-secrets-of-forensic-video-enhancement-myth-versus-science/