comparing physcial file name of the database

Question

Post reply

comparing physcial file name of the database

sps-554505

Default port

Points: 1463
More actions
December 1, 2008 at 5:12 am

#132530

hi,
Iam using below query to compare the physical location specified,but it is not showing the correct result and as the sys.files it is in diffrent
Is there any other method to compare
as per the structure all my data folders to be in E:drive
asnd log will be in f drive
i want to know which db is not following the correct structure
print('Filename')
exec('use['+@database_name+']
insert #file_check
select '''+@database_name+''',
(select filename from sys.sysfiles where fileid =1 and filename not like ''H:\MSSQL\'+@database_name+'\DATA\'+@database_name+'_Data.mdf''),
(select filename from sys.sysfiles where fileid =2 and filename not like ''I:\MSSQL\'+@database_name+'\LOG\'+@database_name+'_Log.ldf'')
from sys.sysfiles;')
Is there any other method to find out,this is working for some databases
some databases it is not
it is checking Sys.files table and the entire string is matching only it displays the correct result.
Is sys.master files can be used for this
Or please let me know the best method to find out the wrong structure.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 1

This may help you

declare @files table(dbname varchar(100), filename varchar(1000))

insert into @files

exec sp_msforeachdb 'select ''?'', filename from ?..sysfiles'

select dbname,min(left(filename,1)),max(left(filename,1)) from @files

group by dbname

having min(left(filename,1))<>'E' or max(left(filename,1))<>'F'

Note that sp_msforeachdb is undocumented which you can simulate using

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx

Madhivanan

Failing to plan is Planning to fail

Perry Whittle SSC Guru Points: 233825 More actions · Answer 2

to get a list of database and filenames use

select name, fileid, filename from sys.sysaltfiles

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

sps-554505 Default port Points: 1463 More actions · Answer 3

hi,

i need to display which database is not following the correct path,

as per the code i have written it should be that location,what iw ant is it should display if it is not matching

sps-554505 Default port Points: 1463 More actions · Answer 4

hi,

thanks,my code is getting me the result in coupel of instances,onely few instances ,sys.files filename location is stored as case sensitive,that is why it is giving error messge,

is there any method to modify my code and get the result,i need to check all db,if it is not macthcing with the format specified,

i should get the result of databases which is not correct as per the folder structure..

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

Try this:

DECLARE @database_name SYSNAME

DECLARE @iRowCount INT

DECLARE @t_TableNames_Temp TABLE

(table_name SYSNAME)

drop table #file_check

create table #file_check(DBName sysname,MDFFile sysname,LDFFile sysname)

INSERT @t_TableNames_Temp

SELECT name

FROM SYSDATABASES

WHERE name not in ('pubs','northwind','tempdb')

ORDER BY name

--Getting row count from table

SELECT @iRowCount = COUNT(*) FROM @t_TableNames_Temp

WHILE @iRowCount > 0

BEGIN

SELECT @database_name = table_name from @t_TableNames_Temp

exec('use['+@database_name+']

insert #file_check

select '''+@database_name+''',

(select rtrim(filename) from sysfiles where fileid =1 and filename not like ''H:\MSSQL\'+@database_name+'\DATA\'+@database_name+'_Data.mdf''),

(select rtrim(filename) from sysfiles where fileid =2 and filename not like ''I:\MSSQL\'+@database_name+'\LOG\'+@database_name+'_Log.ldf'')

from sysfiles;')

DELETE FROM @t_TableNames_Temp WHERE @database_name = table_name

SELECT @iRowCount = @iRowCount - 1

END

Select * from #file_check

SET NOCOUNT OFF

HTH

MJ

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Shine.mm If this copied correctly

(select filename from sys.sysfiles where fileid =1 and filename not like ''H:\MSSQL\'+@database_name+'\DATA\'+@database_name+'_Data.mdf''),

You have a EXTRA ' between like and and the drive letter H and after the _Data.mdf Modifiying your code to eliminate those additional characters

(select filename from sys.sysfiles where fileid =1

and filename not like ('H:\MSSQL\'+@database_name+'\DATA\'+@database_name+'_Data.mdf'))

does indeed generate a return.

sys.files filename location is stored as case sensitive,

I have tested the following 2 select statements and they show that the filename entry in sys.sysfiles is NOT case sensitive.

SELECT fileid, name

FROM sys.sysfiles

WHERE filename NOT LIKE ('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TIPSnTRICKS_Data.MDF')

SELECT fileid, name

FROM sys.sysfiles

WHERE filename NOT LIKE ('c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tipsntricks_data.mdf')

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]