I wrote a small utility to script some key SQL Server configuration information. I had two goals for this utility:
- Assist with disaster recovery preparation
- Identify configuration changes
I’ve released the application as open source through CodePlex. You can download it from CodePlex at the Script SQL Server Configuration project page.
The application is a .NET 2.0 console application that uses SMO. It writes its output to a directory that you specify.
Disaster Planning
ScriptSqlConfig generates scripts for logins, jobs and linked servers. It writes the properties and configuration from the instance to text files. The scripts are designed so they can be run against a DR server in the case of a disaster. The properties and configuration will need to be manually compared.
- Each job is scripted to its own file.
- Each linked server is scripted to its own file. The linked servers don’t include the password if you use a SQL Server account to connect to the linked server. You’ll need to store those somewhere secure.
- All the logins are scripted to a single file. This file includes windows logins, SQL Server logins and any server role membership.
- The SQL Server logins are scripted with the correct SID and hashed passwords. This means that when you create the login it will automatically match up to the users in the database and have the correct password. This is the only script that I programmatically generate rather than using SMO.
- The SQL Server configuration and properties are scripted to text files. These will need to be manually reviewed in the event of a disaster. Or you could DIFF them with the configuration on the new server.
Configuration Changes
These scripts and files are all designed to be checked into a version control system. The scripts themselves don’t include any date specific information. In my environments I run this every night and check in the changes. I call the application once for each server and script each server to its own directory.
The process will delete any existing files before writing new ones. This solved the problem I had where the scripts for deleted jobs and linked servers would continue to show up. To see any changes I just need to query the version control system to show many any changes to the files.
Database Scripting
Utilities that script database objects are plentiful. CodePlex has at least a dozen of them including one I wrote years ago. The code is so easy to write it’s hard not to include that functionality. This functionality wasn’t high on my list because it’s included in a database backup. Unless you specify the /nodb option, the utility will script out many user database objects. It will script one object per file. It will script tables, stored procedures, user-defined data types, views, triggers, table types and user-defined functions. I know there are more I need to add but haven’t gotten around it yet. If there’s something you need, please log an issue and get it added.
Since it scripts one object per file these really aren’t appropriate to recreate an empty database. They are really good for checking into source control every night and then seeing what changed. I know everyone tells me all their database objects are in source control but a little extra insurance never hurts.
Conclusion
I hope this utility will help a few of you out there. My goal is to have it script all server objects that aren’t contained in user databases. This should help with configuration changes and especially disaster recovery.