SQL Server Performance

how to change the path of data and log files

Discussion in 'General DBA Questions' started by manojbandha, Apr 19, 2006.

  1. manojbandha New Member

    On dev server for one of the database I woulike to change the path for databa d the log file.
    for example: current path:- c:program filesMSSQLload.mdf
    new path the one i want is:- c:program filesMSSQLDataload.mdf

    Is is possible to change the path for data and the log files. I tried but not successful.

    can anyone let me know as soon as possible please

    thanks
    manojbandha, Apr 19, 2006
    #2

  2. cmdr_skywalker New Member

    if you can take the database offline and you have admin priv, use the sp_attach_db, sp_detach_db and xp_cmdshell (move or copy the files). it is the fastest way (assuming the database is large enough, else for single table with few records, use the select into and alter table).

    You can also use sp_renamedb if you want to rename the current db and attach the new copy of load.mdf. Don't forget to set the database in single mode (sp_dboption) though.

    Other methods is using the backup/restore, DTS, T-SQL, bulk utilities which all takes time. By the way, the SQL 2005 Enterpirse manager support database attachment. Check it out.
    cmdr_skywalker, Apr 19, 2006
    #2

  3. manojbandha New Member

    Thanks a lot.
    i think you have misunderstood. I donot want to move the files anywhere but want to change the path of the data and log files .
    can you please explain the procedure .
    manojbandha, Apr 19, 2006
    #2

  4. Luis Martin Moderator

    If you try any of cmdr solutions and fail the reason is you must set to single mode.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



    Luis Martin, Apr 19, 2006
    #2

  5. MichaelB Member

    If you want to change the path you would have to move the files to where the new path is... Manojbandha is right. You cant just point to a new location that doesnt have the files unless you are creating a new db then you just tell it where to put them when creating.

    Mike

    Michael Berry
    DBA
    MichaelB, Apr 19, 2006
    #2

  6. satya Moderator

    If you're doing the change of path on the same server then you must have allow downtime to the database(s) and refer what Cmdr referred. If not on a new server you can run RESTORE DATABASE... WITH MOVE clause to change the path of data and log files.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Apr 20, 2006
    #2

  7. Mast_dba New Member

    According to Satya...

    Take backup of ur Db & then restore ur Db with same backup...After selecting backupfile goto the option tab on the top where u find the location of data file & log file change the location with new path & restore the db. Ur both file will move to new place.
    Thanks

    MAST
    ITS IMPOSSIBLE TO DEFEAT A PERSON WHO NEVER GIVE UP.
    Mast_dba, Apr 21, 2006
    #2

  8. satya Moderator

    To add Mast's reference you need to perform the action using Enterprise Manager or using the RESTORE... WITH MOVE from Query Analyzer. BOL has the code examples too in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Apr 22, 2006
    #2

Share This Page