Is it possible to determine what content database a deleted SharePoint site collection existed in?

Question

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?

Answer 1

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 proc_DeleteSite. This calls the stored proc proc_DeleteSiteInternal. Both of them just do a whole lot of DELETE T-SQL statements and update SharePoint's internal log so this isn't much use.

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).