SQL Express & IIS - A database won't attach if you manually detached it?

Question

Since the answers did not really cover the problem, I posted this on ASP.Net and completly rephrased it. I will post the edited question here:

---

I have been using the attachDB connection string and I usually deploy to IIS. The site works fine, however, I made some changes to the database and the newest version would not copy as it said file in use.

I opened up SQL management studio and saw that it was mounted so I did a dettach.

I was then able to copy the new version without problem, however, when I next run the site, I get:

Unable to open the physical file "C:\inetpub\wwwroot\vs\App_Data\aspnetdb.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)". An attempt to attach an auto-named database for file C:\inetpub\wwwroot\vs\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

And, if I try to access .Net users or the other few options within IIS Manager, I get the following error:

.NET Users

There was an error while performing this operation.

Details:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

OK

I have tried recycling the application pool, restarting the SQL instance and even restarting the computer.

Nothing helps and I cannot figure out what is wrong... Where does it remember where previous databases are connected and why doesn't it automatically reattach the database?... Someone said that they auto detach after 2 1/2 hours, however I waited 5 hours when a database wasn't in use and SQL Manager showed it was still attached.

When I manually reattach the database, everything works fine.

Answer 1

Are you certain there isn't already a database with the same name attached to the SQL Express instance you are pointing your site at?

Also, does the identity the site is running under when it attempts to attach the database have admin rights in SQL Express? If not, this command won't work (I'm not sure what error it gives in that situation, but "Access Denied" would sound reasonable).

The page "SQL Server 2005 Express Edition User Instances" seems to give a good overview of the issues and workarounds.

---

Edit to add

Could this be the issue:

An error will be generated if a log file exists in the same directory as the data file and the 'database' keyword is used when attaching the primary data file. In this case, remove the log file. Once the database is attached, a new log file will be automatically generated based on the physical path.

Taken from the documentation for SqlConnectionStringBuilder.AttachDBFileName Property

You'll need to delete the log file before you can re-attach the database through this method.

Answer 2

When you ask a database to be attached on-the-fly to a SQL Express instance using the AttachDBFileName connections string the application will not connect to the SQL Expres sinstance at all, but instead it will connect to a child instance, which is an new instance created specificaly for the user requesting the attach operation. See SQL Server 2005 Express Edition User Instances. This child instance will attach the database and will continue to run for up two one hour, after which it will shut itself down.

When you try to connect from 'enterprise manager' you will not be able to connect to the child instance (is realy complicated to connect explicitly to one, so you cannot accidentaly do it), you are connecting to the parent instance and messing with the database.

To summarize, either stick with the RANU model and use AttachDBFileName, or use a normal database operations mode and manage the database from the SSMS. Don't mix the two.