September 29, 2012 at 3:43 am
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.
September 29, 2012 at 5:03 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy