Post reply

Moving Databasse Transaction Logs

  • June 2, 2005 at 10:29 am

    #86893

    Hey All,

    I am in need of a little help...I'm trying to move the transactions logs for some databases on my SQL 2000 Server. I dont need to move the Database, just the Logs.  I have done some searching on microsoft and found a few articles but they just seem mundane.  If only it were as easy as moving them in Exchange

     

    Thanks for the help!

  • June 2, 2005 at 12:53 pm

    #562693

    Here's what I would do:

    First, make backups.

    Read the sp_attach_db topic in Books Online.

    I would detach the database using sp_detach_db, copy (not move) the log file to its new location, then attach the database using sp_attach_db. You can do this in the Enterprise Manager GUI, or through a command-line like Query Analyzer. The commands are below.

    So, detach the database. (This will make it entirely inaccessible, so don't do it during production hours!)

    exec sp_detach_db @dbname='MyDB'

    Then copy your MyDB_log.ldf file to its new home

    Then attach. You'll specify the same filename for the data file, and the new name for the log file. Something like:

    exec sp_attach_db 
     @dbname='MyDB', 
     @filename1='D:\MSSQL\Data\MyDB.mdf', 
     @filename2='E:\MSSQL\Data\MyDB_log.ldf'

    After you've attached the database with the log file in the new location and have no users screaming at you, you can delete the log file in the original location.

    -- J.Kozloski, MCDBA, MCITP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply