SQL Server Performance

Relocating .mdf & .ldf files

Discussion in 'General Developer Questions' started by bharathsivam, May 16, 2008.

  1. bharathsivam New Member

    Hi,
    Im using SqlServer 2000 and I want to know if it's possible to change the location of (.mdf & .ldf) files?
    For eg: If it's located in
    D:MySqlDataTest.mdf
    D:MySqlLogTest.ldf
    I want it to be moved to
    E:MySqlDataTest.mdf
    E:MySqlLogTest.ldf
    If possible, how(syntax/reference links) & can it be done without detaching the database?
  2. FrankKalis Moderator

    See if this helps:
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;314546
    http://support.microsoft.com/kb/221465/
    http://support.microsoft.com/kb/224071/
  3. techbabu303 New Member

    If you want the existing database to remain open to users and without detaching the database, I suggest RESTORE COPY script , then remove the old database and rename the new one with old name.
    example below
    Step 1 Backup existing database
    Backup database dev
    to disk ='E:sql2000
    estoresdev.bak'
    Step 2 Check for logical name paths by running the command below on backedup database
    RESTORE FILELISTONLY
    FROM DISK = 'E:sql2000
    estoresdev.bak'
    Step 3 Use old logical path name as listed in result of above command , the RESTORE will create a new database called dev2 from backup , to new location in F drive as mentioned below.
    RESTORE DATABASE dev2
    FROM DISK ='E:sql2000
    estoresdev.bak'
    WITH MOVE 'dev_data' TO 'F:userdatadev2.mdf',
    MOVE 'fin62_dev_log' TO 'F:userdatadev2.ldf'
    Note : make sure you have enough disk space for above operations and also take the backup at offpeak hours if your database is very large.
    Cheers
    Sat
  4. bharathsivam New Member

    Both, thanks for your response. I understood the options and syntax [H].

Share This Page