So, what about it?
First the basics:
For each Web Application in MOSS 2007 you have at least 1 database.
In that database you can put your Site Collections. (How to dedicate databases for Site Collections, that you can read here.)
And now in Fight Club style:
The first rule of MOSS 2007 is: you do not attach the same content database twice to the same farm
The second rule of MOSS 2007 is: you DO not attach the same content database twice to the same farm
So, why is that?
Well, every Site Collection has an ID (an ugly GUID of course). That GUID is registered in the farm's Config database. So this would mean when you attach the same database twice (after restoring it under a different name in SQL Server), you would have duplicate GUID's. And you don't want to have this. Because this will take your original site down as well.
So, what to do?
Well, when you want a Site Collection twice in MOSS 2007 (let's say a copy for testing or training purposes), you should take a backup with the stsadm commandline tool (stsadm -o backup -url http://sharepoint/sites/trycatch -filename D:\backup.bak) and then restore it to a different name (stsadm -o restore -url http://sharepoint/sites/trycatch2 -filename D:\backup.bak).
When restoring, a new ID (your ugly GUID) will be created, and so no conflict will arise in the Config database.
One problem though: It's not possible within the same database of the originating Site Collection...
So, why is that?
Every Site Collection has a SECOND ID. Known as the Webrootid. This is basically again a GUID, but this one doesn't have to be unique within the farm, but within a database. When doing the commandline restore action, NO new Webrootid will be created.
So, what to do?
When restoring a Site Collection on MOSS 2007 with a different name, be sure an extra (read > new) content database is ready for receiving Site Collections.
Conclusion
Every Site Collection has 2 GUID's. The First (the ID) has to be unique within a farm. The Second (the Webrootid) has to be unique within a database.
Cheers
Tom
ps: And the eighth and final rule: if this is your first time at Fight Club, you have to fight.