Everyone knows, I hope, that it is usually a best practice to have your database log file on a different drive from the data file. But what if you have a situation, where originally, the database was created with both the log and the data file being on the same drive? How do you move the log file to a new location? Here is what you do:
Step 1: Make sure nobody is using the database at the moment. (This whole procedure is better done off business hours).
Step 2: Do a full database backup. (This is not an absolutely required step, but is ALWAYS a good idea before you do something that is somewhat dangerous).
Step 3: After you backup the database, I would shrink the log file so when you move it, it doesn't take forever. (This is also not a required step, and shouldn't be really done if your log file is not huge). If you do decide to perform this step, here is the syntax: DBCC Shrinkfile ('yourDB','1'); Note: the second parameter here is the size in kilobytes to which you want to shrink your log.
Step 4: Detach your database by using this syntax:
USE master
GO
EXEC sp_detach_db @dbname=N'yourDB';
GO
Step 4: However you want, copy the actual yourDB_log.ldf file to whatever location you want. Say for this example, we'll move it to the root of the D drive.
Step 5: Attach your database, and specify the new file locations.
USE master
GO
EXEC sp_attach_db @dbname = N'yourDB',
@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\yourDB_Data.mdf',
@filename2 = N'D:\yourDB_log.ldf';
GO