none
chnage location of file

    Question

  • Hi,
    One of the databases in Dev is stored in C drive.
    How can I place it in D drive instead please?
    Thanks
    Tuesday, May 29, 2012 9:36 AM

Answers

  • ALTER DATABASE dbname SET OFFLINE
    GO
    ALTER DATABASE dbname 
    MODIFY FILE( NAME = dbname _Data , FILENAME = 'c:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dbname _Demo.mdf' )
    ALTER DATABASE database_name 
    MODIFY FILE( NAME = dbname _Log , FILENAME = 'd:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dbname _Demo_1.ldf' )
    GO

    ---Copy the file to the destination 

    ALTER DATABASE dbname   SET ONLINE


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/



    As there is not enough space in C drive, I would like to place both .mdf and .ldf into D drive.
    How can your sql be to do just that?
    Thanks
    • Marked as answer by arkiboys Tuesday, May 29, 2012 10:25 AM
    Tuesday, May 29, 2012 9:53 AM

All replies

  • ALTER DATABASE dbname SET OFFLINE
    GO
    ALTER DATABASE dbname 
    MODIFY FILE( NAME = dbname _Data , FILENAME = 'c:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dbname _Demo.mdf' )
    ALTER DATABASE database_name 
    MODIFY FILE( NAME = dbname _Log , FILENAME = 'd:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dbname _Demo_1.ldf' )
    GO

    ---Copy the file to the destination 

    ALTER DATABASE dbname   SET ONLINE


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/



    Tuesday, May 29, 2012 9:41 AM
    Answerer
  • ALTER DATABASE dbname SET OFFLINE
    GO
    ALTER DATABASE dbname 
    MODIFY FILE( NAME = dbname _Data , FILENAME = 'c:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dbname _Demo.mdf' )
    ALTER DATABASE database_name 
    MODIFY FILE( NAME = dbname _Log , FILENAME = 'd:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dbname _Demo_1.ldf' )
    GO

    ---Copy the file to the destination 

    ALTER DATABASE dbname   SET ONLINE


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/



    As there is not enough space in C drive, I would like to place both .mdf and .ldf into D drive.
    How can your sql be to do just that?
    Thanks
    • Marked as answer by arkiboys Tuesday, May 29, 2012 10:25 AM
    Tuesday, May 29, 2012 9:53 AM
  • -- Set the Database Offline
    USE [master];
    GO
    ALTER DATABASE [TESTDB] SET OFFLINE;
    GO
    -- Modify the file location
    USE [master];
    GO
    ALTER DATABASE [TESTDB]
    MODIFY FILE (Name=TestDB, FILENAME='D:\UserDB\TestDB.MDF');
    GO
    ALTER DATABASE [TestDB]
    MODIFY FILE (Name=Testdb_log, FILENAME='D:\UserDB\TestDB_Log.LDF');
    GO
    -- Manually move the files (data and log file) to D:\UserDB and Bring the database online
    USE [master];
    GO
    ALTER DATABASE [TestDB] SET ONLINE;
    GO
    Hope, this may help :)

    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

    Tuesday, May 29, 2012 10:17 AM
  • I think that Uri has already given you the idea how to do the file move.

    the Set database offline and then modify the file to the new destination is a recommended way to do the job.

    if you don't use the SQL server broker, you can simply detach your database, and then move your data file and log file to D drive and reattach the database.

    If you use Uri's method, and you also want to move your data file to D drive. then suppose that you want to move to D:\SQLData folder then

    ALTER DATABASE dbname SET OFFLINE;
    GO
    ALTER DATABASE dbname
    MODIFY FILE( NAME = dbname _Data , FILENAME = 'D:\SQLData\dbname _Demo.mdf' );
    GO

    --copy the data file from c to d:\sqldata\ 

    ALTER DATABASE dbname   SET ONLINE

    --after the database if online and everything is ok

    --delelte the data file in c drive to save space



    Tuesday, May 29, 2012 10:20 AM
  • Thank you all.
    Tuesday, May 29, 2012 10:25 AM
  • USE master
    GO
    ALTER DATABASE db_name SET OFFLINE;
    GO
    
    USE master
    GO
    ALTER DATABASE db_name
    MODIFY FILE (Name=filename, FILENAME='D:\xxx\xxx.MDF');
    GO
    ALTER DATABASE db_name
    MODIFY FILE (Name=filename_log, FILENAME='D:\xxx\xxx_Log.LDF');
    GO
    
    USE master
    GO
    ALTER DATABASE db_name SET ONLINE;
    GO


    • Edited by Vimarsh Tuesday, May 29, 2012 10:54 AM
    • Proposed as answer by Vimarsh Wednesday, May 30, 2012 9:14 AM
    Tuesday, May 29, 2012 10:53 AM
  • USE master
    GO
    ALTER DATABASE db_name SET OFFLINE;
    GO
    
    USE master
    GO
    ALTER DATABASE db_name
    MODIFY FILE (Name=filename, FILENAME='D:\xxx\xxx.MDF');
    GO
    ALTER DATABASE db_name
    MODIFY FILE (Name=filename_log, FILENAME='D:\xxx\xxx_Log.LDF');
    GO
    
    USE master
    GO
    ALTER DATABASE db_name SET ONLINE;
    GO

    • Proposed as answer by Vimarsh Tuesday, May 29, 2012 11:13 AM
    Tuesday, May 29, 2012 10:57 AM
  • No, it is recommended to separate data and log files on different physical drives (for performance gain)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 29, 2012 11:28 AM
    Answerer
  • No, it is recommended to separate data and log files on different physical drives (for performance gain)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    if it is ok, can you explain the advantages and disadvantages of having the two files in one drive?
    Thanks
    Tuesday, May 29, 2012 11:35 AM
  • Disadvantage is

    SQL Server reads  data to the log file almost sequentially  at the same time as it is written the data file .

    1.Writes to the log file that it’s going to change the data

    2.Writes to the data file

    3 .Writes to the log again to remark that transaction is finished

    As we know that writes are almost random  it  will be slow to jump from the log to the data files and back again.

    "Advantages" is

    Database is read only or DML operations such as (INSERT/DELETE/UPDATE) are rarely perfomed


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


    Tuesday, May 29, 2012 11:42 AM
    Answerer
  • Disadvantage is

    SQL Server reads  data to the log file almost sequentially  at the same time as it is written the data file .

    1.Writes to the log file that it’s going to change the data

    2.Writes to the data file

    3 .Writes to the log again to remark that transaction is finished

    As we know that writes are almost random  it  will be slow to jump from the log to the data files and back again.

    "Advantages" is

    Database is read only or DML operations such as (INSERT/DELETE/UPDATE) are rarely perfomed


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


    Regarding your point on Advantage:
    We do alot of DML i.e. insert, delete, update.
    What do you mean it is readonly please?
    Thanks
    Tuesday, May 29, 2012 12:45 PM
  • Only reads (SELECT) operation on the database

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 29, 2012 12:49 PM
    Answerer
  • Only reads (SELECT) operation on the database

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Apologies but I am still unclear...
    Do you mean that if the .mdf and .ldf files are put on two different drives, then you can only use the select operation and NOT the insert/delete/update ?
    Tuesday, May 29, 2012 1:21 PM