Where does sql server hold the paths of data and log files?



  • Sql server configs (logins/roles/server configs) are stored in masterdb

    Jobs/schedules are stored in msdb

    Where are the following values stored:

    1. Data and log file path of system dbs

    2. Data and log file path of user dbs. I want to know this because when I restore master db on another server then it doesn't auto create the user dbs in the GUI.



  • Since your Question is tagged with SQL2019 my answer is tested in the same version.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver15

    Contains a row per file of a database as stored in the master database. This is a single, system-wide view.

    For tempdb, view sys.master_files shows initial tempdb size. The values are used as a template for tempdb creation at startup of SQL Server. So, when tempdb grows it is not reflected in the view. To get current size of tempdb files, query tempdb.sys.database_files.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-files-transact-sql?view=sql-server-ver15

    Contains a row per file of a database as stored in the database itself. This is a per-database view.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-server-registry-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

    You can get master database data and log file location from here. You can get the same information by opening 'SQL Server Configuration Manager' located at 'C:\Windows\SysWOW64\SQLServerManager15.msc'.

    You will find the same information in old system tables and are discouraged by Microsoft to not use those. https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mapping-system-tables-to-system-views-transact-sql?view=sql-server-ver15 between old system tables and newer system views




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2