SQL - Move SQL Server Database Files

Due to lack of space on the disk or database server relocation requirements are the main key points to move our data files from one drive location to the other location. Sometimes, we need to follow the standard best practises to secure our data from any disaster on the server and want to move our data files on the secured drive. 

Key Points- Before moving our data files from one drive location to another location, we should have done the following things-

1. Always do these operation in the down time to avoid the miss of any transactional data.
2. You should know the current database files Logical Name and Physical Location.
3. You should have the access right on the shared location from the both side means old location as well as on the new shared location.
4. If your database goes in Recover Pending Mode then you must have the access rights on the database files to resolve this issue.
5. Before moving your database, always take your database into offline mode WITH ROLLBACK IMMEDIATE
6. All the action on the database should use with ALTER DATABASE commands.

To understand all the movement process, we are going to create a Demo database and move their database file on the other location. When you are creating database then database creates the database files on the default location where you installed your SQL Server.

USE MASTER GO ---- Create Database CREATE DATABASE DEMO

After creating the database on your server, you are able to view your database as-

Actual Database files Logical Name and Physical Location

After creating the database into the SQL Server, we need to check the actual file name and stored location of these files as given below-

USE MASTER GO ----- PULL THE FILE NAME AND STATUS SELECT NAME AS LOGICAL_FILENAME, PHYSICAL_NAME AS FILE_LOCATION, STATE_DESC AS ACTUAL_STATUS FROM SYS.MASTER_FILES WHERE DATABASE_ID = DB_ID('DEMO');

File Locations are -

How can we move the Database Files? 

There are lots of option to relocate your existing file to another location and some of them are given below-

1) First of all, set your database as offline by using the below command -

USE MASTER GO ---- SET OFFLINE ---- WITH ROLLBACK IMMEDIATE ALTER DATABASE DEMO SET OFFLINE WITH ROLLBACK IMMEDIATE; GO

Watch the actual status of your database in SQL Server as given below-

Now, your database in offline mode, so you need to move the both files on the other location by using the following commands-

USE MASTER GO ----- Command for MDF File Movement ALTER DATABASE DEMO MODIFY FILE (NAME = DEMO, FILENAME = 'E:\Data\Demo.mdf'); -- New file path ----- Command for LDF File Movement ALTER DATABASE DEMO MODIFY FILE (NAME = DEMO_Log, FILENAME = 'E:\Data\Demo_log.ldf'); -- New file path

After running the above command, you will see the following messages-

The file "DEMO" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "DEMO_Log" has been modified in the system catalog. The new path will be used the next time the database is started.

After restarting the SQL server services, you have to make the database as online mode as given below-

USE MASTER GO ---- SET ONLINE ALTER DATABASE DEMO SET ONLINE;

If you are getting the following issue that means you don't have the access permissions on the database files - 

Msg 5120, Level 16, State 101, Line 19 Unable to open the physical file "E:\Data\Demo.mdf". Operating system error 5: "5(Access is denied.)". Msg 5120, Level 16, State 101, Line 19 Unable to open the physical file "E:\Data\Demo_log.ldf". Operating system error 5: "5(Access is denied.)". Msg 5181, Level 16, State 5, Line 19 Could not restart database "DEMO". Reverting to the previous status. Msg 5069, Level 16, State 1, Line 19 ALTER DATABASE statement failed.

If you try to view your database in SQL Server, it will be look like in Recovery pending mode as given below–

There no need to be worried because you don't have the access permissions on your databases files because database file locations are reset in the SQL Server as -

USE MASTER GO ----- PULL THE FILE NAME AND STATUS SELECT NAME AS LOGICAL_FILENAME, PHYSICAL_NAME AS FILE_LOCATION, STATE_DESC AS ACTUAL_STATUS FROM SYS.MASTER_FILES WHERE DATABASE_ID = DB_ID('DEMO');

File Locations are -

LOGICAL_FILENAME	FILE_LOCATION	ACTUAL_STATUS
DEMO	E:\Data\Demo.mdf	ONLINE
DEMO_log	E:\Data\Demo_log.ldf	ONLINE

So, go on the shared location where all the data files are stored and give the access or sharing permissions on them as given below-

After giving the permissions on the requested data files, you can stop your SQL Services and just copy/cut these files on put on the new location or just run the above process steps again (in this case SQL Services should be running)

After doing all the steps, just restart your SQL Services and your database files are available on the new location.

Conclusion

We don't have to stop the SQL Server service to move database files, but you do have to take the specific database offline. This is because you can't move files while they're being accessed and taking the database offline stops the files from being used by the SQL Server application. Before moving database files, you should have the access permissions on the shared location as well as on the database files also. After move them online, we should restart the SQL Server Services.