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

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

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

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

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

Different SQL Server versions have some different configuration settings.