Moving a SQL Log File

Sometimes you need to move a log file for a SQL database, when I originally learned to do this the process was to detach the database, move your files and then attach and modify the locations.  I had a need to do this last weekend, and found that there is a much better way to do this (and a bit safer).

You do this by using the ALTER DATABASE command to MODIFY FILE and tell it where the log will go.  This command won’t take effect until the next time the database is brought online.

ALTER DATABASE dbname MODIFY FILE ( NAME = dbname_log, FILENAME = 'S:\newlocations\dbname_log.ldf');

So now the database knows where to look for the log file the next time it’s started.  So now you need to take that database offline and move the file.  You can shut down SQL, but you can use ALTER DATABASE again to just have downtime for the one database.

ALTER DATABASE dbname SET OFFLINE;

When this completes your database will be offline, but still registered on the SQL server, which is why this is safer than the detach/attach method.  If you want to confirm your settings you can run the following.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'dbname);

Now move the log file to the location you specified and bring the database back online.

ALTER DATABASE dbname SET ONLINE;

.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s