Sql-server – Do NDF files move with Alter Database automatically

sql server

I know you don't want to hear this but i am not a DBA, but I am responsible for a 2016 SQL server. From time to time I need to do some DBA work.

This time I need to move the tempdb (mdf, ndf, and ldf files) to another volume.

When i run the alter database query do i need to specify the ndf files in the query? Or do those move automatically because they are part of the file group?

Being each of the ndfs have logical names, I am thinking I need to, but just want to be certain.

I know I need to specify the LDF files.

These are all the files we have associated with the TEMPDB:

tempdev C:\Program Files\Microsoft SQL
Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf

templog C:\Program Files\Microsoft SQL
Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\templog.ldf

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

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

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

temp5 C:\Program Files\Microsoft SQL
Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_5.ndf

temp6 C:\Program Files\Microsoft SQL
Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_6.ndf

temp7 C:\Program Files\Microsoft SQL
Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_7.ndf

temp8 C:\Program Files\Microsoft SQL
Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_8.ndf

Thanks for the help!

-Evan

Best Answer

ALTER DATABASE can be used to do many things. In order to move the file you'll want to use MODIFY FILE to move your files to the specified location. This goes for mdf, ndf, ldf. Erik Darling blogged about this a while back and gave some examples.

ALTER DATABASE [Sample] MODIFY FILE ( NAME = Sample, FILENAME = 'C:\Whatever\Sample.mdf' );

From the docs:

MODIFY FILE Specifies the file that should be modified. Only one property can be changed at a time. NAME must always be specified in the to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size.

To move a data file or log file to a new location, specify the current logical file name in the NAME clause and specify the new path and operating system file name in the FILENAME clause. For example:

To move a data file or log file to a new location, specify the current logical file name in the NAME clause and specify the new path and operating system file name in the FILENAME clause. For example:

Related Question