SQL Server Performance

MOVE LOG FILE to another drive

Discussion in 'SQL Server Log Shipping' started by pavankan, Jan 9, 2004.

  1. pavankan New Member

    I need to move the log file of my SQL Server 2000 database to another <br />drive. <br /><br />I detached the db <br /><br />Copied to log fle over to another drive <br /><br />But when I go back to attach the db using EM, I cannot find a place to ask it find the log file in a different place than in the same place where there is the MDF file! <br /><br />Any help would be appreciated <img src='/community/emoticons/emotion-1.gif' alt=':)' /> <br /><br /><br />Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.<br /><br />
  2. bradmcgehee New Member

    You can do this because I have done it before.

    When you reattach the database, you the the Attach Database screen. First, you must select the MDF file. Then, you can type in the name of the log file, and its path, directly in the area in the middle of the dialog box.

    Or, you can use the sp_attach_db SP to do the same thing.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. Luis Martin Moderator

    Brad:
    Triplicate post with same answer.
    I'll delete in General.

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  4. pavankan New Member

    I tried this:


    EXEC sp_attach_db @dbname = 'MASProd',
    @filename1 = 'G:MASProd_Data.MDF',
    @filename2 = 'H:MASProd_Log.LDF'

    I get this error:

    Server: Msg 9003, Level 20, State 1, Line 1
    The LSN (749:1561:1) passed to log scan in database 'MASProd' is invalid.

    Connection Broken



    It is the same error that I get when I try to do it form the EM




    quote:Originally posted by bradmcgehee

    You can do this because I have done it before.

    When you reattach the database, you the the Attach Database screen. First, you must select the MDF file. Then, you can type in the name of the log file, and its path, directly in the area in the middle of the dialog box.

    Or, you can use the sp_attach_db SP to do the same thing.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com

    Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

  5. Twan New Member

    Were the files copied at the same time and after the database had been shutdown cleanly? This is normally a problem when the database file and log file have got out of synch

    If you have no way of repeating the process by shutting down the source database, and then copying the files again, then you could try attaching only the mdf file, and if the database was shutdown cleanly then SQL will create the log file for you

    Cheers
    Twan
  6. pavankan New Member

    What I am actually trying to do is this:

    WE have a perfectly running db on server1

    I detached it and copied the files to a server2

    I physically copied the MDF file and the LDF file into separate drives on the server2
    (at the same time)

    I tired to attach the db on server2, But I get the (003 error

    However, if I put the LDF file back in the drive that MDF is in, the db attaches without errors!



    quote:Originally posted by Twan

    Were the files copied at the same time and after the database had been shutdown cleanly? This is normally a problem when the database file and log file have got out of synch

    If you have no way of repeating the process by shutting down the source database, and then copying the files again, then you could try attaching only the mdf file, and if the database was shutdown cleanly then SQL will create the log file for you

    Cheers
    Twan

    Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

  7. bradmcgehee New Member

    For the fun of it, try using the Copy Database Wizard to move the database and log between servers. This also uses the sp_attach_db method, and if it works, then you know that somewhere you introduced a problem, although it is hard for me to what that is without more information.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  8. pavankan New Member

    Thanks Y'all!

    I got it work.

    I just copied the files again and did not move them around like I did last time!
    that's all

    Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

  9. satya Moderator

    Glad the problem is solved and kindly refrain posting multiple (duplicate) posting in future.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page