-- ALZDBA
-- Add default Data- and LogFile locations
--
USE [master]
GO

Declare @DataPath NVarchar(2000)
Declare @LogPath NVarchar(2000)
	EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DataPath OUTPUT
	EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @LogPath OUTPUT
print @DataPath
print @LogPath

-- activate to install
If 0 = 1
begin
	-- Adjust !!!
	Select @DataPath = N'D:\MSSQL.1\MSSQL\Data'	-- Aanpassen !
		 , @LogPath = N'D:\MSSQL.1\MSSQL\Data'	-- Aanpassen !


	/* SQL2005 always use xp_instance_regwrite */
		EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, @DataPath
		EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, @LogPath

end

/*
-- find default backup directory (elaborate to find datadirectory
-- activate cmdshell if disabled
EXEC sys.sp_configure N'show advanced options', N'1'
RECONFIGURE WITH OVERRIDE	

declare @CmdShellSettingBefore table (cfgname varchar(128), minimum varchar(128), maximum varchar(128), config_value varchar(128), runvalue varchar(128))
insert into @CmdShellSettingBefore
	EXEC sys.sp_configure N'xp_cmdshell'
if exists(select * from @CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')
begin
	EXEC sys.sp_configure N'xp_cmdshell', N'1'
	RECONFIGURE WITH OVERRIDE	
end

-- Get Default-path 
declare @DefaultBackupDirectory nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @DefaultBackupDirectory OUTPUT

Print 'Default BackupDirectory for instance [' + @@servername + '] = ' + @DefaultBackupDirectory
-- disable cmdshell if it was originaly disabled
if exists(select * from @CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')
begin
	EXEC sys.sp_configure N'xp_cmdshell', N'0'
	RECONFIGURE WITH OVERRIDE	
end

*/

     Jul 13  

Sometimes this is me