1

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.

enter image description here

Improve this question
2
  • 5
    Can't be. Are you sure the files are in the same directory?
    – James Anderson
    Commented Jul 11, 2014 at 7:29
  • Files would be on different directory as already said. More over two database are physically different from each other. You can very well take one offline and move the files to different location but it would contradict with same file name. You could take backup of other database( then remove it) and restore it with different mdf and ldf name however logical name would not change. In that case it would not contradict
    – Shanky
    Commented Jul 11, 2014 at 9:11

1 Answer 1

Reset to default
2

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:

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

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.