A question about understanding the mdf database file

[Tony Johansson]

I can right click on the project and select Add->New Item and then in the
Add New Item dialog I can select the template
Service-based Database which will create a mdf file.

This is my observations for this matter.
1. I must have the SQL server express service running otherwise I get error
when I try to create the Service-based Database
2. The mdf file is stored local in my project.
3. If I log in to SQL Server 2008 express this created database can't be
found.within SQL server

Now I can also create a database in the following way.
1. Bring up the Server Explorer
2. Right click on the DataConnection in Server Explorer and select Create
New Sql Server Database.
3. The created database is shown in server explorer as homepc\sqlexpress
myCreatedDatabase.dbo
4. If I log in to SQL Server 2008 express this created database is found
here

Now to my first questions it seems to me that when I create the
Service-based Database the SQL Server is involved in some way even though
the actual database is not stored within the SQL Server because I get an
error when the SQL Server service was not running

My second question what is the difference between these two examples ?
I mean is the difference any more then just that in the first example the
mdf file is stored local in the project and
in the second example the mdf file is not stored local but somewhere else on
disk and the actual created database is shown in the SQL Server.

In addition I can load the mdf file into the SQL Server so will be visible
there.

//Tony

 

[Arne Vajhøj]

I can right click on the project and select Add->New Item and then in the
Add New Item dialog I can select the template
Service-based Database which will create a mdf file.

This is my observations for this matter.
1. I must have the SQL server express service running otherwise I get error
when I try to create the Service-based Database

You can not use a file served by a server without the server running.

2. The mdf file is stored local in my project.
OK.

3. If I log in to SQL Server 2008 express this created database can't be
found.within SQL server

You should be able to attach it.

Now I can also create a database in the following way.
1. Bring up the Server Explorer
2. Right click on the DataConnection in Server Explorer and select Create
New Sql Server Database.
3. The created database is shown in server explorer as homepc\sqlexpress
myCreatedDatabase.dbo
4. If I log in to SQL Server 2008 express this created database is found
here

Yes. Because that is a normal database that is made available when the
server startup.

I would prefer this approach.

Now to my first questions it seems to me that when I create the
Service-based Database the SQL Server is involved in some way even though
the actual database is not stored within the SQL Server because I get an
error when the SQL Server service was not running

No matter how the MDF files get accessed by SQLServer then SQLServer is
always needed.

My second question what is the difference between these two examples ?
I mean is the difference any more then just that in the first example the
mdf file is stored local in the project and
in the second example the mdf file is not stored local but somewhere else on
disk and the actual created database is shown in the SQL Server.

I don't think there are any differences except how/when SQLServer
accesses the files.

TSQL is TSQL.

Arne

 

[Tony Johansson]

You can not use a file served by a server without the server running.


You should be able to attach it.


Yes. Because that is a normal database that is made available when the
server startup.

I would prefer this approach.


No matter how the MDF files get accessed by SQLServer then SQLServer is
always needed.


I don't think there are any differences except how/when SQLServer
accesses the files.

TSQL is TSQL.

Arne

Good explained Arne

 

[Arne Vajhøj]

Good explained Arne

If you are really interested in attach and detach then start reading here:

http://msdn.microsoft.com/en-us/library/ms190794.aspx

And for the user instances read:

http://msdn.microsoft.com/en-us/library/ms254504.aspx

Arne