Easy Way to Change the Location of SQL Server Datafiles

Have you decided to change the location of your SQL server datafiles from default location to your specified location? If yes then I will guide to you for this throughout the article. Before going to change the location of your SQL server data files (mdf & ndf), you will have to check the current location of your data files. To do this run below query:

Select Name, Physical Name

From sys.master-files

Where Name = “my-database-name”

Where “my-database-name” is the name of your database.

Output of query:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\mysql-database-name.mdf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\mysql-database-name2.mdf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\mysql-database-name3.mdf

The output of query shows that you database’s data files are stored in the C drive of your computer.

Prerequisites: Before going to change the location of your data files, you have to put your database in office mode. You can do this by “ALTER” command:

ALTER Database my-database-name

SET Offline

Go

To change location of Data Files: You have to run “Alter” command again to change the location of data files. The queries are:

ALTER Database my-database-name

Modify File

(

Name = my-databse-name,

FileName = ‘C\data\my-database-name.mdf’

)

Go

Now use copy & paste or drag & drop method to change the location of data files. To verify this, you have to set your database Online.

You may like to read: SQL server Data Transfer from One server to another