The situation is going to come up when somebody deletes their site colleciton and wants it to be restored. But how can we tell what content database the site was in so that we can restore from a database backup?
The short answer is you can't easily.
It seemed most obvious that this information would be stored in a content database itself. So I ran a SQL trace and found deleting a site collection calls the stored proc
However you would be able to turn on auditing relating to site collections and find more information. The user interface may tell you this but my feeling is you would need custom code or a third party solution (either for backup or auditing).