Knowing where database files are placed SQL 2008

Is the master database always placed in C:\Program Files\Microsoft SQL Server\MSSQL$<Instancename>\Data ? If the instance is down, how weould I find it if it where placed elsewhere?
Where is the placement of the other attached custom databases stored? Master database? Other places?
LVL 2
itniflAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
the entry point is the master database; of which the location is specified in the startup options:
http://msdn.microsoft.com/en-us/library/ms190737%28v=sql.105%29.aspx
and hence yes, the master db could be anywhere on the disk...

from there, the master db contains the references to the other databases and their files.
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Not necessarily, you can place master and system databases under any location.
If the instance is down, then it would be difficult to identify the files location.
You can use the below query to find the file location of all databases:

select DB_NAME(dbid),* from sys.sysaltfiles
0
 
Eugene ZConnect With a Mentor Commented:
you can pre- record all this info as per above posted info
and \or from registry via , for example, script from
the post
http://sqlandme.com/2012/01/05/sql-server-identifying-default-data-directory-for-multiple-instances-through-registry/
or like this
declare @SQLDataRoot varchar(500)
exec master..xp_instance_regread
   @rootkey='HKEY_LOCAL_MACHINE',
   @key='SOFTWARE\Microsoft\MSSQLServer\Setup',
   @value_name='SQLDataRoot',
   @value=@SQLDataRoot output

also you can use registry via regedit to find this info as well

and\or powershell ,  WMS ,etc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.