SQL Server – Move sql database to another drive 1

We can move sql server system database and user database to another drive, in case SAN disk is full then copy the sql server mdf and ldf to another drive that has more drive space. Sql server will not allow to move database files while online.  We have to put database offline and then only it will allow to copy the mdf and ldf files.

In some article you may have seen that can be move database files to another drive using sp_detach_db and sp_attach_db. refer this article How to move database using sp_attach_db and detach_db option

Now same thing we will do using ALTER DATABASE option in SQL server 2005 and later version.

How to move sql database to another drive

Below are the steps involve for moving database file from one drive location to another location on same server

1. Execute the below query to put the database offline so that its not accessible to users.

ALTER DATABASE testdb SET OFFLINE.

2. Copy the files to the new location.

3. Run the below query to modify the file location in sql server

USE MASTER
ALTER DATABASE testdb
MODIFY FILE (NAME = testdb_data, FILENAME = ‘D:\testdb_data.mdf ‘)

ALTER DATABASE testdb
MODIFY FILE (NAME = testdb_log, FILENAME = ‘E:\testdb_log.ldf’)

4. Execute the below query to bring back the database online.

ALTER DATABASE testdb SET ONLINE.

Another option available in sql server is Backup and restore.

One comment on “SQL Server – Move sql database to another drive

  1. Reply Nikhil Mar 20, 2013 9:59 am

    Hi,

    I am using SQL Server Express Edition Client Tool on My Machine.

    For Moving the database from one folder to another I have used the
    above commands. When I executed the 4th command i.e.
    ALTER DATABASE Test SET ONLINE.
    I am getting below error and not able to access the database

    Error:
    ——————————————————————-
    Msg 5120, Level 16, State 101, Line 2
    Unable to open the physical file “C:\Test\TestData\Test.mdf”. Operating system error 5: “5(Access is denied.)”.
    Msg 5120, Level 16, State 101, Line 2
    Unable to open the physical file “C:\Test\TestData\Test_log.ldf”. Operating system error 5: “5(Access is denied.)”.
    File activation failure. The physical file name “C:\Test\TestData\Test_log.ldf” may be incorrect.
    Msg 945, Level 14, State 2, Line 2
    Database ‘Test’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
    Msg 5069, Level 16, State 1, Line 2
    ALTER DATABASE statement failed.
    ——————————————————————

Leave a Reply

  

  

  


6 − = 5