Hi all, I am trying to query the default location of data and log files in a SQL server instance through an SP. can anyone help in providing the T-SQL for that. I used the following but it didnt work on my PC. is there any other way to do it. any help is appreciated declare @SmoDefaultFile nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT declare @SmoDefaultLog nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT SELECT ISNULL(@SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@SmoDefaultLog,N'') AS [DefaultLog]
Thanks nDinakae, this SP wouldnt help me, I need to get just the default data and log dir for an instance, not the files for a database. For instance if you set the default dir when installing SQL server instance to NB_filesData and NB_filesLogs, the tsql am trying to use will return these 2 directories only so I can use them in another Tsql.
Maybe using this Declare @SmoDefaultFile nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT Declare @SmoDefaultLog nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT if the variable is null the default configuration for the data and log file is empty.
Thanks Buveta. the query you provided succeeds but does not show output in Management studio. do I have to set anything before using it?
Sorry, my script is incomplete declare @SmoDefaultFile nvarchar(512)exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N 'DefaultData', @SmoDefaultFile OUTPUT declare @SmoDefaultLog nvarchar(512)exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N 'DefaultLog', @SmoDefaultLog OUTPUTselect @SmoDefaultFile as MdfDefaultLocation, @SmoDefaultLog as LogDefaultLocation
I tesst this script and if the default database location on SQL Server Instance Properties (TAB: Database Settings) is empty the result is null. but in my pc the result is OK.
Thanks Buveta, that helps a lot. it is working in my PC. Now I have to use .bat file to get the results from what you provided above and restore a DB from a known .bak file and restore to the above data/log directory using OSQL. am trying but couldnt get it right. thanks again