Introduction
In this article, we'll learn the best practices of moving a database to another physical location on the Server.
One of my friends was moving the database files to another location from the default location. He suddenly ran into an error stating, "Unable to Open the physical file "xxx" . Operating system error (Access is denied.) ".
This is an unexpected error on production server. When he asked me for help, I thought it would be good to share the solution with others too so that they can avoid this problem in their application.
The general recommendation is to keep database physical files on a different location from the default location . It would be better to separate the data and log files on different logical drives. It will boost the I/O operations.
Here are the steps involved in moving files onto another location.
Access to required Security Permissions
Make sure that the new physical location of database has the required security permissions. That folder must have full control over the MSSQLSERVER service. If you do not give permission, it will throw an error "unable to open the physical file".
Take database to offline mode
Before starting the process, make sure that you have taken the database in offline mode. If you do not set it to offline mode, it may have data discrepancy to new database.
- ALTER DATABASE CopyMoveDBTest SET OFFLINE WITH ROLLBACK IMMEDIATE;
Alter the logical file path to new path
In this step, you have to modify the logical file path of .MDF , .NDF and .LDF files. Get the list of current logical names with path of database.
- USE master;
- GO
-
- SELECT name, physical_name AS CurrentLocation, state_desc
- FROM sys.master_files
- WHERE database_id = DB_ID(N'CopyMoveDBTest')
Here is the command to alter the logical path. Make sure that the new location exists. Otherwise, it will give the "directory lookup failed" error.
- USE master;
- GO
-
-
- ALTER DATABASE CopyMoveDBTest
- MODIFY FILE ( NAME = CopyMoveDBTest,
- FILENAME = 'F:\TempDB\CopyMoveDBTest.mdf');
-
- ALTER DATABASE CopyMoveDBTest
- MODIFY FILE ( NAME = CopyMoveDBTest_log,
- FILENAME = 'F:\TempDB\CopyMoveDBTest_log.ldf');
Move physical files to new location
Before starting this process, make sure that you have completed the above steps . Just move the physical file using copy/paste, Robocopy, or any other option you would like.
Verify the logical name and location
Before moving to the next step, verify the changes you have made. Make sure that the new location is correct.
- USE master;
- GO
-
- SELECT name, physical_name AS CurrentLocation, state_desc
- FROM sys.master_files
- WHERE database_id = DB_ID(N'CopyMoveDBTest')
Bring database to Online mode
After completing the above steps, you are done with all the required changes but your database is in an offline state. So, you need to bring it back to the online mode
Set database mode to online
- USE master;
- GO
- ALTER DATABASE tempdb SET ONLINE;
Summary
In this article, you have learned how to move database files to another location safely. Thanks for reading. If you have any other approach in mind, please let me know via the comments section.