Moving an LDF to a seperate physical location

Posted on 2007-10-16
Last Modified: 2012-06-21

I am a novice in SQL so please remember that

I wish to use the gui to detach and reattach a DB, this in itself is no problem and works fine. However I want to be able to move the MDB file to one location and the LDF to another. The detach/reattach process only allows me to put the MDF/LDF to the same location.

is there a way (in gui) to do this....

Question by:credmood
    LVL 142

    Accepted Solution

    with the attach/detach interface, you cannot do it.
    you need either to use the stored procedure sp_attach_db, where you can specify the locations explicitely, or use the backup + restore method, where during the restore you can specify new locations per file individually.


    Author Comment

    In your opinion which is the best way?

    I'm thinking the stored procedure way..if so what are the command variables to do this

    LVL 8

    Assisted Solution

    The best approach is to use the backup and restore method as its pretty easy way to change loaction of MDF/LDF files.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    I prefer the backup + restore method, as that has the implicit security of having a backup!

    anyhow: the syntax of sp_attach_db:
    LVL 5

    Assisted Solution

    On SQL 2005 the easiest way to move the files is the CREATE DATABASE.. FOR ATTACH command:

    USE [master]

            ( FILENAME = N'E:\MyDB\data.mdf' )
          , ( FILENAME = N'F:\MyDB\TRLog.ldf' )

    On sQL 2000 use:

    EXEC sp_attach_db @dbname = N'MyDB'
          , @filename1 = N'E:\MyDB\Data.mdf'
          , @filename2 = N'F:\MyDB\TRLog.ldf'

    You can change the database name, and the names of the files to suit your environment.
    LVL 68

    Assisted Solution

    >> In your opinion which is the best way? <<

    The size of the db is a factor here.  Backup & restore requires, well,  a backup and a restore :-) .  For a large db, this can be a significant amount of time.

    The detach / attach using pre-written commands can be done in seconds for any size db.  However:

    ** Before detaching the db, run a CHECKDB on it!  A damaged db cannot be re-attached!! **

    Note that the db can be read/written to during the check.

    Author Comment

    Thanks for this....

    A few of the DB's are large a few are small..I think its horses for courses...small ones backup/restore the larger ones detach/reattach ...
    Ill just have a play with a few test DB's
    Thanks for all your help..
    I hope your happy with my points sharing


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now