How to change the default location of MS SQL Server Data and Log files

Question

The default location of SQL Server data and log files on my machine is C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA. I want to change it to D:\Microsoft SQL Server\DATA because there is no enough space on C drive.  I tried AttachDbFilename in the connection string, but it did not work.

Answer 1

If you have MS SQL Server Management Studio installed on your machine, connect to the SQL server using MS SQL Server Management Studio and do the followings:

1. In Object Explorer, right-click on your server and click Properties.

2. In the pop up panel on that Properties page, click the Database settings tab.

3. In Database default locations, check the current default locations for new data files and new log files. To change a default location, enter a new default pathname in the Data or Log field, or click the browse button to find and select a pathname. Click OK to set the new pathname.

4. In Object Explorer, right-click on your server and click Restart to restart the SQL server, otherwise, the new pathname will not be selected.