How to obtain the location of a SQL Server 2008 database data file

There are three ways to get the physical location of a database data file in SQL Server:

sp_helpdb Bright

Name filename Filegroup size

------------- ------------------------------- ---------- -----------

Bright Z:\MSSQL\data\Bright.mdf PRIMARY 10130240 KB

Bright_log Z:\MSSQL\data\Bright_log. LDF NULL 3456 KB

Bright01 Z:\MSSQL\data\Bright01.mdf PRIMARY 10919616 KB

Bright01_log Z:\MSSQL\data\Bright01_log. LDF NULL 265344 KB

Bright02 Z:\MSSQL\DATA\Bright02.ndf PRIMARY 102080 KB

Select type_desc,name,physical_name,size from Bright.sys.database_files

Type_desc name Physical_name size

--------- ------------- -------------------------------- -----------

ROWS Bright Z:\MSSQL\data\Bright.mdf 1266280

LOG Bright_log Z:\MSSQL\data\Bright_log. LDF 432

ROWS Bright01 Z:\MSSQL\data\Bright01.mdf 1364952

LOG Bright01_log Z:\MSSQL\data\Bright01_log. LDF 33168

ROWS Bright02 Z:\MSSQL\DATA\Bright02.ndf 12760

Select type_desc,name,physical_name,size from Master.sys.master_files where database_id=db_id (Bright)

Type_desc name Physical_name size

--------- ------------- -------------------------------- -----------

ROWS Bright D:\MSSQL\DATA\Bright.mdf 1266280

LOG Bright_log D:\MSSQL\DATA\Bright.LDF 432

ROWS Bright01 D:\MSSQL\DATA\Bright01.mdf 1364952

LOG Bright01_log D:\MSSQL\DATA\Bright01.LDF 33168

ROWS Bright02 D:\MSSQL\DATA\Bright02.mdf 12760

For the general database, with the "sp_helpdb" command to obtain a more intuitive file size, the other two command returns the size of the actual number of blocks, the size of the data block is 8KB.

However, if a database is a read-only database that is recovered with the "restore Database with STANDBY", then only the third method, the file location obtained from the master database, is real.

How to obtain the location of a SQL Server 2008 database data file