My colleague came and asked me if the client wanted to change their current SQL Server’s DATA folder’s directory to another directory in their server, can it be done?
The answer is it can be done.
Then, the next question is how? There is a method I found online and tried it and it worked for my local machine. The procedure of changing is not difficult and it is simple to understand. If you are using Window machine, the steps are as below:
Step 1: Identify the mdf and ldf files.
If you did not change the file path during the installation, by default it will set at, C:\Program Files\Microsoft SQL Server\<server_version>\MSSQL\DATA.
So, your filenames are:
Just make sure there are .mdf and .ldf files with the matching database name. For example, in my case,
Step 2: Set the new file path for the SQL database files.
For my case, I will move the current file path to another drive which I have created a folder to hold the data files. My path is E:\Data. Open the SQL Server Management Studio (SSMS) and execute the below script after you have changed the database name and new file path.
Then, what are the things to be changed?
ALTER DATABASE MoveDatabase
MODIFY FILE ( NAME = MoveDatabase,
FILENAME = 'E:\Data\MoveDatabase.mdf');
ALTER DATABASE MoveDatabase
MODIFY FILE ( NAME = MoveDatabase_log,
FILENAME = 'E:\Data\MoveDatabase_log.ldf');
There is a couple of things to be done before you can execute the above script in SSMS.
- Change the database name “MoveDatabase” to your database name.
- In some cases, the .mdf file name is with “_data” at the end of the database name. It could be something like, “MoveDatabase_data”. Just check your database name from step 1. It uses the same name from the step 1.
To execute the above SQL script, the SQL server’s service does not need to be stopped and the database does not need to be offline at this point of time, however, it is advised to ensure your production’s database is not in-used during this point of time to avoid any data losses.
Note: It is advisable to keep database folder in a different directory or best to be in different drive in a machine. Same goes to the database backup files.
Step 3: Set database to offline.
Run the following SQL script in the SSMS to set the database to offline.
ALTER DATABASE MoveDatabase SET OFFLINE;
This script is important from this point onward. Change the database name according to your database name. If the database is not set to offline, the following steps cannot be done.
Step 4: Move the database files to new location.
Move .mdf and .ldf files of the specific SQL database to a new location specified in step 2 either by using copy and paste or cut and paste function. If you are familiar with using command, it works the same.
Note: Moving files to another location, there is one important thing to look into, the folder or file permission access.
Step 5: Set database to online.
Run the following SQL script in the SSMS to set the database to online.
ALTER DATABASE MoveDatabase SET ONLINE;
From this point onward, the specific database starts pointing to new location. However, if you received an error message reads as below:
Msg 5120, Level 16, State 101, Line 13
Unable to open the physical file “E:\Data\MoveDatabase.mdf”. Operating system error 5: “5(Access is denied.)”.
Then, there is a need to set the folder or file permission access with the user account which you can do it at the Permission Setting of the folder. For more details, you can refer to this link, https://www.sqlshack.com/move-sql-database-files-mdf-ldf-another-location/.