Thursday, February 1, 2018 3:36 PM
The default location of the "data" and "log" files for SQL Server database is "C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA" for version 2016 unless it is set differently during the installation process.
The core issue in discussion here is the copy the database files from one drive to another in case of a scenario where you current drive is getting exhausted. In my case the C: drive was running of space and I wanted to move the files to F: drive
So start the SQL Management Studio and fire the following query to understand the current file locations for a specific DB.
SELECT name,physical_name FROM <database_name>.sys.database_files
Once you have verified the locations, just copy the .mdf and .ldf files from the location listed in the output of above query and move to the desired drive (location). At this instance you have the database DATA nd LOG files in 2 drives, and you want it at just the new location. So before you are ready to delete the files from the old location first you must change the registry of those file at Server level. You can edit the file locations using the following query command
ALTER DATABASE <database_name>
MODIFY FILE (NAME = <database_name_file_name_for_mdf>, FILENAME = 'F:\DATA\my_db.mdf');
ALTER DATABASE
<database_name> MODIFY FILE (NAME = <database_name_file_name_for_ldf>_Log, FILENAME = 'F:\DATA\my_db.ldf');
Note: <value> are used as placeholders, do replace them with your respective values
The final part, is the take the DB offline delete the files in the old location and bring the DB online again so that it will start using the files from new location.