SQL 2008 – Relocating log files

I needed to move our Data Warehouse and ACS database log files to a new drive, the following is a relatively simple method to move a SQL log file to a new location without needing to detach your database.

ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE DBNAME SET OFFLINE;
ALTER DATABASE DBNAME MODIFY FILE ( Name = Log File Name, Filename = ‘Drive Letter:\Log file location.ldf‘);
At this point copy the log file to the new location
ALTER DATABASE DBNAME SET ONLINE;
ALTER DATABASE DBNAME SET MULTI_USER;

The Log file name and file location can be found in the properties of your database under the File Tab.
This method could also be used to move your database file.

Loading

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.