What is the default location for a DB in SQL Server 2008?

Question

I detached a database in SQL Server 2008 but am unable to find the physical file to move. Where is this located?

Answer 1

There's no guarantee that this is where the file will be, but the default location is:

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

This is the default default, but it can be changed to a different default. The quickest way to check this is in the SSMS, connect to the server, right-click the server in the Object Explorer, go to Properties and then go to the Databases pane, and the defaults are listed in there.

^{(you might have a different instance name from MSSQL.1, and if you're running 32-bit SQL on Windows 64-bit then it'll be Program Files (x86)}

Answer 2

Since your default location can be changed AND the initial location depends upon where you select in the installation, there is no correct answer here. You can list all your current database file locations by :-

select * from sys.master_files

And list your default locations through T-SQL you can currently use

EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @dir output, 'no_output'

EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @dir output, 'no_output'

Please note these two commands above may be replaced in future iterations of SQL Server.

Regards,

Mark

@retracement

Answer 3

I hope this is the answer you are looking for System and Sample Databases

The default location of the database and log files isProgram Files\Microsoft SQL Server\MSQL10.<InstanceName>\MSSQL\. This location will change if the default directory was changed when SQL Server was installed.

Answer 4

Depend on your database configuration. Are you using cluster mode or single server ? It will be located on different location for each files.