June 30, 2005 at 4:26 am
I have a database that records the physical location of a hi-res file associated with each record in it. I then have some hi-res files whose database record no longer exists. I know I can script a query to compare the two and report the orphaned physical files, but I don't know where to start. Suggestions?
June 30, 2005 at 6:20 am
Are all the files in the same directory?
June 30, 2005 at 6:52 am
Yes they are.
June 30, 2005 at 6:56 am
This is something I did for someone who wanted to find the unused dbs in its mssql/data directory.
You should be able to adapt this script yourslef :
--list the database data files found in the directory that are not listed in master :
Create table #temp
( output varchar (2000) null
)
GO
insert into #temp
exec master.dbo.xp_cmdshell 'DIR "E:\Program Files\Microsoft SQL Server\MSSQL\Data"'
Select dtDirectory.FileName FROM
(SELECT SUBSTRING(output, 40, LEN(OUTPUT) - 38) as FileName from #temp where RIGHT(output, 4) = '.MDF') dtDirectory
LEFT OUTER JOIN
(Select RIGHT(FileName, charindex('\', Reverse(FileName), 1) - 1) as FileName from master.dbo.SysDatabases) dtMaster
on dtDirectory.FileName = dtMaster.FileName
where dtMaster.FileName is null
DROP TABLE #temp
June 30, 2005 at 7:07 am
Thanks, I'll give it a shot.
July 1, 2005 at 2:15 am
Hi,
You could also try using the extended stored procedure xp_FileExists. See http://www.sqlservercentral.com/scripts/contributions/424.asp
Cheers,
Sameer
July 1, 2005 at 8:21 am
xp_fileexists will not be a set based solution... this could run 10 times slower than the xp_cmdshell version.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy