locked
DBO or MDF whats the diffrence? RRS feed

  • Question

  • User-1068041051 posted

    ]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 .DBO

    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.

    Sunday, May 11, 2008 4:26 PM

Answers

  • User1702108463 posted

    noisychatterboxer,

    >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.

     Pete

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 13, 2008 11:18 AM

All replies

  • User-1581724896 posted

    there is not file of type dbo in Sql2000 or 2005... this might be a different db server like db for Objects

    Monday, May 12, 2008 7:38 AM
  • User1580272028 posted

    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 

     

    my doubts::

    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??? 

    Monday, May 12, 2008 11:23 AM
  • User1702108463 posted

    Hi,

    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: http://msdn.microsoft.com/en-us/library/ms189563.aspx

    [Excerpt]

    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.

    File Description

    Primary

    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

    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.

    Transaction Log

    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.

    [/Excerpt]

    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.

    Pete

    Monday, May 12, 2008 1:06 PM
  • User1580272028 posted

    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....
     

    Tuesday, May 13, 2008 7:03 AM
  • User-1581724896 posted

    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.

    Tuesday, May 13, 2008 8:18 AM
  • User1580272028 posted

     

    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?? 

    Tuesday, May 13, 2008 9:28 AM
  • User1702108463 posted

    noisychatterboxer,

    >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): http://msdn.microsoft.com/en-us/library/ms165716.aspx

    [Excerpt]

    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.

    How does Xcopy Deployment Work?

    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.

     [/Excerpt]

     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.

    Pete

    Tuesday, May 13, 2008 9:47 AM
  • User1702108463 posted

    noisychatterboxer,

    >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

    Pete

    Tuesday, May 13, 2008 9:56 AM
  • User1580272028 posted

     

    to peter

     

    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  

    Tuesday, May 13, 2008 10:50 AM
  • User1580272028 posted

     

      

     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 owner?

    Tuesday, May 13, 2008 10:56 AM
  • User1702108463 posted

    noisychatterboxer,

    >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.

     Pete

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 13, 2008 11:18 AM
  • User-1581724896 posted

    Hi ricksvoid,

    If u have recieved the answer to your problem, please mark the thread as resolved.

    Wednesday, May 14, 2008 2:43 AM
  • User-1068041051 posted

    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.

    Wednesday, May 14, 2008 3:00 PM
  • User-959812872 posted

    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

    Jim

    Thursday, June 12, 2008 9:59 PM
  • User2029810015 posted

    i want to know where this .dbo file is saved in the system


    Monday, January 24, 2011 2:38 PM