how to know the .mdf file belongs to which databas

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

how to know the .mdf file belongs to which databas

Author

Topic

chanduraag
Starting Member

14 Posts

Posted - 2013-04-26 : 09:33:33

Hi All,

i would like to know ...how to find the .mdf file belongs to which database....i tried with
DBCC CheckPrimaryFile ('Path of Mdf File', 2)

but it's showing the below error
---------
Msg 5123, Level 16, State 20, Line 1
CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file 'F:\MSSQL2008\MSSQL$DEFAULT\Userdatabases\databasename.mdf'.
---------------
is this DBCC command is used for the already retired databases.please provide your valuable advices for this .

Thanks All in Advance

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-26 : 10:06:40

[code]SELECT * FROM sys.master_files [/code]Look for name and physical_name columns.

chanduraag
Starting Member

14 Posts

Posted - 2013-04-26 : 10:19:24

thank you very much for the prompt response..
but it's giving the .mdf and .ldf files of all the databases in the server

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-26 : 10:49:19

You can use a where clause to limit it to one database, or list the name of the database for each so you know which file belongs to which database.

SELECT DB_NAME(database_id),*  FROM sys.master_files ;
SELECT *  FROM sys.master_files WHERE DB_NAME(database_id) = 'YourDatabaseName';

chanduraag
Starting Member

14 Posts

Posted - 2013-04-26 : 11:12:02

i vill axplain my question what actually i would like to know.......i am having a .mdf set from this fileset i would like to know which database MDF file is this???(to which database it is mapped),
Even i don't want to use sp_helpfile it will gives the list of .mdf and .ldf files associated with a particular database.

........thankq

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-04-27 : 03:51:31

Are you saying that currently the MDF is not attached to the SQL Server instance?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-04-27 : 23:04:31

[code]
SELECT *
FROM sys.master_files
WHERE physical_name like '%' + 'theMDFFileName'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources