Script SQL Server Configuration
Script SQL Server configuration information in a format suitable for disaster recovery purposes or checking into a source control system.
Server level objects (linked servers, audits, etc.) are scripted in one file per object type. This makes DR easier and reviewing history possible. Database level objects (tables, functions, etc.) are scripted in one file per object. This makes reviewing object history easy and DR possible. The database backup is the primary DR mechanism for databases.
The following instance level objects are scripted:
- Logins These are scripted with the proper SID and optionally the hashed password.
- Jobs
- Linked servers
- Audits
- Alerts
- Credentials
- Proxy Accounts
- Database Mail
- Event Notifications
- User-Defined Endpoints
The following instance level objects and current values are written to a file:
- Properties
- Options
User objects in master, model and msdb are scripted.
The following user database objects can be scripted:
- Tables
- Stored procedures
- User-defined data types
- Views
- Triggers
- Table types
- User-defined functions
- Users, Database roles and application roles
- Service Broker Objects
Requirements
This requires a SQL Server installation from SQL Server 2008, 2008 R2, 2012, or 2014.
You can control which version of SMO is loaded by updating the ScriptSqlConfig.exe.config
file.
By default it loads the SQL Server 2012 version of SMO. To change this, uncomment the <runtime>
section of the ScriptSqlConfig.exe.config
file.
You'll see a number of entries that look like this:
<dependentAssembly>
<assemblyIdentity name="Microsoft.SqlServer.Smo"
publicKeyToken="89845dcd8080cc91"
culture="neutral" />
Assembly versions can be redirected in application,
publisher policy, or machine configuration files.
<bindingRedirect oldVersion="11.0.0.0" newVersion="10.0.0.0" />
</dependentAssembly>
Simply update the newVersion
attribute with the version of SMO you'd like to target.
A comment in the ScriptSqlConfig.exe.config
files includes instructions and the version numbers.
Notes
Earlier versions of the source code and a 2008 version can be found on CodePlex at https://scriptsqlconfig.codeplex.com/.
This shouldn't be needed now since this version can load the 2008 SMO libraries if needed.