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

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

Avyayah 1,291

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

Ratheesh K Nair 1 Reputation point

2020-10-25T05:04:56.557+00:00

You have to run the ALTER DATABASE MODIFY FILE TO new location and then shutdown the service , Copy\move the files and then start the service.

Run the select * sysaltfiles to find the new location of the files.

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

Answer accepted by question author

9 additional answers

Your answer

Ratheesh K Nair 1 Reputation point

2020-10-25T05:04:56.557+00:00

You have to run the ALTER DATABASE MODIFY FILE TO new location and then shutdown the service , Copy\move the files and then start the service.

Run the select * sysaltfiles to find the new location of the files.

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

Answer 1

Avyayah 1,291

@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?

Shashank Singh 6,251 Reputation points

2020-10-26T15:55:01.717+00:00

.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?

The answer as I have already said is because you did not specifically changed the location. I just tried your scenario and could easily reproduce it. I first just changed the location of Tempdb mdf and ldf file and not the ndf files. The mdf and ldf moved to new location while ndf did not. I had to run alter command to change the location and then they moved. please note you never have to move files physically. I believe you did some mistake from your side. Yes ndf files will grow unless you stop autogrowth on it
Avyayah 1,291 Reputation points

2020-10-27T22:39:02.543+00:00

I executed the following statement

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

Stopped Sqlserver Service, deleted temp datafile, logfile and ndf file. But since the information is in the master database temp datafile and logfile are created as well as ndf files. I don't think I can delete .ndf files.
Shashank Singh 6,251 Reputation points

2020-10-28T03:14:57.49+00:00

The above statement ONLY moves temodb mdf and ldf files, it does not moves ndf file. So if you delete all the files, post startup, tempdb mdf and ndf files will be created on new location while ndf will be recreated on old location. When tempdb is stopped you can delete all the files associated with it. For tempdb to work you only need mdf and ldf files. Ndf is added for tempdb performance
Avyayah 1,291 Reputation points

2020-10-28T04:30:08.87+00:00

Got it, it looks like it is normal to for tempdb to ndf files. Any advise on the size of ndf files that I should check on the server?
Shashank Singh 6,251 Reputation points

2020-10-28T04:43:04.12+00:00

The so called "initial size" of all the tempdb files (mdf +ndf) should be same and autogrowth(AG) should be same. Do not keep AG in percentage. You do not need to enable TF 1117, 1118 as your have SQL Server 2016. Also note since you have SQL Server 2016 you do not also have to keep AG value same as system takes care of that but doing beforehand is not going to harm. Just look at your tempdb growth and size the data files accordingly. If the thread has answered your question please close it by marking answer, and voting helpful on answer which has helped you.
tibor_karaszi@hotmail.com 4,316 Reputation points

2020-10-28T09:41:36.137+00:00

I replied to you yesterday, but you didn't pick up on that.

You need to move all the files that you want to move.

If you have more files than you want, you can remove them,

In general we have one data file per processor core, up to 8 files.

As for how large file files should be, we can't say that. Run SQL for a while and see what size they grow to. That is the size for tempdb that you need, apparently.

Answer 2

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

Executed the statement, stopped the service, removed all files but found ndf files in the same location. Any other suggestions?

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

Shashank Singh 6,251 Reputation points

2020-10-25T14:29:39.927+00:00

@Avyayah I have edited my answer and added new information, please check.

Answer 3

Avyayah 1,291

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 now moved to new location. These ndf files were generated after I moved the system databases. Any reason why it was generated after moving the files?

Shashank Singh 6,251 Reputation points

2020-10-26T09:35:53.667+00:00

@Avyayah What do you mean by files were generated by after I move system databases. You mean adter you moved master, msdb only them tempdb files moved? This is not correct they do not have any correlation. Unless you use alter database modify file command to move ALL the files they would not move. You must have used alter database command to move all the files.

Answer 4

Hi @Avyayah ,

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

The tempdb in In your SQL Server instance contains multiple secondary data files.
Each database has only one primary data file(.mdf). All other data files except the primary data file are scondary data files( .ndf). Where there are multiple database files, the .mdf file will always store the database’s internal configuration information and other internal system data.

>ndf files now moved to new location. These ndf files were generated after I moved the system databases. Any reason why it was generated after moving the files?

The tempdb is re-created every time SQL Server is started. Now that you have executed the "ALTER DATABASE tempdb MODIFY FILE(...)" command modified the physical_name of the tempdb .ndf files. After restarting the sql server service, the data file will be created in the new location.

In addition, it seems that you have already got suggestions for moving the system database from the responses of others. So if the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

tibor_karaszi@hotmail.com 4,316

Regarding the tempdb files: I have written a blog post regarding managing tempdb, including relocating database files. The key is that tempdb is created at startup based on sys.master_files regarding the database files. You find my blog post here: http://sqlblog.karaszi.com/managing-tempdb/

Share via

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

9 additional answers

Your answer