• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

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?
0
itnifl
Asked:
itnifl
3 Solutions
 
Raja Jegan RSQL 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
 
Guy Hengel [angelIII / a3]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
 
Eugene ZCommented:
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now