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,211 Reputation points
2020-10-24T20:08:30.87+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,257 questions
{count} votes

Accepted answer
  1. Avyayah 1,211 Reputation points
    2020-10-26T13:44:41.6+00:00

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


9 additional answers

Sort by: Most helpful
  1. Leon Laude 85,566 Reputation points
    2020-10-24T20:47:26.907+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

  2. Shashank Singh 6,246 Reputation points
    2020-10-25T12:56:57.007+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  3. tibor_karaszi@hotmail.com 4,301 Reputation points
    2020-10-26T15:08:31.967+00:00

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

    1 person found this answer helpful.
    0 comments No comments

  4. Avyayah 1,211 Reputation points
    2020-10-25T05:08:45.34+00:00

    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