none
moving system database and log files RRS feed

  • Question

  • Is it ok to move master, model and msdb database and log files from C drive to D drive, to free up 10 GB space on C drive? Do I need to take any precautions, other then having a verified backup?

    I used the steps copied below for moving some user databases files and logs, but I am not sure if I will be able to move master database and log files using the procedure below after I run the first step of taking master offline? Do you suggest any other way? 

    1. offline the database.

    alter database db_name set offline;

    2.Physically copy the mdf and ldf files to the new location(D drive).

    3. modify file path
    use master;

    Go alter database dbname modify file ( name = 'logicalName', filename = 'D:\new\db_name.mdf'); --> This is the new file location



    4.Bring the database online.

    alter database db_name set online;

    Thanks in advance.

    Thursday, July 11, 2019 1:19 AM

Answers

  • Hi kvd123,

     

    Moving master database is a bit different from moving other system database.

    Here are the steps for moving an undamaged system database. For more details, please refer to :

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

     

    A. Moving system databases ( other than the master database) :

    1. For each file in the database to be moved, use the ALTER DATABASE command with the MODIFY FILE option to specify the new physical location.

    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
    

    2. Stop the SQL Server instance.

    3. Physically move the files.

    4. Restart the SQL Server instance.

    5. Verify the change by running the following query:

    SELECT name, physical_name AS CurrentLocation, state_desc  
    FROM sys.master_files  WHERE database_id = DB_ID(N'<database_name>');
    

    B. Moving master database:

    The biggest difference between moving this database and moving other system databases is that you must go through the SQL Server Configuration Manager.

    To move the master database, follow these steps.

    1. Open the SQL Server Configuration Manager. Right-click the desired instance of SQL Server, choose Properties, and then click the Startup Parameters tab.

    2. Edit the Startup Parameters values to point to the new directory location for the master database data and log files and click Update.

    3. Stop the instance of SQL Server and physically move the files for to the new location.

    4. Restart the instance of SQL Server.

    5. Verify the file change for the master database by running the following query:

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

    Regards,

    Sabrina


    Thursday, July 11, 2019 8:06 AM

All replies

  • Please read this article

    For master db

    The following example shows the parameter values for the default location of the master data file.

    -dC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\master.mdf

    -lC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

    If the planned relocation for the master data file is E:\SQLData, the parameter values would be changed as follows:

    -dE:\SQLData\master.mdf

    -lE:\SQLData\mastlog.ldf

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


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by -Kiron Thursday, July 11, 2019 10:24 AM
    Thursday, July 11, 2019 4:35 AM
    Moderator
  • Hi kvd123,

     

    Moving master database is a bit different from moving other system database.

    Here are the steps for moving an undamaged system database. For more details, please refer to :

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

     

    A. Moving system databases ( other than the master database) :

    1. For each file in the database to be moved, use the ALTER DATABASE command with the MODIFY FILE option to specify the new physical location.

    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
    

    2. Stop the SQL Server instance.

    3. Physically move the files.

    4. Restart the SQL Server instance.

    5. Verify the change by running the following query:

    SELECT name, physical_name AS CurrentLocation, state_desc  
    FROM sys.master_files  WHERE database_id = DB_ID(N'<database_name>');
    

    B. Moving master database:

    The biggest difference between moving this database and moving other system databases is that you must go through the SQL Server Configuration Manager.

    To move the master database, follow these steps.

    1. Open the SQL Server Configuration Manager. Right-click the desired instance of SQL Server, choose Properties, and then click the Startup Parameters tab.

    2. Edit the Startup Parameters values to point to the new directory location for the master database data and log files and click Update.

    3. Stop the instance of SQL Server and physically move the files for to the new location.

    4. Restart the instance of SQL Server.

    5. Verify the file change for the master database by running the following query:

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

    Regards,

    Sabrina


    Thursday, July 11, 2019 8:06 AM
  • Thank You Sabrina.

    That worked like a charm.

    Thursday, July 11, 2019 9:25 PM