Copy SQL Server Client Component Configurations

I was recently given a new workstation.  Hooray! 

Since that happened, a number of the tools I use have to be re-installed and re-configured.  Booooo!

The most critical of them all, of course, the SQL Server 2005/2008 Client Components.  If you work in an environment with multiple Development, UAT,  and Production SQL Servers this can be a drag.  Fortunately, with SQL 2005, or with SQL 2008, some of it’s not too painful.  Being able to migrate from one installation to the next, quickly, makes it easier to share settings and tools with your fellow DBAs or to copy them to machines you commonly work from to give you a similar landscape. There are several items that I like being able to bring forward from one to the next:

re-configureimage11SQL Server Groups and Registrations

On the source machine, within SSMS, click “Ctrl+Alt+G” to view the Registered Servers. 

 Right click on “Database Engine” (Or any preferred sub-group) and select “Export…”


re-configureimage24Select the “Database Engine” to export all registered Servers or select an individual Server Group.  Provide the location and file name the export should be saved to and click Ok.

Warning:  If these are production server registrations, either leave the “Do not include user name and passwords…” option checked or make sure the export is kept in a secure location.

Copy the export file to your target machine(s) and “import” the Server Registrations in the same manner.

NOTE:  The Server Registration exports ARE NOT interchangeable between SQL 2005 and SQL 2008.

Template Explorer Customizations – I have used Template Explorer quite extensively to keep my most commonly used scripts at the ready.  I also maintain a collection of “troubleshooting” scripts that can be referenced in an emergency.  It helps with remaining calm, when others may be excited, knowing where to find the scripts you need under pressure.

If you have created Templates, and saved them, their default location will be:

C:\Documents and Settings\YourUserName\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql

Simply copy the folders and templates from that location on your source machine to your target.

Recommendation:  Create one or two folders and then creating sub-folders within those to keep your personal scripts organized and separate from those provided.  It makes migrating or sharing them that much easier and reduces the likelyhood that any will be missed.  It’s cleaner too if you’re moving from SQL 2005 to SQL 2008.  I also have a “junk” folder (Like a drawer) for any scripts I’m working on that I’m not worried about keeping yet.

Solution Explorer – Ditto with Projects you may have created:

If you have created Projects, and saved them, their default location will be:

C:\Documents and Settings\YourUsername\My Documents\SQL Server Management Studio\Projects

Simply copy the folders and Solutions from that location on your source machine to your target.

SQL Server Configuration Alias– Just like the SQL Server Groups and Registrations, having to recreate the 15 different Alias’ that are defined within SQL Server Configuration Manager can be tedious.  Good news here is that they can be quickly exported and imported.  A bonus is that Configuration Alias’ ARE interchangeable between SQL 2005 and SQL 2008.

Warning:  The steps below involve making changes with your registry.  Proceed with caution…at your own risk 🙂

Launch “Regedt32” and browse to, and highlight, the following location: 




From the menu select “File->Export”.  Provide the path and file name on your source machine.  Copy that file to your target machine and double-click on it.  It will ask you if you want to import it…say yes!

If you have other suggestions or features you migrate please let me know.


4 responses to “Copy SQL Server Client Component Configurations

  1. I would love it if you would share your most commonly used scripts and your collection of “troubleshooting” scripts with us.

    If you have already done so, could you post a link back to where.


  2. Keith,

    The commonly used scripts are fundamental queries and statements that are internal to our environment.

    In my humble beginnings, the “troubleshooting” scripts aren’t originals. As I continue to publish posts, I’ll share them and give credit where appropriate.

    In the meantime, check out SQLServerPedia. It has been a great help to me.


  3. Pingback: Another 3 SQL Server bloggers at SQLServerPedia | Brent Ozar - SQL Server DBA

  4. I can not believe this. Very exciting doing it, it take just a few second you are done. Thank you!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s