Generate Script To Migrate sp_configure Settings In SQL Server




During Migration or DR activity we need to move system configuration settings from one server to other. Every time either we do this manually or we use some script which require manual formatting.

So to avoid same you can directly use below query on source server  and apply on target server

Quick Download to avoid formatting issue

Please collect output in Text format instead of Grid
/*********************************
Created By: Saurabh Sinha
Create Date: Sep 18 2014
Functionality: To extract sp_configure settings modified on server in executabe format
Dependency: You must generate output in text view instead grid
SSMS--> Menu --> Query--> Results To--> Results to Text
*********************************/
set nocount on ;

Select  
name , minimum , maximum , value_in_use,  
case is_dynamic 
when 1 then 'reconfigure with override' 
when 0 then 'restart'end as [APPLY]
--,[description]
into #configurations

from sys.configurations 

select 'sp_configure  ' +''''+ rtrim(name)+ '''' + ' , ' + convert(Varchar, value_in_use) + '  ' +CHAR(13) +CHAR(10)+'go' +CHAR(13) +CHAR(10)+
case [APPLY] when 'restart' then '-- Please restart sql services' +CHAR(13) +CHAR(10)+'go' 
  Else  'reconfigure with override  '+CHAR(13)  +CHAR(10)+'go'  end as sql_text
  into #configurations_final from #configurations 
  If exists  (Select 1  from sys.configurations where value_in_use <>0 
   and  is_dynamic = 0)
   Begin
   insert into #configurations_final values 
   ('Select ''IMP NOTE : One of configuration option need sql server restart to get activated''')
   End

   SELECT 
     RowNum = ROW_NUMBER() OVER(ORDER BY sql_text desc )
     ,*
INTO #Geo
FROM #configurations_final
--select * from #Geo

DECLARE @MaxRownum INT
SET @MaxRownum = (SELECT MAX(RowNum) FROM #Geo)

DECLARE @Iter INT
Set @Iter =1
DECLARE @sql_text varchar (2000)
Set @sql_text = ''
SET @Iter = (SELECT MIN(RowNum) FROM #Geo)
Create table #final (sql_text varchar (2000))

WHILE @Iter <= @MaxRownum
BEGIN
     insert into #final  
     select sql_text 
     FROM #Geo
     WHERE RowNum = @Iter

--Select @sql_text = sql_text
--from #configurations_final where 
--print @sql_text

SET @Iter = @Iter + 1
END

Select * from #final

Drop table #configurations
Drop table #configurations_final
Drop table #Geo
Drop table #final
/*********************************/

7 comments:

  1. Nice work. This will help larger migrations. Thank you for your work and sharing!

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Thanks for sharing, however, this script scripts out the maximum value and not the config_value or run_value. I modified in my environment to use the Run_Value if it's the same with the config_value. If not, it alerts.

    ReplyDelete
    Replies
    1. Thanks Brittany for reporting issue, I have correct that.

      Delete