Post reply

Moving Transaction Log Files and Attaching in SQL 2000

  • June 20, 2008 at 2:42 pm

    #93492

    It is my understanding that the database files and the log files should be on separate drives.

    I understand that I need to DETACH the database, copy the log file to the new location, and then ATTACH the database.

    I need specifics on how to copy the log file to the new location and then tell the database when you attach it, that the log files are now on another drive.

    Thank you.

  • June 20, 2008 at 2:55 pm

    #832086

    Keeping mdb and ldf files on separate physical disks is considered a best practice.

    See:

    http://msdn.microsoft.com/en-us/library/ms187858.aspx

    for information on how to attach and detatch database and log files.

    While the specific syntax you need is not listed in this article, you should be able to use the example code to figure this out for yourself.

    dwagnon (6/20/2008)

    It is my understanding that the database files and the log files should be on separate drives.

    I understand that I need to DETACH the database, copy the log file to the new location, and then ATTACH the database.

    I need specifics on how to copy the log file to the new location and then tell the database when you attach it, that the log files are now on another drive.

    Thank you.

    Brad M. McGehee
    DBA

  • June 20, 2008 at 3:03 pm

    #832089

    So you are saying that after we detach the database, we use a create statement showing where the *.mdf and *.ldf files go?

  • June 20, 2008 at 3:06 pm

    #832091

    Use sp_attach_db with the same .mdf/.ldf files. Simply put one of them on a separate physical disk(s) and your path statements in sp_attach_db should reflect those paths.

    -- You can't be late until you show up.

  • June 21, 2008 at 10:54 pm

    #832340

    Hi,

    Consider if you are planning to move log files from C: drive to D: drive. Lets assume the db name is TEST, you need to detach the db, move the log file to a new location and attach.

    *) Detach the database. Use SP_Detach_db procedure

    *) Move the log file alone from C: drive to D: drive

    *) Attach the db using SP_attach_db procedure as shown below

    sp_attach_db 'TEST','C:\sql\test.mdf','D:\sql\test_log.ldf'

    Regards..Vidhya Sagar
    SQL-Articles

  • June 23, 2008 at 6:37 am

    #832606

    That seems to work although the database cannot be seen in Enterprise Manager. Is there another step to have the database show?

  • June 23, 2008 at 10:51 am

    #832906

    Thank you all. We have it working now.

  • June 25, 2008 at 11:06 am

    #834152

    Thank you all for your help. What we were missing was after copying the log file to the new disk drive, we were not deleting it from the old drive, when we tried to "attach" there were log scan errors. Once we deleted the "old" log file everything worked great!!

    Thanks again from a very new SQL person!!!

  • June 25, 2008 at 12:22 pm

    #834198

    I have actually made this same mistake myself, more than once. It is not always easy to remember to delete the old file.

    dwagnon (6/25/2008)

    Thank you all for your help. What we were missing was after copying the log file to the new disk drive, we were not deleting it from the old drive, when we tried to "attach" there were log scan errors. Once we deleted the "old" log file everything worked great!!

    Thanks again from a very new SQL person!!!

    Brad M. McGehee
    DBA

Viewing 9 posts - 1 through 8 (of 8 total)

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