How to move C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf to G:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf

Question

I have all system databases in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf. How do I move the system databases to G:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf? Will it cause any issues. I have one database that is existing in the current sqlserver where the datafile is in G:\Data\x.mdf

Answer 1

@Shashank Singh , used the below command to move the files. Deleted all tempdb datafles,log files and ndf files.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'F:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\ tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\ templog.ldf');
GO

All temp files were created:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO

name CurrentLocation state_desc
tempdev F:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ONLINE
templog E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\templog.ldf ONLINE
temp2 E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf ONLINE
temp3 E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf ONLINE
temp4 E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf ONLINE

.ndf files did not exist prior to moving the system databases so my question was any reason why it was generated? Will the .ndf files grow?

Answer 2

Hi @Avyayah ,

There shouldn't be a problem moving the system databases, you can follow the official documentation here:

Move System Databases
https://learn.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-ver15

----------

(If the reply was helpful please don't forget to upvote or accept as answer, thank you)

Best regards,
Leon

Answer 3

Do I need to remove the ndf files? I read it might cause performance issue if I remove the file.

You can, this should not be an issue. I believe you have followed the documentation and changed the Tempdb file location. Please note for tempdb, since it is recreated after restart you DO NOT need to physically move the files like you do for master, msdb and model DB. You just need to change the location using alter database command and next time when SQL Server restarts it will create NEW tempdb files on the location mentioned. In your case

tempdev F:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ONLINE
templog E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\templog.ldf ONLINE

So this is new file created by SQL Server. Now you can safely go and remove below old tempdb files

temp2 C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf ONLINE

temp3 C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf ONLINE

temp4 C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf ONLINE

Now system will not create more than one mdf file of tempdb post alter DB command. If you have multiple tempdb files you would have to manually add the tempdb file. This will not need any system restart and can be done any time.

EDIT: From your subsequent question where you said that even after removing ndf files on C drive it came back again

It seems like you have NOT moved the ndf files using alter database command. You need to move all the mdf, ldf and ndf files of tempdb after that you would not need to physically move the files SQL Server will create new ndf files. Note after you have run alter database modify file for all the ndf files, stop sql server service, remove old ndf files and then start SQL Server again. You will see new files.

Answer 4

Are you saying that sys.master files gives the same result as tempdb.sys.database_files?

If so, is this what you want?

If not, then use ALTER DATABASE to change the location for those that are in the wrong place and then restart your SQL Server.

As for the size, we can't say how large tempdb you need. Only you can. You can run your SQL server for a while and see what size the files grow to, and then specify that size as the startup size. I.e., so the files won't have to grow after a re-start of SQL Server.

Make sure you have the same size for the files. Since you are on SQL server 2016, you will get even files growth (if one file grows, then all grows).

Answer 5

Followed the documentation and was able to move the system databases but why do I see the ndf files for tempdb?

Using Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

name CurrentLocation state_desc

tempdev F:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ONLINE

templog E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\templog.ldf ONLINE

temp2 C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf ONLINE

temp3 C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf ONLINE

temp4 C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf ONLINE

Do I need to remove the ndf files? I read it might cause performance issue if I remove the file.

Also followed the YouTube link: https://www.youtube.com/watch?v=QAlgpxjJCiM