posts - 230, comments - 423, trackbacks - 27

My Links



Follow billgraziano on Twitter

Article Categories


Post Categories


SQL Server

Update to SQL Server Configuration Scripting Utility

Last spring I released a utility to script SQL Server configuration information on CodePlex.  I’ve been making small changes in this application as my needs have changed.  The application is a .NET 2.0 console application. 

script-outputThis utility serves two needs for me.  First it helps with disaster recovery.  All server level objects (logins, jobs, linked servers, audits) are scripted to a single file per object type.  This enables the scripts to be easily run against a DR server.  If these are checked into source control you can view the history of the script and find out what changed and when.

The second goal is to capture what changed inside a database.  Objects inside a database (tables, stored procedures, views, etc.) are each scripted to their own file.  This makes it easier to track the changes to an object over time.  This does include permissions and role membership so you can capture security changes.  My assumption is that a database backup is the primary method of disaster recovery for databases so this utility is designed to capture changes to objects. 

You can find the full list of changes from the original on the Downloads page on CodePlex.

Print | posted on Sunday, March 11, 2012 7:49 PM |



# re: Update to SQL Server Configuration Scripting Utility


Great utility. We are going to incorporate this into our disaster recovery plan for all sql environments that we support.

However, in testing I did run into an unhandled exception with attempting to script out a stored proc that is encrypted. Can encrypted objects be skipped to avoid this? thanks.

3/11/2012 10:48 PM | 7gartner

# re: Update to SQL Server Configuration Scripting Utility

This is fixed in the source code on CodePlex but not yet rolled out to a release. I'll do that in the next few days. I haven't seen an encrypted stored procedure in ages!
3/11/2012 11:05 PM | graz

# re: Update to SQL Server Configuration Scripting Utility

Thanks for the quick fix. the proc encryption is so easily circumvented, but some 3rd party apps still use it.
3/11/2012 11:11 PM | 7gartner

# Getting the following error

Getting the following error:

4/6/2012 10:01:24 AM : Getting target server version through SMO
4/6/2012 10:01:24 AM : Getting Configuration.Properties
4/6/2012 10:01:24 AM : Error: The infamous sp_ProcessorUsage bug prevents writin
g properties.
4/6/2012 10:01:24 AM : Getting smoServer.Properties

Unhandled Exception: System.NotSupportedException: The given path's format is no
t supported.
at System.Security.Util.StringExpressionSet.CanonicalizePath(String path, Boo
lean needFullPath)
at System.Security.Util.StringExpressionSet.CreateListFromExpressions(String[
] str, Boolean needFullPath)
at System.Security.Permissions.FileIOPermission.AddPathList(FileIOPermissionA
ccess access, AccessControlActions control, String[] pathListOrig, Boolean check
ForDuplicates, Boolean needFullPath, Boolean copyPathList)
at System.Security.Permissions.FileIOPermission..ctor(FileIOPermissionAccess
access, String[] pathList, Boolean checkForDuplicates, Boolean needFullPath)
at System.IO.Directory.CreateDirectory(String path, DirectorySecurity directo
at ScriptSqlConfig.Program.WriteFile(StringCollection script, String fileName
, Boolean addGoStatements)
at ScriptSqlConfig.Program.WriteServerProperties(Server smoServer, String dir
at ScriptSqlConfig.Program.ScriptInstance(String server, String directory)
at ScriptSqlConfig.Program.Main(String[] args)
4/6/2012 11:11 AM | MELANIE
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET