Thanks a bunch for all your guidance!!!
I finally got the site working :D

The problem was that there were no records in the FolderPermission table. And so, when a user requests a file, the LinkClick handler cannot extract it because of insufficient permissions. And hence the error, "Resource cannot be found". I wish the error message was more specific to begin with.

I was able to find this out by stepping through the code for the LinkClick handler, i.e. FileServerHandler.vb. To step through the code, I had to:
1. Copy the DNN Source to my IIS root (wwwroot folder)
2. Copy the contents of the folder "Website" to my website folder "Mysite", leaving out the config files.
3. Open the solution file for DNN project.
4. Remove the project http://localhost/Website
5. Add project/website http://localhost/Mysite
6. Open http://localhost/Mysite in a browser
7. Debug > Attach to process - aspnet_wp.exe

I had to copy the DNN source folders to my IIS root, as I did not want to change the location of MySite. That would have messed up the folder locations of my XS Document Library. There could be a better way to accomplish the same, but I was not in a mood for any more trials-and-errors.

Thanks!
No, a recursive synchronization of the file manager did not help. In fact, with an admin login, all the file manager buttons were disabled for me due to lack of permissions. I tried full sync with host login, but didn't make any difference.
I had to bulk insert records from my backup database to the live database - for the FolderPermission table.