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: Algae Team
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 ;

name , minimum , maximum , value_in_use,  
case is_dynamic 
when 1 then 'reconfigure with override' 
when 0 then 'restart'end as [APPLY]
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)
   insert into #configurations_final values 
   ('Select ''IMP NOTE : One of configuration option need sql server restart to get activated''')

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

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

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
     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

Select * from #final

Drop table #configurations
Drop table #configurations_final
Drop table #Geo
Drop table #final

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

  2. This comment has been removed by the author.

  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.


Please do not enter spam links

Meet US


More Services