Two SQL Server databases setup with same .mdf and .ldf files In a SQL Server 2008 R2 instance two databases (the second one being a restore/copy of the first one) have been setup against the same .mdf and .ldf files. 

How would I go about either deleting the second one or copying/moving the files for the second one without affecting the first (production!) db?

Update: Indeed one of them is Standby/Read-Only.
For a database in SQL Server, there's a setting called Auto-Close, which is meant to minimize the amount of resources used while SQL Server is running, but no users are actively doing things.

This setting is enabled by default in Express Edition instances, as those are usually running on developer workstations where performance isn't critical, and resources are needed for other applications.

When resources are released by this feature, the implementation includes closing the file handles associated with the database files (*.mdf, *.ldf, etc) on disk. This means that any other application can potentially open them, including the same or another instance of SQL Server.

In testing out this scenario, I found out there's no constraint inside SQL Server (at least in this particular version) that prevents attaching a database to the same set of files on disk, as long as they're readable. While this isn't a good/safe thing, of course, it's a relief to know that only one of the databases can be written to at a time, because the files will be exclusively locked by SQL Server when one of the databases is opened. The other database(s) will not be accessible.

To undo the situation:


Detach the database(s) you don't want. This removes the database from the instance, but leaves the files on disk alone (whereas DROP DATABASE does remove the files on disk). It may be necessary to restart the instance to get it to recognize that the database you want to keep is accessible.
Disable auto-close for the main database, to better prevent this from happening again. You may also want to disable it in the Model system database (if applicable) so new databases don't inherit this setting. For a production system, I recommend making sure auto-close is disabled on every database; I like to use Policy-Based Management to do the checking periodically, but there are lots of ways to go about it.