I have a problem with my application that works on my dev SQL Server 2008 (Developer Edition), but not on a production SQL Server 2008. Is there an easy way to export the settings from the production server and compare it to my server configuration?

What I found out is that I can export Facets in SQL Server Management Studio to XML files and compare them in a diff tool.

Is there any other/better way to export and compare settings of two SQL Server instances?

  • "I have a problem with my application" -- care to elaborate? There are many places to look for a problem...
    – Jon Seigel
    Jun 26, 2012 at 13:30
  • I was asking about how to compare SQL Server configuration, not about my app problem. It does not matter in this context. Jun 26, 2012 at 21:24

5 Answers 5


You really just have to get creative. As we all know, there are many places that settings are stored, depending on what exactly you're looking to compare. For instance, to compare instance-wide configuration settings, you can simple do an EXCEPT query (you may have to create a linked server, or export/import the data depending on how you want to approach that):

select *
from [YourProdInstance].master.sys.configurations


select *
from [YourDevInstance].master.sys.configurations

Use this same type of methodology for all other configuration settings that you will want to compare.

  1. Find the root of the config data
  2. Get both instances' config data in a common place
  3. Write a query that will compare the data (whether it is XML, or not)
  • Check out the script at sqlserverperformance.idera.com/uncategorized/… to quickly spot non-default settings for SQL Server 2008.
    – MicSim
    Jun 26, 2012 at 14:11
  • @MicSim I don't think the OP is looking for non-default settings. Just config settings that differ between two instances (which may also be non-default). Jun 26, 2012 at 14:22
  • I know, that's why just a comment as additional info to your answer. Usually you got 80% default settings. So this way you're left to compare only the remaining 20%. (+1 btw)
    – MicSim
    Jun 26, 2012 at 14:38
  • @MicSim Absolutely, I completely agree. Thanks! Jun 26, 2012 at 14:46

You can use custom policies or predefined ones (so called best practices) and adjust them according to your developer SQL Server instance. Then, as explained in the SQL Server Policy Based Management – evaluating policies on multiple SQL Server instances online article, evaluate them against (even multiple) production instances


I wrote an open source utility that will script out common server configurations and save them to various files. It includes everything in sys.configurations as well as security settings, server properties, credentials, databases and a whole lot more.

The utility is SQL Server Configurations on CodePlex.

If you script out the two servers and use a diff utility on the results you should have a pretty good list of what's different between the two servers. I mostly use it for DR purposes but it should work well in this case.


I helped to create a free tool that compares instance and database settings and also works for Azure SQL Database.


  • Unfortunately this doesn't have a MARGE feature to be able to apply the differences from one server to another.
    – Fandango68
    Jan 7, 2020 at 1:15
  • 1
    Not yet. The tool is really for highlighting issues, but merging and rolling out changes is in the pipeline. Sep 1, 2020 at 9:20

1) If you are looking for an ongoing way to compare environments and 2) if you are open to a commercial solution, you may want to take a look at https://www.orcaconfig.com/compare-configurations

Orca captures the configurations of applications, middleware, databases (incl SQL Server, and operating systems. And tt automatically compares the configuration against a known standard (such as a gold master revision or gold master server) as well as your compliance standards. Yep, I work there.

Otherwise, the options listed by other commenters are likely your best choice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.