Why cant I create a database with mdf file?

Question

I have a feature in visual studio which I have never really understood.

I am able to 'right-click' on the App_Data folder and then I am able to select 'Sql Server Database'. I dont really understand how I can create a db using just an mdf file? I thought the sql service was responsible for manipulating these files? And that you have to create the db using the 'sql management studio' interface?

Im confised as to how we can essentially just have a lone file and run a db from it for a website?

Answer 1

You're application is still connecting through the SQL Server service but it can instruct the service to attach to a specific mdf file at runtime through a connection string. e.g.:

"Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;"

Answer 2

When you installed Visual Studio you also installed SQL Server Express. This gives you the ability to create and use SQL Server databases.

If you were to deploy your application you would then also need to have a SQL Server (Express) install on the web-server you were using (at least because you don't want to use your development database server in production).

Answer 3

All SQL Server databases are represented as one (or more) .mdf files and usually .ldf files as well (ldf is the log file, mdf is the data file.) An .mdf file is a file but it is highly structured and maintained by SQL Server. The way SQL Server uses this file is very different from serving up CSV data, as a simple example. SQL Server breaks the file into pages and serves the requests for reads and writes via this paging system. It is indeed like a file system within a file system. If you think about it it does all make sense. The data has to get persisted to disk and that disk persistence has to come in the form of a file or files.