File Paths in sysfiles on Replica Database

• alex.sqldba

  SSChampion

  Points: 10254

  More actions

  July 22, 2020 at 10:44 am

  #3773571

  Morning Guys,

  How can I get the correct file path from sysfiles (or anywhere in the database with t-sql) when the db is the read only replica.

  It appears that sysfiles shows the location of the files on the principle database not on the replica. In this case the are in different locations.

   

  Cheers

  Alex

   

• alex.sqldba

  SSChampion

  Points: 10254

  More actions

  July 22, 2020 at 11:15 am

  #3773697

  Ah, looks like sys.sysaltfiles is what I needed.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738277

  More actions

  July 22, 2020 at 3:40 pm

  #3773853

  That's deprecated. I think you want master.sys.master_files

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

• alex.sqldba

  SSChampion

  Points: 10254

  More actions

  July 22, 2020 at 3:53 pm

  #3773857

  Ah, yes good point! Cheers Steve!