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.

Answer 2

It's true that the MDF file contains the database but things are more complex. (A single db can actually span several mdf files)

You cannot treat or think of Sql Server as a file-based db like MS Access or some other desktop databases. You have to think in terms of the server. The server controls a number of named databases and each database is backed up by at least one data file (.mdf) and one log file (.ldf). You reference the data through the server first, then a registration of the database through the server and the files themselves should be your last concern.

i think that you're probably trying to recreate a database on the server with a name that is already in use. Yes, the .mdf files can have the same names at different locations but that actual name of the database as far as the server sees it must be unique within that server.

If you're familiar with web servers, here's an analogy: Would you expect to be able to copy html files to different folders and then simply navigate to those different folders with your browser? Of course not. You first have to create a new virtual folder that the web server recognizes, and perhaps tweak some other settings, and then you'd be able to browse your "copy". The database in Sql Server is kinda like your virtual folder in web servers, in that the data can be "copied" and then "seen" as different data stores.

Answer 3

Your problem is composed of two issues:

1. You're getting an error when 'using' your database file (via AttachDbFilename). The error sais 'CREATE FILE .... failed'.
2. You're unable to 'use' a database file MyDataBaseFile.mdf in case the file with the same name exists in Data directory of SQL Server

Let's deal with the first one. The process of 'using' a database file is actually attaching a database to your server instance. This operation (database attachment) is transparent to your application and is possible only with Sql Server Express Edition as far as I know. When you attach a database file you need a log file which is created 'on the fly' in case it does not exists - as soon as each SQL Server database has to have a log file. I can't say why your SQL Server is unable to create a log file for you - weather it's a lack of space, lack of permissions or whatever. But at least you have an idea of why this file is created for. Attaching database is implemented as a special case of database creation - that's why a CREATE DATABASE statement is executed.

Now we come to the second issue. Database file name is usually derived from database name. Hence in case you don't edit file names during database creation intentionally having two similary named database files means that you will get a database with the same name. In case one of the files reside in Data directory of SQL Server it's most likely that you have it used by the server. When you try to 'use' a database file (via AttachDbFilename) it actually results in an attempt to create a database with duplicate name. This attempt will always fail.

Hope this helps.

Answer 4

What worked from us was - user running the SQL 2008 Service didn't have permission on the folder specified.