Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    1

    Unhappy Unanswered: facing problem in changing the PATH of data/Log file

    Hi All,
    My name is Navaid Arif and i am a new user of SQL Server DB. The project on which i am working in on SQL Server 2005 as a Production database. Recently, i came to know that the data and log files used in this database and stored on the default location i.e. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA. and i need to change this path to some other location with better storage.

    I took a backup using following command:

    backup database myProdDB
    to disk='D:\Database Backup\Exported Data\myProdDB_backup20090307.bak'
    with init, stats;


    and tried to recreate the database by dropping and recreating it. And during recreating, changing the path of these data/log file and restore the data in this recreated database using:

    restore database fun
    from disk='D:\Database Backup\Exported Data\myProdDB_backup20090307.bak'
    with replace, stats;


    But facing error while dropping the database "DROP FAILED FOR DATABASE 'myProdDB'. (Microsoft.SqlServer.Smo)"


    Can anyone help me in changing the PATH of these Data/Log files with NO DATA LOSS.

    Thanks in advance.

    Regards,
    Navaid Arif.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    Hi , have you tried something like:
    RESTORE DATABASE MyDB
    FROM DISK = ‘E:\BackUpMyBaackUpFile.bak’
    WITH MOVE ‘YourMDFLogicalName’ TO ‘D:\DataYourMDFFile.mdf’,
    MOVE ‘YourLDFLogicalName’ TO ‘D:\DataYourLDFFile.mdf’

  4. #4
    Join Date
    Dec 2007
    Posts
    11
    make sure that no other sessions of the particular database is are running before trying to restore.
    But, why do you want to restore the Database for changing the data and log path? You can dettach the Database then move the files to the desired location and attach it agian.

    use the following TSQL

    1) sp_detach_db <DB_NAME>

    move the files

    2) sp_attach_db <DB_NAME>,'New location of your data file'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •