locked
How to create a .mdf file from sql server 2008/2005 database? RRS feed

  • Question

  • How to create a .mdf  file from sql server 2008/2005 database, so I can attach it to one of my  database server, I hope that one expert can help me fix this problem?

    Thanks.
    Tuesday, October 13, 2009 6:33 PM

Answers

  • Hi Jake

    What is it you are actually trying to achieve here?  Are you trying to create a database on one Server and then "copy" it to another Server?  Or do you want to take an existing database and "copy" it to another Server?

    For either scenario you need to ensure that the database already exists (as pointed out by Leks).  Once the database exists you can backup the database, copy the backup file to the other Server and then restore the database using the backup file.

    Tony C
    • Marked as answer by Jake Wardley Wednesday, October 14, 2009 5:37 PM
    Wednesday, October 14, 2009 10:42 AM

All replies

  • Hello Jake,

    when you have a SQL Server runing then open SQL Server Management Studio (SSMS) and connect to that server.

    Open the node <ServerName> => Databases. 
    Right-Click on "Databases" => "New Database".
    There you can create a new database by defining the name, location (best in the suggested DATA folder of SQL Server) and so on.
    This creates at least one database file (mdf) and one transaction log file (log); as you define.
    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Tuesday, October 13, 2009 7:53 PM
  • You cannot create logical mdf and ldf files on your own. These files are craeted by SQLSERVR.exe when a new database is created .

    Try to create a new database and you will have mdf and ldf files on the disk . You wont be able to copy or attach the file in other instance since sql locks mdf and ldf , you will have to use detach and attach or backup and restore option to move db from one instance to the other.
    Thanks, Leks
    Tuesday, October 13, 2009 10:32 PM
    Answerer
  • Hi Jake

    What is it you are actually trying to achieve here?  Are you trying to create a database on one Server and then "copy" it to another Server?  Or do you want to take an existing database and "copy" it to another Server?

    For either scenario you need to ensure that the database already exists (as pointed out by Leks).  Once the database exists you can backup the database, copy the backup file to the other Server and then restore the database using the backup file.

    Tony C
    • Marked as answer by Jake Wardley Wednesday, October 14, 2009 5:37 PM
    Wednesday, October 14, 2009 10:42 AM