Script to migrate Sp_Configure settings between SQL Servers

While setting up a new server, you will expect the server to have the same configuration settings similar to the old server.  In that situation you may use the following query to script out the configuration settings from the old server.

Run this on your old server

EXEC SP_CONFIGURE 'show advanced options' , 1;

GO

RECONFIGURE;

GO

DECLARE @spConfigVal TABLE

      ([name] VARCHAR(255)

      ,[minimum] INT

      ,[maximum] INT

      ,[config_value] INT

      ,[run_value] INT)

INSERT INTO @spConfigVal

EXEC SP_CONFIGURE

SELECT      'EXEC sp_configure ''' + name + ''', ' + CAST(config_value AS VARCHAR)

FROM  @spConfigVal

GO

EXEC SP_CONFIGURE 'show advanced options' , 0;

GO

RECONFIGURE;

GO

The output of the above script looks like this,

EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1

EXEC SP_CONFIGURE 'Agent XPs', 1

EXEC SP_CONFIGURE 'clr enabled', 1

EXEC SP_CONFIGURE 'cost threshold for parallelism', 5

EXEC SP_CONFIGURE 'cursor threshold', -1

EXEC SP_CONFIGURE 'Database Mail XPs', 1

EXEC SP_CONFIGURE 'default full-text language', 1033

EXEC SP_CONFIGURE 'default trace enabled', 1

EXEC SP_CONFIGURE 'fill factor (%)', 80

EXEC SP_CONFIGURE 'ft crawl bandwidth (max)', 100

EXEC SP_CONFIGURE 'ft notify bandwidth (max)', 100

EXEC SP_CONFIGURE 'max full-text crawl range', 4

EXEC SP_CONFIGURE 'max server memory (MB)', 10000

EXEC SP_CONFIGURE 'max text repl size (B)', 65536

EXEC SP_CONFIGURE 'min memory per query (KB)', 1024

EXEC SP_CONFIGURE 'min server memory (MB)', 10000

EXEC SP_CONFIGURE 'nested triggers', 1

EXEC SP_CONFIGURE 'network packet size (B)', 4096

EXEC SP_CONFIGURE 'PH timeout (s)', 60

EXEC SP_CONFIGURE 'query wait (s)', -1

EXEC SP_CONFIGURE 'remote access', 1

EXEC SP_CONFIGURE 'remote login timeout (s)', 20

EXEC SP_CONFIGURE 'remote query timeout (s)', 900

EXEC SP_CONFIGURE 'scan for startup procs', 1

EXEC SP_CONFIGURE 'server trigger recursion', 1

EXEC SP_CONFIGURE 'SMO and DMO XPs', 1

Copy the output and paste it in your new server SSMS window. Before executing this, change the show advanced options to 1 and execute the query. Finally change the advanced options back to 0 state. Run sp_configure to validate the settings between the servers

EXEC SP_CONFIGURE 'show advanced options' , 0;

RECONFIGURE;

GO;