(DIR) Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
 (HTM) https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
 (DIR) Return to: Scripts
       *****************************************************
       #Post#: 47--------------------------------------------------
       T-SQL Script to Add SQL Server Startup Parameters
       By: srinivasma_exceldbp Date: April 15, 2014, 7:02 am
       ---------------------------------------------------------
       /*
       T-SQL Script to Add SQL Server Startup Parameters
       SQL Server Configuration Manager can be used to change startup
       parameters,
       establishing a remote desktop session to the relevant machine is
       required to launch it.
       This process can be time-consuming when dealing with large
       numbers of
       scattered SQL Server instances.
       In addition, when using SQL Server Configuration Manager,
       manually entries  of the startup parameters, may result in
       making mistakes
       1. This script works only on instances running SQL Server 2008
       R2 SP2 or
       later because the sys.dm_server_registry DMV isn't available in
       earlier
       versions of SQL Server.
       2. For the script to run successfully, the SQL Server service
       account should have full
       access to the registry or be a member of Local Administrators
       group.
       After successfully testing the script against a SQL Server
       instance,
       you can extend its functionality by using Central Management
       Server.
       @Parameters -- Start up parameter to be added
       @Argument_Number variable -- In the registry, each parameter's
       name starts
       with SQLArg followed by a number
       SQLArg0 identifies the master database data file.
       SQLArg1 identifies the Errorlog folder.
       SQLArg2 identifies the master database log file.
       Therefore, if no other startup parameter has been defined,
       the value of @Argument_Number would be 3,
       which will be used for the next startup parameter SQLArg3.
       */
       declare
       @Parameters
       @Argument_Number
       @Argument
       @Reg_Hive
       @CMD
       --==============================================================
       ===
       --** Parameter Cleanup
       /*
       checks to see whether the specified startup parameter (in this
       case, -T1222)
       already exists. If it exists, the script deletes it.
       This is done so that the script can be rerun. Plus,
       it prevents the code from breaking if it encounters this
       situation.
       */
       if exists
       (
       select * from sys.dm_server_registry
       where value_name like 'SQLArg%'
       and convert(varchar(max),value_data) = @Parameters
       )
       begin
       select
       @Argument
       @Reg_Hive
       1,len(registry_key))
       from sys.dm_server_registry
       where value_name like 'SQLArg%'
       and convert(varchar(max),value_data) = @Parameters
       
       set  @CMD = 'master..xp_regdeletevalue
       ''HKEY_LOCAL_MACHINE'',
       '''+@Reg_Hive+''',
       '''+@Argument+''''
       exec (@CMD)
       end
       --==============================================================
       ===
       --** Add Paramter
       /*
       The registry key that holds the startup parameters can be
       different
       on different instances (e.g., different on default and named
       instances).
       For this reason, the script uses the sys.dm_server_registry
       dynamic management view (DMV) to get the correct registry path
       to the startup parameters
       */
       select
       @Reg_Hive
       1,len(registry_key)) ,
       @Argument_Number
       max(convert(int,right(value_name,1))) + 1
       from sys.dm_server_registry
       where value_name like 'SQLArg%'
       group by substring(registry_key,len('HKLM\') +
       1,len(registry_key))
       set @Argument = 'SQLArg' + convert(varchar(1),@Argument_Number)
       select @Argument,@Reg_Hive
       set  @CMD ='master..xp_regwrite
       ''HKEY_LOCAL_MACHINE'',
       '''+@Reg_Hive+''',
       '''+@Argument+''',
       ''REG_SZ'',
       '''+@Parameters+''''
       exec  (@CMD)
       --==============================================================
       ===
       *****************************************************