A quick method to find all the data and log file paths for all databases on a database server instance.
SELECT name, physical_name AS current_file_location FROM sys.master_files
Example Output:
name current_file_location master C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAmaster.mdf mastlog C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAmastlog.ldf tempdev E:MSSQLData empDB.MDF templog C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA emplog.LDF tempdev2 E:MSSQLDATA empdev2.ndf modeldev C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAmodel.mdf modellog C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAmodellog.ldf MSDBData C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAMSDBData.mdf MSDBLog C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAMSDBLog.ldf ReportServer E:MSSQLDataReportServer.mdf ReportServer_log E:MSSQLDataReportServer_log.LDF ReportServerTempDB E:MSSQLDataReportServerTempDB.mdf ReportServerTempDB_log E:MSSQLDataReportServerTempDB_log.LDF
