Change log file with extension .ldf

• ASFSDJG

  Mr or Mrs. 500

  Points: 500

  More actions

  September 29, 2012 at 3:43 am

  #267404

  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.

• Perry Whittle

  SSC Guru

  Points: 234040

  More actions

  September 29, 2012 at 5:03 am

  #1543682

  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"