How to create database from .mdf file only

  • Hi,

    I have installed SQL Server 2012 and downloaded sanple database from the below link, which has AdventureWorks2008R2_Data.mdf for download.

    http://msftdbprodsamples.codeplex.com/releases/view/55330

    Now, how to restore AdventureWorks database from AdventureWorks2008R2_Data.mdf file?

    Thanks

  • Can you use Create Database for attach with rebuild log?

  • I think you can attach the db by right clicking on the database folder.

  • I don't think you can rebuild the log when using the UI, however this is off the top of my head, and well age is not doing my memory well.

  • Chris Shaw (1/11/2012)


    I don't think you can rebuild the log when using the UI, however this is off the top of my head, and well age is not doing my memory well.

    You can, not specifying a log will rebuild it

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Try

    CREATE DATABASE Database1 ON

    (FILENAME = N'C:\Temp\Database1.mdf')

    FOR ATTACH_REBUILD_LOG

    Rob

  • You can attached database mdf file without ldf file, see example given below:-

    sp_attach_single_file_db @dbname='Amber',@physname='S:\AMBERDATAFILE\Amber.mdf'

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

  • Now, how to restore AdventureWorks database from AdventureWorks2008R2_Data.mdf file?

    What if you create a blank file and name it as AdventureWorks2008R2_log.ldf and then try to attach the mdf and ldf file.

    Not too sure, but can give a try.

    ----------
    Ashish

  • sharmaamard (1/12/2012)


    You can attached database mdf file without ldf file, see example given below:-

    sp_attach_single_file_db @dbname='Amber',@physname='S:\AMBERDATAFILE\Amber.mdf'

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

    From that link:

    This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead. For more information, see CREATE DATABASE (Transact-SQL). Do not use this procedure on a replicated database.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • crazy4sql (1/12/2012)


    What if you create a blank file and name it as AdventureWorks2008R2_log.ldf and then try to attach the mdf and ldf file.

    You'll get an error saying that the ldf file is not a valid database file and the attach will fail.

    CREATE DATABASE ... FOR ATTACH_REBUILD_LOG is the answer providing the DB was shut down cleanly before the ldf file removed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Attaching the .mdf file through the management console, without a .ldf file

    1. right click "Databases" and select attach.

    2. Click on "add" and select the .mdf file, click OK.

    3. In the details panel select the .dlf file, click remove button.

    4. Click the main OK button.

  • rtowler1 (1/18/2013)


    Attaching the .mdf file through the management console, without a .ldf file

    1. right click "Databases" and select attach.

    2. Click on "add" and select the .mdf file, click OK.

    3. In the details panel select the .dlf file, click remove button.

    4. Click the main OK button.

    This helped me in 2012 also. Thank you.

  • How to attach .MDF file with using .LDF file in Sqlserver 2008 R2 Express Edition?

  • This will only work if the SQL Server was stopped when you copied the MDF file from the old SQL Server. If you copied the file while the SQL Server was running this file probably will not attach either and you will need to get a valid backup of the database.

    Download help tool for sql database - SQL Server Restore Toolbox.

    http://www.sqlserver.restoretools.com/[/url]

    You can read up more on SQL Server here.

    http://www.filerepairforum.com/forum/microsoft/microsoft-aa/sql-server/498-creating-a-new-database-using-an-mdf-file?_=1416149856104

  • this thread is old!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply