Configuration Data Best Practice

After my recent Health and Risk Assessment visit from my Microsoft Premier Field Engineer, I learned some new and exciting  SQL Server best practices and I thought that I would share them with you in this blog space.  Our topic-o-choice today is all about backing up your configuration data.  I must admit that I have not given this topic a second thought in the past and luckily it has never bitten me before.  Microsoft defines configuration data as your sp_configure, SSIS packages and your replication topologies.  These items are not usually backed up in your nightly backups and may not be contained in your system databases.

Microsoft recommends scripting your SQL Server configuration data to a file using SELECT * FROM sys.configurations anytime you make changes to your server.  I would then include this in your disaster recovery walk-away bag or remote location destinations.  Personally, I would recommend documenting these settings if at all possible in a Word document explaining why you have chosen the settings and if they relate to your department’s best practices so that when you get hit by a bus, your replacement DBA will understand the intricacies of your choices and not chalk them up to the ‘this guy was an idiot who knew not the ways of the force.’

In addition, if you are using replication then you should script out all of the components for your disaster recovery.  It is also a good idea to back up your master and MSDB databases for the publisher, distributor and all of your subscribers.  I personally have never done that, but Microsoft recommends it…so do IT!  Enjoy!


About SQLGator

Microsoft Data Platform MVP, Florida Gator, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server Business Intelligence Consultant, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on February 10, 2012, in Database Administration Tasks and tagged , . Bookmark the permalink. Leave a comment.

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: