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.
Go to Top of Page

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
Go to Top of Page

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';
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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]

Go to Top of Page
   

- Advertisement -