Often your SQL Server transaction log will outgrow its current location's capacity and you will be faced with the task of moving that log to a different volume or drive. To move a transaction log you create a SQL Server stored procedure that first detaches from the current volume, moves the log file, and then subsequently attaches to the new volume.
Here's an example:
For the library database use this command
EXEC sp_detach_db 'library'
Then copy the library.mdf and library_log.ldf files to your new target volume using
- copy <source path>\library.mdf <target path>\datacopy <source path>\library_log.ldf <target path>\data
Complete the procedure by attaching the files to SQL Server with
EXEC sp_attach_db 'library', '<target path>\library.mdf, <target path>library_log.ldf
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
This was first published in September 2002