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 ;
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
/*********************************/
Nice work. This will help larger migrations. Thank you for your work and sharing!
ReplyDeleteWelcome !!
DeleteThis comment has been removed by the author.
ReplyDeleteThanks 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.
ReplyDeleteThanks for sharing this script
ReplyDelete