SQL Server: loading database file

Question

I'm using SQL Server 2008. I should be able to "connect" to a user-specified database file (mdf) (using the AttachDbFilename section of the connection string) and save a copy of the selected file. I also have to work with the contents of the database.

If I got it right, a single mdf file represents a complete database with tables, stored procedures, etc. But then, if I have two files with the same name in different folders (one in the DATA folder of SQL Server and the other in the root of C:), and I try to load the file under C: I get an error stating that a database already exists with that name.

I renamed the file under C:, but now I'm getting an error:

"CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create physical file 'C:/myDatabaseFile_log.ldf'. Could not open new database 'C:/MYDATABASEFILE.MDF'. CREATE DATABASE is aborted. An attempt attach an auto-named database for file 'C:/myDatabaseFile.mdf' failed. A database with the same name exists or specified file cannot be opened, or it is located on UNC share."

Please note: I'm trying to OPEN the database and not trying to create it.
So what am I doing wrong? Did I misunderstand something? How do these database file work (I mean, how to use them)?

Answer 1

You seem to be under the impression that databases are like Word documents or text files, to be opened and closed at will within an application instance. This isn't how it works.

Instead, there is typically exactly one instance of the database server program (you could install multiple instances, but it's not like running two copies of a normal program). You can think of it more as if your databases must be registered inside this server instance, and you can only register one database with a given name for the server. What happens from the server's perspective when you attach to an MDF file is that a new database is created (the CREATE DATABASE command is executed) and told to use this MDF file for the schema and data.

I suggest you get Sql Server Management Studio and connect to the server running on your local system. You'll likely find there's already a database there with name you want.