Post reply

ndf vs ldf

  • February 3, 2011 at 10:10 am

    #149873

    Hi,

    I noticed that one of sql server 2005 database has mdf and ndf files (no ldf)

    When restored the database someone made mistake, instead of typing .ldf, .ndf is added in log file.

    Now, how to replace the .ndf file with .ldf

    Please advice.

  • February 3, 2011 at 10:29 am

    #1282023

    laddu4700 (2/3/2011)

    Hi,

    I noticed that one of sql server 2005 database has mdf and ndf files (no ldf)

    When restored the database someone made mistake, instead of typing .ldf, .ndf is added in log file.

    Now, how to replace the .ndf file with .ldf

    Please advice.

    You can't ndf mean secondary partition

    But, you can restore database without ldf

    http://technet.microsoft.com/en-gb/library/ms189563.aspx

  • February 3, 2011 at 10:43 am

    #1282035

    Thank you for the reply. I will restore the database with mdf and ldf

    My question is why SQL Server 2005 is accepted .ndf extension in log file path? any idea?

  • February 3, 2011 at 10:52 am

    #1282043

    Not sure if I completely understand your situation, but here is an attempt to clarify for you.

    SQL Server does NOT enforce any specific extensions for data / log file naming. So you could, theoretically, use a . xyz extension if you so desired. That said, the accepted norm for extensions in SQL Server is .mdf for Primary data file, .ndf for secondary data files, and .ldf for the transaction log file.

    Hope that this helps you.

  • February 3, 2011 at 1:31 pm

    #1282127

    I think you could detach the database, rename the extension, then attach.

    Always test first and have good backups.

  • February 3, 2011 at 1:51 pm

    #1282140

    You can rename the file using ALTER DATABASE ... MODIFY FILE (...). Lookup the exact syntax in Books Online.

    Once you have done that, then you take the database offline, change the file name and bring the database back online.

    Using detach/attach the owner of the database will change and you will have to change it back.

    Example:

    ALTER DATABASE MyDatabase MODIFY FILE (Name = LogicalName, Filename = 'new filename');

    GO

    ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE;

    GO

    -- rename file

    ALTER DATABASE MyDatabase SET ONLINE;

    GO

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 6 posts - 1 through 5 (of 5 total)

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