Moving a transaction log 1

[Spirit]

Hi,

How do I move the transaction logs etc. from one drive to another?

Thanks

Iain

 

[sguslan]

Detach the database, then move the transaction log to the new location and re-attach the database.

Examples:

EXEC sp_detach_db 'pubs', 'true'

EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

You can serach books online for more help sp_detach_db and sp_attach_db..

 

[Spirit]

Thanks,

A wee star for you!

Iain

 

[pto]

Yeah I have seen that script and the example shows moving both the database and the transaction log. The question is how do you move the transaction logs to a different drive not the database. Or does it mean that you would still have to detach the database and the move the transaction log then run the reattach script with the database pointing to the original drive and the transaction log pointing to the new drive?

Iain did you figure it out how to move just the transaction logs or did you have to move both?

 

[SonOfEmidec1100]

You just specify the correct location for @filename2. You don't need to copy the ldf if it does not exist SQLserver creates a new one - a good way to shrink the log back to the default size. Remember to delete the old one in this case.

 

[pto]

Thanks SonOfEmidec1100 I was not sure you could do that. I will try.