Change log file with extension .ldf

  • Hi,

    One of my Junior DBA, Created a DB with Log file .mdf extention. can we change it with .ldf extension.

    T SQL Used:

    RESTORE DATABASE NewDB

    FROM DISK = 'c:\Backup\OldDB.bak'

    WITH REPLACE, MOVE 'NewDB_Data' TO 'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDB.mdf',

    MOVE 'NewDB_log' TO 'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDB_log.mdf'

    How to set the log file. Please help me.

  • ASFSDJG (9/29/2012)


    Hi,

    One of my Junior DBA, Created a DB with Log file .mdf extention. can we change it with .ldf extension.

    T SQL Used:

    RESTORE DATABASE NewDB

    FROM DISK = 'c:\Backup\OldDB.bak'

    WITH REPLACE, MOVE 'NewDB_Data' TO 'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDB.mdf',

    MOVE 'NewDB_log' TO 'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDB_log.mdf'

    How to set the log file. Please help me.

    Yes, it doesn't actually matter what file extension the file has but for clarity it helps.

    You need to set the correct path\filname in the system catalogs. Based on the query supplied you would use

    ALTER DATABASE NewDB MODIFY FILE (name=NewDB_log,

    FILENAME='D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDB_log.ldf'

    Take the database offline and then rename the OS files extension. Once this is done bring the database back online.

    However, before you start just verify the path and filename in use by using

    SELECT name, physical_name FROM sys.master_files

    WHERE database_id = DB_ID('NewDB')

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

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

Viewing 2 posts - 1 through 2 (of 2 total)

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