Last post Jan 24, 2011 02:38 PM by pramuk97
May 11, 2008 04:26 PM|ricksvoid|LINK
]First of all here is how i created my SQL database and established a connection in visual studio 05. I go into the server explorer and right click on the data connections node and and select "create new SQL database". A window then prompts me for a server
name and data base name. Under server name I write "localhost\SQLEXPRESS" and under data base name I put in whatever. This totally worked and the database was asigned the extension of
I can now insert, select and delete information with some simple ADO coding. The only problem is that using this method makes it seamingly impossible to FTP my database onto my host server. I am aware that I can create a data base in the APP folder by simply
right clicking it and selecting " new database".
This would simplify the deploying process but I cant seem to establish a connection this way. Ive also noticed that when I use this method that it saves it as a .MDF instead of a .DBO. What is the difference? Why does my .DBO work but not my .MDF in my app
folder? Im so confused over this issue that I am problably not even asking the right question.
Bottom line is that I have a small web application with a .dbo database that works wonderfully in my isolated visual studio environment and I need a solution to deploy it on the world wide web. Please help with any information I would be very thankfull.
May 12, 2008 07:38 AM|sudipta|LINK
there is not file of type dbo in Sql2000 or 2005... this might be a different db server like
db for Objects
May 12, 2008 11:23 AM|noisychatterboxer|LINK
i have been posting the similar questions for days but no one seems to know the difference lies in them//////dissapointed....
can someone come out with an straight-to-the point and easy to understand explanation......
hope get to see the right answers
the file created under create new SQL database with an extension of DBO is the same as the database created using the MANAGEMENT studo ,having an extension of MDF???
May 12, 2008 01:06 PM|Peter Lee|LINK
dbo means you are logged in as the database owner. See:
http://www.sqldba.org/articles/17-SQL-DBO-SQL-Server-Database-Object-Owner-dbo.aspx I assume that is why the server explorer is appending it to the database name.
A .mdf file stores data for a database. See:
At a minimum, every SQL Server 2005 database has two operating system files: a data file and a log file. Data files contain data and objects such as tables, indexes, stored procedures, and views. Log files contain the information that is required to recover
all transactions in the database. Data files can be grouped together in filegroups for allocation and administration purposes.
SQL Server 2005 databases have three types of files, as shown in the following table.
The primary data file contains the startup information for the database and points to the other files in the database. User data and objects can be stored in this file or in secondary data files. Every database has one primary data file. The recommended
file name extension for primary data files is .mdf.
Secondary data files are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for a single Windows
file, you can use secondary data files so the database can continue to grow.
The recommended file name extension for secondary data files is .ndf.
The transaction log files hold the log information that is used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf.
In your scenario, this is my understanding of the differences:
1) MyLocalHost\sqlexpress.Testing.dbo -- here the connection string looks like:
Data Source=MyLocalHost\SQLEXPRESS;Initial Catalog=Testing;Integrated Security=True;Pooling=False
2) Database.mdf -- here the connection string looks like:
Data Source=.\SQLEXPRESS;AttachDbFilename="C:\...\My Documents\Visual Studio 2008\WebSites\Testing\App_Data\Database.mdf";Integrated Security=True;User Instance=True
In instance 1, you are connecting just as you would over the network to another sql server. In instance 2, you are connecting directly to a database
file locally, isolated from any "real" databases.
You can copy mdf files from machine to machine and later import them into a Sql Server installation using Management Studio.
In summary, the difference: in scenario 1, you are connecting as you would over the network, in this case, to localhost. in scenario 2, you are connecting directly to an isolated database file, an .mdf file.
I hope the explanation was clear. Maybe someone else will correct me where my understanding is wrong. But I thought you might at least appreciate the answer above since no one else seems to be replying.
May 13, 2008 07:03 AM|noisychatterboxer|LINK
to peter lee
so u are saying that the mdf files are actually the databases that could be copied and transfered to other server to get attached and they are those types of databases working under an isolated environment. unlikely,
the dbo databases are working over the network?? does this mean that we cannot use mdf files for network purpose ? correct me if i misinterpretated..
anyway its good to see a reply here....
May 13, 2008 08:18 AM|sudipta|LINK
DBO is the database owner while the .MDF is the manifest data file which is the actual database file. .LDF is the log file of the database. To cpoy the databsse from onelocation to another, you have to copy the .mdf and .ldf files and then attach these files
on the different server on which the database is required to be copied.
May 13, 2008 09:28 AM|noisychatterboxer|LINK
so the dbo database is just a normal mdf database with database owner feature??
where can i locate the mdf files relevant to those dbo database??
May 13, 2008 09:47 AM|Peter Lee|LINK
>so u are saying that the mdf files are actually the databases that could be copied and transfered to other server to get attached and they are those types of databases working under an isolated environment. unlikely,
Yes, I've done this before: I have worked with a local .mdf and .ldf file and later moved them to a production server. You can either import the data through Sql Management Studio, like I said above, or follow the instructions on deployment at:
http://msdn.microsoft.com/en-us/library/ms187858.aspx there's plenty of good documentation on this if you do a google search at site:msdn.microsoft.com
As far as the portability of .mdf and .ldf files, see where it says (I underlined key phrases):
With Xcopy, when you distribute your application to users, you can send
just the .exe and .mdf files. Each user can place these files in a folder and double-click the .exe to start using the application.
SQL Server Express can automatically attach an .mdf file to a running instance of SQL Server Express when an application first establishes a connection. When the user closes or exits the application, SQL Server Express detaches the .mdf file from the instance.
The feature results in the .mdf file being fully portable: you can copy and move the file, and simultaneously run multiple copies of the file on the same instance.
Another point of interest to see they are isolated: Open up your installation of SqlServer express. At the same time, edit the .mdf file data via Visual Studio. I expect you will see the modifications are independent; unless, of course, the .mdf and
.ldf files that are being used are attached to your sql server express installation also.
> the dbo databases are working over the network??
The word dbo means database owner. A database owner can do whatever he wants with the databases he owns. My previous post was saying that the mode of connection to MyLocalHost\sqlExpress.Testing.dbo talking to the sql express installation locally is the
same as if it were talking over the network to a remote sql server installation. But you don't need a network, if you're connecting locally, of course.
> does this mean that we cannot use mdf files for network purpose ? correct me if i misinterpretated..
You can use it on a network but you'd have to import the data or attach it to a sql server installation. .mdf and .ldf files are just the backend of sql server, they store data and what you do to the data. Or if you attach it to your local sql server express
installation, you can serve it up from your local machine.
> anyway its good to see a reply here....
Sure. Maybe what I've posted will be of some help.
May 13, 2008 09:56 AM|Peter Lee|LINK
>so the dbo database is just a normal mdf database with database owner feature??
I'd say pretty much so.
>where can i locate the mdf files relevant to those dbo database??
Sql server express might put the .ldf and .mdf files in a path similar to: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
May 13, 2008 10:50 AM|noisychatterboxer|LINK
you had cleared most of the doubts i have,, thx a lot...
just one more point to mention: i think the database files created under visual studio application are stored somewhere else , i mean they could be stored somewhere different from the directory given by you
May 13, 2008 10:56 AM|noisychatterboxer|LINK
and where exactly can i find the backend files for DBO databases, and these databases need to be attached the same way we do to a mdf database created using management studio.?? or those dbo databases are exclusively usable and accessible by database
May 13, 2008 11:18 AM|Peter Lee|LINK
>just one more point to mention: i think the database files created under visual studio application are stored somewhere else , i mean they could be stored somewhere different from the directory given by you
They are typically placed by visual studio in the app_data subfolder of your web site project if they are created in visual studio.
> and where exactly can i find the backend files for DBO databases,
The prior post with the path to a typical MSSQL installation is where Sql Server stores them. Here's that path again, which is likely to differ from machine to machine, but it should be similar:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
>and these databases need to be attached the same way we do to a mdf database created using management studio.??
Sql Server creates the mdf and ldf files for you and places them in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data, and they are already attached. On the other hand, visual studio will create .mdf and .ldf files independently of your sql server
installation and so won't yet be attached.
>r those dbo databases are exclusively usable and accessible by database owner?
It probably depends on the permissions granted to the other users.
May 14, 2008 02:43 AM|sudipta|LINK
If u have recieved the answer to your problem, please mark the thread as resolved.
May 14, 2008 03:00 PM|ricksvoid|LINK
Thanks for the info Pete but what I really need to know is how to deploy my web app and .DBO database to my hosting company.
Jun 12, 2008 09:59 PM|bigjim8987|LINK
If anyone thinks they have to use dbo with visual studio you don't. If you try creating the db with the db tab active it will create the dbo. What you need to do is right click the app data folder and then you can create the mdf database quick and easy.
I always forget about this and when ever i forget it drives me nuts for 20 min trying to create it the way I want it.
you can create the mdf in visual studio
Jan 24, 2011 02:38 PM|pramuk97|LINK
i want to know where this .dbo file is saved in the system