Move Database File from One Drive to Another Drive

Introduction

Sometimes, we need to change the database file location from one drive to another drive. Here are some simple tips on how we can achieve this goal.

Steps

1. Suppose you have a database file named Test and you want to move it to other drive from the default location. First, run the following query to get information about the database file.
   SQL

   select * from Test.sys.database_files

   Using databse_files view, you can get information about databasefile such as Name, physical_name, state_desc. From physical_name column, you get the current location of the database file.

2. Run database OFFLINE script:
   SQL

   use master
   alter database Test set OFFLINE

3. Move database file (both .mdf, .ldf) to the desired location. In my case, I have moved my files in D drive SqlDatabase folder (D:\SqlDatabase).
4. Update database and modify File. Set FileName as your desired location:
   SQL

   ALTER DATABASE test 
   MODIFY FILE (
       NAME = 'test',
       FILENAME = 'D:\SqlDatabase\Test.mdf'
   )
   
   ALTER DATABASE test
   MODIFY FILE (
       NAME = 'test_log',
       FILENAME = 'D:\SqlDatabase\Test_log.ldf'
   )

5. Change the permission of these files and give full control to Users Group.

   

6. Run database online script:
   SQL

   ALTER DATABASE Test SET ONLINE 

By following these simple steps, you can move your database from one drive to another.

Points of Interest

• https://stackoverflow.com/questions/19060441/cannot-install-adventureworks-2012-database-operating-system-error-5-access-i/19061137#19061137

History

• 18^th October, 2018: Initial version