Moving Transaction Logs to a Different Drive

Moving Transaction Logs to a Different Drive

You can move a transaction log using SQL Server stored procedures. Simply detach the database, move the log file, then reattach the database. For example, to move the pubs database from drive C to drive D, use the following sp_detach_db command to detach the database:

EXEC sp_detach_db 'pubs'

Next, copy the pubs.mdf and pubs_log.ldf files to the destination drive. The following example shows how to copy these two files to the d:mssql7data directory:

copy c:mssql7datapubs.mdf d:mssql7datacopyc:mssql7datapubs_log.ldf d:mssql7data

Finally, after you copy the files, you can use sp_detach_db to reattach the data to SQL Server:

EXEC sp_attach_db 'pubs', 'd:mssql7datapubs.mdf','d:mssql7datapubs_log.ldf'


Share the Post: