Besides moving over user data, what is the second biggest task that makes one resist moving over to a new workstation? Moving and reconfiguring the user settings in various tools and IDEs.

Whenever IT rebuilds a machine for me, one of the first things I set about doing is to customize the environment settings to my preference. Recently, I had to setup a new workstation for myself and accidentally discovered a hidden gem inside of the SQL Server Management Studio for SQL Server 2012 – The Import/Export Settings wizard.

Using the Import/Export Settings Wizard

The Import/Export settings wizard allows one to export and share the SSMS settings between computers including, but not limited to the following:

• General Settings
• Code snippet locations
• Object Explorer options
• Menu & command line customizations
• Options
• Debugging
• Environment
• Text Editor

To demonstrate the usage of this feature, I have customized some of the keyboard, text editor and object explorer settings.

• Text Editor -> Tab & Indentation options for all languages – Indentation set to ‘smart’ and set tabs to save as spaces
• Designer -> Table & DB Designers –> Set ‘Auto generate change scripts’ to ON
• Object Explorer options –> Value for select (n) rows command – changed from 1000 to 10

Exporting user settings

The series of screenshots below explain exporting of user settings to a file.

To launch the Import/Export settings window in the SSMS for SQL Server 2012, go to Tools –> Import and Export Settings

The Wizard provides 3 options: 1. Export selected environment settings 2. Import selected environment settings 3. Reset all settings

Clicking on “Next” allows the user to choose which settings should be exported. (The wizard even indicates which settings might expose intellectual property information.)

The next window allows the user to choose the path and file name to store the settings to.

Once ready, click “Finish” to export the settings to the file.

Resetting all settings

The screenshots below show how to reset user settings (used in this case to simulate a workstation movement).

Choosing to reset settings allows a user to save the current settings, just in case one doesn’t have a backup. Since I just exported the settings, we will choose to proceed without exporting them again.

Clicking “Next” allows the user to choose whether to reset the settings to either SQL Server 2008 R2 (default configuration) or VS2010. We will choose to continue with the default and click “Finish”. Once the settings are reset, we find that SSMS immediately returns to a SQL Server 2008 R2 default configuration (Object Explorer and Properties windows are open). We can also confirm that all customizations are reverted back. Sometimes, it may be required to restart SSMS for the changes to take effect.

Importing User Settings

The screenshots below show the procedure to import user settings on another workstation.

Before importing, the system allows the user to save the current configuration, if required. We will proceed without saving the configuration.

Using the “Browse” button, one can select the file exported earlier and then click “Next”

From the exported file, one can choose the settings to import while choosing not to import any settings affected intellectual property information. In this example, I have selected to import everything. Clicking on “Finish” completes the import and (after restarting SSMS in some cases) one can confirm that user settings have been imported successfully.

Concluding

Versions of SSMS prior to SQL Server 2012 did not come with the Import/Export Settings wizard. However, starting SQL Server 2012, it becomes very easy to move user customizations and settings between workstations in a matter of minutes.

I have already generated my configuration backup file via the Export wizard – have you?

Question for you:

• Do you know of any other such cool tools & utilities that come built-in with SQL Server which you had planned to explore? Do share the list with me – I will be happy to explore them for you and present a step-by-step guide for their usage

Until we meet next time,

Be courteous. Drive responsibly.