How to move MSSQL database files to new location

As we all know when installing MSSQL server it creates database files on the system drive (C Drive). But as the database size increases the system drive space decrease and impact the system performance. So it is suggested to configure the non system drive while creating new database.

But what can we if we have existing database on system drive. We can safely move our database files to non system drive.

So lets get started.

Step1: Find out the current file paths

Select * from SampleDatabase.sys.database_files

It will give you the path something like below:

SAMPLEDB C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER17\MSSQL\DATA\SampleDatabase.mdf
SAMPLEDB_Log C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER17\MSSQL\DATA\SampleDatabase.ldf

Step2: Make Database Offline to make changes

Alter database SampleDatabase set Offline;

Step3: Copy Files to new location before performing below commands

Alter database SampleDatabase
MODIFY File(
Name='SAMPLEDB',
FILENAME='D:\MSSQLDatabase\DATA\SampleDatabase.mdf'
)
GO
Alter database SampleDatabase
MODIFY File(
Name='SAMPLEDB_log',
FILENAME='D:\MSSQLDatabase\DATA\SampleDatabase.ldf'
)

Step4: Bring Database Online

Alter database SampleDatabase set Online;

Step5: (Optional) If you encounter errors like access denied

Open services.msc go to SQL SERVER server and open properties.

Select Logon Tab

Select Local system account click apply.

Also give a stop and start to the service.