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
--PASTE THE RESULTS HERE
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
Different SQL Server versions have some different configuration settings.