Where are my databases located with MSSQL
Often you need to know/verify where the database files are located.
Applies to:
- Microsoft SQL Serve 2000
- Microsoft SQL Serve 2005
Graphical Interface for database location
Right clicking on the database, and properties.
Database properties - Files
SQL script for database location
You have more flexibility when using ISQL.
USE master;
go
SELECT SUBSTRING(name,1,20) Name,
SUBSTRING(filename,1,75) Filename
FROM sysfiles
go
USE model;
go
SELECT SUBSTRING(name,1,20) Name,
SUBSTRING(filename,1,75) Filename
FROM sysfiles
go
USE msdb;
go
SELECT SUBSTRING(name,1,20) Name,
SUBSTRING(filename,1,75) Filename
FROM sysfiles
go
USE tempdb;
go
SELECT SUBSTRING(name,1,20) Name,
SUBSTRING(filename,1,75) Filename
FROM sysfiles
go
go
SELECT SUBSTRING(name,1,20) Name,
SUBSTRING(filename,1,75) Filename
FROM sysfiles
go
USE model;
go
SELECT SUBSTRING(name,1,20) Name,
SUBSTRING(filename,1,75) Filename
FROM sysfiles
go
USE msdb;
go
SELECT SUBSTRING(name,1,20) Name,
SUBSTRING(filename,1,75) Filename
FROM sysfiles
go
USE tempdb;
go
SELECT SUBSTRING(name,1,20) Name,
SUBSTRING(filename,1,75) Filename
FROM sysfiles
go
Name Filename -------------------- --------------------------------------------------------------------------- master C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf mastlog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf (2 row(s) affected) Name Filename -------------------- --------------------------------------------------------------------------- modeldev C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf modellog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf (2 row(s) affected) Name Filename -------------------- --------------------------------------------------------------------------- MSDBData C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf MSDBLog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf (2 row(s) affected) Name Filename -------------------- --------------------------------------------------------------------------- tempdev c:\syv\tempdb.mdf templog c:\syv\templog.ldf (2 row(s) affected)