You are hereBlog
Register   |  Login
 Links
Minimize
 User Group Blog
Minimize
 User Group Blog
Minimize
Jan 23

Written by: Allen Berezovsky
1/23/2009 6:27 PM

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

Tags:

Re: Moving SQL Server Log file to a new location

+1

By IngaFoRT61 on   3/26/2012 10:34 AM