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!