Post reply

How to create database from .mdf file only

  • January 10, 2012 at 12:55 pm

    #249318

    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

  • January 10, 2012 at 1:21 pm

    #1431381

    Can you use Create Database for attach with rebuild log?

  • January 11, 2012 at 1:49 pm

    #1432107

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

  • January 11, 2012 at 5:05 pm

    #1432220

    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.

  • January 12, 2012 at 1:18 am

    #1432281

    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" 😉

  • January 12, 2012 at 3:49 am

    #1432327

    Try

    CREATE DATABASE Database1 ON

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

    FOR ATTACH_REBUILD_LOG

    Rob

  • January 12, 2012 at 3:56 am

    #1432333

    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

  • January 12, 2012 at 3:58 am

    #1432337

    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

  • January 12, 2012 at 4:00 am

    #1432340

    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

  • January 12, 2012 at 4:01 am

    #1432341

    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

  • January 18, 2013 at 11:13 am

    #1578820

    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.

  • March 8, 2013 at 9:12 am

    #1595017

    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.

  • July 17, 2013 at 5:07 am

    #1633176

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

  • November 19, 2014 at 11:32 am

    #1759592

    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

  • November 19, 2014 at 2:08 pm

    #1759643

    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