The question will be not is it possible or not. No, the question is. Is it recommend or not.
The short answer is. No it is not recommend!
One of our customers wanted to have a restore of his database from last sunday. He did not wanted to have a „really“ restore. No, he wanted to have the same database parallel on his Sharepoint server under a different name. He had some reason why to do that. That is not interesting here.
First we will think… why not. A restore is simple. This is like the same as if you want to transfer your database from one server to another one. Everything is ghosted within your database. Should be not a problem. But it is not working. MOSS 2007 is very intelligent here. And this is our luck.
If you create a new site collection in your Central Administration MOSS 2007 will write an GUID to his configuration database. You can take a look within the configuration database in the „SiteMap“ table. You will find the column ID which is a primary key. The same GUID is stored in your new created site collection. Take a look in the database of your new site collection on the „Sites“ table. Here you will find the same GUID within the ID column.
I think at this point is everything clear. If you restore the same site collection to the same database server so the same GUID will be written to the „SiteMap“ table on the configuration database. Because of that your database server do not accept the new entry. That is good because if it would be allowed may some settings of your production environment will be away or destroyed.
Personally I would not change just the ID within the „Sites“ table in your new created site collection. You do not know whats going on behind. Is the GUID is used somewhere else.
If you need to restore a database and want the same site collection with a different timestamp up and running parallel use another environment.
To figure out this problem take a look in you event log.
This error is related to the Event ID: 5763, Category: Database. Source: Windows SharePoint Services. Description: Primary key violation on SQL Server instance <servername> in database <configuration database name>. Additional error information from SQL Server is included below. Violation of PRIMARY KEY constraint ‚PK_SiteMap‘. Cannot insert duplicate key in object ‚dbo.SiteMap‘. The statement has been terminated.
Timo