Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know database instance names are stored in the registry in subkeys of HKLM:SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL and the path to master.mdf is stored in HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\__InstanceId__\\MSSQLSERVER\Parameters@SqlArg0. However, I don't see a table in master.mdf or a registry key where the names of the other attached databases are stored.

I know I can look in sys.databases for the databases. However, what I want to know is how to find this information out without sql server running. Is it stored in the master.mdf? In the registry? Can I get that information with a hex editor, OrcaMDF or something else? How does Sql server know what databases to attach at startup?

share|improve this question
    
They are stored in master. Why do you to get this information without SQL Server running? –  Mark Storey-Smith Mar 2 at 7:29
    
@MarkStorey-Smith forensic analysis and pen testing. –  Justin Dearing Mar 2 at 13:00

2 Answers 2

I don't see a table in master.mdf ... where the names of the other attached databases are stored.

That would be sys.databases. Running sp_helptext 'sys.databases' will quickly reveal what is the catalog table backing it:

CREATE VIEW sys.databases AS
    SELECT d.name, d.id AS database_id,
...
FROM sys.sysdbreg d 
...

To query sys.sysdbreg see Using a Dedicated Administrator Connection.

share|improve this answer
    
I don't see sys.sysdbreg in OrcaMDF. I'll ask the author about it. –  Justin Dearing Mar 2 at 14:50
    
Update, that table is actually in mssqlsystemresource.mdf. However, I still don't see that there. –  Justin Dearing Mar 2 at 15:40
1  
@JustinDearing - It is in master.sys.sysdbreg as this answer says. If you look at the execution plan for SELECT database_id,name FROM sys.databases you can also see this. To verify this in the DAC you can find out the physical location with SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM sys.sysdbreg then inspect those pages with DBCC PAGE for example. –  Martin Smith Mar 2 at 18:53
    
@MartinSmith Thanks I understand that the master.sys.sysdbreg is the table in question. My question was how to see the files without SQL Server running. OrcaMDF, the tool I normally use to parse MDF files offline does not list that table. Its obvious I'll have to patch OrcaMDF myself (if the original author doesn't beat me to it). The T-SQL you suggested will be quite helpful in this regard. –  Justin Dearing Mar 3 at 1:24

The user database information is stored within the master database. Once the system databases are online SQL Server will pull that list and begin bringing them online.

As user databases can be physically created in any directory locally on the server (or UNC path with SQL Server 2012) there is no shortcut method of grabbing that list without SQL Server running.

You might attempt pulling the default path of database files from the registry as a start but again a database is not restricted to that directory.

Update

Based on your last comment, you can get the list of user databases by reviewing the SQL Server error log files. These logs indicate the start-up sequence of the database engine and the user databases. The SqlArg1 registry key has the path the ERRORLOG files will be written to and you can parse the specific files in the directory with this PowerShell command:


dir ERRORLOG* | foreach {Get-Content $_ | Where-Object {$_ -match "Starting up database"}}

The output returned should be similar to this: enter image description here

share|improve this answer
    
Thanks for the update about the log. I think I'll be able to get the actual list from mssqlsystemresource.mdf. However, that's another good forensic tool in case databases are being detached/reattached. –  Justin Dearing Mar 3 at 1:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.