thanks guys for the response...

my requirement has changed a bit...

i'm looking for something like this...

check if destination database exists, if it exists select physical location path ( data and log file )

else get the default instance path.

query to get the default instance path :

DECLARE @defaultDataLocation nvarchar(400)

DECLARE @defaultLogLocation nvarchar(400)

EXEC master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',

N'DefaultData',

@defaultDataLocation OUTPUT

EXEC master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',

N'DefaultLog',

@defaultLogLocation OUTPUT

SELECT @defaultDataLocation AS 'Default Data Location',@defaultLogLocation AS 'Default Log Location'

Please let me know the script...this is for database restore automation....so when we give the destination db parameters...it must check the db first....

thanks in advance