Quickest way to change the location of a log file in SQL Server

The quickest way to move the log file to a different location is to Detach the database, Move the .ldf and then Attach the database

Step 1: Detach the Database:

Use this command to detach the database

sp_detach_db 'YourDBName'

Step 2: Move the log file (.ldf):

Move the log file to a different location. For eg: Move it from 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\YourDBName_Log.ldf' to 'D:\Logback\YourDBName_Log.ldf'

Step 3: Attach the Database:

Once the file has been moved, attach the database again

sp_attach_db 'mydb','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\YourDBName.mdf','D:\Logback\YourDBName_Log.ldf'

Note: To find out the current location of your database files, use the command 'sp_helpfile'

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook