Home » Tip Bank » Security » SQL Server

Language: SQL
Expertise: Beginner

Jun 25, 2003

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:\mssql7\data directory:

copy c:\mssql7\data\pubs.mdf d:\mssql7\datacopy
c:\mssql7\data\pubs_log.ldf d:\mssql7\data

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:\mssql7\data\pubs.mdf',
'd:\mssql7\data\pubs_log.ldf'