Can find where my sql2K database files are stored

Question

Hi,

I am on a new computer, but I have access to my old computers hard drives.

I can't find where the databases for my sql server 2k are stored.

I have sql2005 express on the same computer.

The folders I have:

/program files/microsoft sql server/
/program files/microsoft sql server/80
/program files/microsoft sql server/90
/program files/microsoft sql server/MSSQL
/program files/microsoft sql server/MSSQL.1

Which one is referring to sql2k?

Update My /80 folder only has 2 folders in it, /COM and /Tools.

No Data!

Actually the only one with data is /mssql.1/mssql/data/

hmm...guess I have to throw these drives back into my older computer, what a pain!

Answer 1

%program files%\Microsoft SQL Server\MSSQL\Data

That's the most likely culprit.

SQL Server 2005 is %program files%\Microsoft SQL Server\MSSQL.1\Data

SQL Server 2008 is %program files%\Microsoft SQL Server\MSSQL10\Data

Answer 2

/program files/microsoft sql server/80 - for SQLL 2000, 90 for SQL 2005 stores tools

/program files/microsoft sql server/MSSQL stores instance data (data files, log files)

open registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names to see what instance names are associated with what SQL server folders. in my case SQLEXPRESS is MSSQL.1, so probably MSSQL will be for SQL Standard (i.e. default instance) in your case

Answer 3

C:\Program Files\Microsoft SQL Server\MSSQL\Data

Answer 4

The 80 folder is the program files for 2000 - the data files themselves could really be anywhere depending on how you had created them in the first place but I'd expect them to be in the MSSQL folder with the 2005 in MSSQL.1

Answer 5

If you want to find where the data files for an existing database are and you can start and access SQL Server, then look at the sysfiles table in the database(s) you want to check:

SELECT * FROM sysfiles

This returns a number of columns, the one you'll be interested in is called 'filename'