November 24, 2009 at 6:43 pm
We have an .mdf and an .ldf file on the server that do not appear to be connected to any current database. Is there a script we can run to identify which database the files belong to? The files seem to be remnants from a database that may no longer exist, although I'm not sure how a database would be deleted leaving the associated files behind.
November 24, 2009 at 7:42 pm
Deletion of database might not be the case, it must have been detached.
There is no need to run a script to find the database, you can know it from the following steps in Management Studio..
In Object explorer, right click database node and select attach.
Attach databases dialog box opens, in the top section, under the section databases to attach..
Click the Add button, locate the .MDF File and select OK..
The wizard will display the database name of the .mdf file... click cancel button to cancel...
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 24, 2009 at 7:43 pm
kwilt (11/24/2009)
We have an .mdf and an .ldf file on the server that do not appear to be connected to any current database. Is there a script we can run to identify which database the files belong to? The files seem to be remnants from a database that may no longer exist, although I'm not sure how a database would be deleted leaving the associated files behind.
Use this syntax:
dbcc checkprimaryfile (N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DB1.mdf' , 2)
November 24, 2009 at 7:56 pm
Cool Script.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 24, 2009 at 9:59 pm
hi alex
What does that 2 indicates?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
November 25, 2009 at 5:40 am
abit more info about 0,2,3:
http://www.sqlservercentral.com/Forums/Topic241886-5-1.aspx#bm241943
November 25, 2009 at 7:04 am
I found a site that shows the option values for that command:
http://mesquiteit.com/online_help/kb/_1rl0v2p72.htm
Syntax:
DBCC checkprimaryfile ({'FileName'} [,opt={0|1|2|3}])
where
FileName is the primary database file to check.
opt=0 - check is the file a primary database file.
opt=1 - return name, size, maxsize, status and path of all files associated with the database.
opt=2 - return the database name, version and collation.
opt=3 - return name, status and path of all files associated with the database.
The following example checks the Test.MDF file and return name, size, maxsize, status and path
of all files associated with the database:
DBCC checkprimaryfile ('C:\SQL2005\Data\Test.MDF', 1)
November 25, 2009 at 7:11 am
Ok, I've tried both approaches (attach through the wizard, and running the recommended checkprimaryfile command.)
In both cases, I receive this error:
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'C:\SQL_Database\Databasename.mdf'.
Any ideas what the error means? I believe these files are to an old version of our production database and were never cleaned up properly when the current production database was created.
November 25, 2009 at 7:36 am
assuming you typed the path+filename correct:
perhaps it's still in use and some program has exclusive lock on it ?
November 26, 2009 at 1:12 am
Or else the account that the SQL Server service is running under doesn't have permissions to access that folder--quite possible if this is a really old database and the account has been changed since then.
November 27, 2009 at 4:37 am
Hi,
You will get that error if you use SQL 2k to check a SQL 2k5 file.
Can we know which version u r using..
Regards,
S.V.Nagaraj
Regards,
Raj
November 29, 2009 at 1:48 am
Run the following statement:
SELECT DB_NAME(database_id), *
FROM master.sys.master_files
...or for SQL Server 2000:
SELECT DB_NAME(dbid), *
FROM master.dbo.sysaltfiles
If the mysterious mdf and ldf files belong to a database that is attached to SQL Server then they will be listed in the resultset.
Chris
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply