Getting directory information for Data/Log/TempDB/Backup/etc

• Jake Shelton

  Hall of Fame

  Points: 3109

  More actions

  November 18, 2014 at 4:40 am

  #298570

  Hi all,

  Is there an Admin script that yields the following directories?

  Data

  Data Root

  Log

  TempDB

  Shared Feature

  Backup

  Thanks,

  Jake

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 18, 2014 at 4:53 am

  #1759214

  well, i know three of them are stored in the registry, o a per-isntance basis.

  EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory';

  EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData';

  EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog';

  so that leaves the items below:

  what is the data root?

  my tempdb is split across multiple files, so do you want where the current first tempdb file resides?

  Jake Shelton (11/18/2014)

  ---

  Hi all,

  Is there an Admin script that yields the following directories?

  Data

  Data Root

  Log

  TempDB

  Shared Feature

  Backup

  Thanks,

  Jake

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 18, 2014 at 4:58 am

  #1759217

  when i query my dev server for tempdb, for example, i get three locations:

  select * from tempdb.sys.database_files

  D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf

  L:\Logs\templog.ldf

  E:\MSSQL\Data\tempdb2.ndf

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  November 18, 2014 at 5:24 am

  #1759234

  Another wrinkle is that the databases could be installed in different directories. I don't have mine that way, but it could be done. If you query sys.master_files, it will give you the data and log directories for all databases.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Jake Shelton

  Hall of Fame

  Points: 3109

  More actions

  November 18, 2014 at 6:28 am

  #1759250

  Good work fellas, thanks!! 🙂