How to Script SQL Server Configuration Options

To query the configuration settings of a SQL Server, run the following query:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure
GO

sp_configure_output

You can see the configuration settings of a SQL Server also by querying the sys.configurations Catalog View:

SELECT * from sys.configurations
ORDER BY name
GO

sys_configurations_output

If you want to configure a new server with all the configuration settings same as in the old server, run the following code on the old server:

SELECT 'EXEC sp_configure ''' + name + ''', ' + CAST(value AS VARCHAR(100))
FROM sys.configurations
ORDER BY name

sys_configurations_script_output

Copy the results from the old server and paste it to the new query window in the new server, and run the code:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

--PASTE THE RESULTS HERE

EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

sp_configure_new_server

Different SQL Server versions have some different configuration settings.

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">