The Script Schemas module scripts out database objects (including tables, indexes, triggers, stored procedures, views, and functions) and SQL Agent jobs. By default, Minion Enterprise scripts to the Collector.DBObjectScripts table for all “Gold” level servers on a daily basis, “Silver” every three days, and “Bronze” every four weeks.

Note that you can also choose to script to files, default: C:\MinionByMidnightDBA\DBScriptBackups\.  For more information, see the section titled “Change the Default Location for Scripted Objects”. 

This is a massively useful feature, as a safeguard against schema level mistakes (such as an incorrectly modified view, or a mistakenly dropped index, or almost any schema object that is modified or dropped).
 

Table

Collector.DBObjectScripts

Holds the object scripts generated from each server, in addition to scripted jobs from the Script Jobs module.

NOTE: This table is shared between the Script Jobs module and the Script Schemas module.
 
Column Type Description
ID bigint Primary key row identifier.
ExecutionDateTime datetime The execution date and time, common to the run of a collection for a single instance.
InstanceID bigint The instance ID of the instance in question, as defined in the table dbo.Servers.
DBName varchar Database name.
SchemaName varchar Schema name.
ObjectName varchar Object name.
ParentName varchar The object name of the current object’s parent. For example, a row for an index will have ParentName = the name of the owning table.
ObjectType varchar The object type. Note that the Script Schemas module scripts out all objects except jobs, so you should filter by ObjectType <> ’JOB’ for this module.
 
Examples:
FUNCTION-Inline
INDEX-ClusteredIndex
INDEX-NonClusteredIndex
SP
TABLE
Contents varchar The object CREATE or ALTER statement.
 

Views

Collector.DBObjectScriptsCurrent

Provides the most recent collections of scripted jobs and objects.

NOTE: This view is shared between the Script Jobs module and the Script Jobs module.

Each “Current” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:
  • ServerName
  • ServiceLevel
  • Version
  • Edition
  • Descr

Collector.DBObjectScriptsPrevious

Provides the next-to-most recent collections of jobs and objects.

NOTE: This view is shared between the Script Jobs module and the Script Jobs module.

Each “Current” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:
  • ServerName
  • ServiceLevel
  • Version
  • Edition
  • Descr

Jobs

CollectorDBScript-GOLD

Calls the executable DBScript.exe for all servers with ServiceLevel = ‘Gold’, to script out database objects.
 

CollectorDBScript-SILVER

Calls the executable DBScript.exe for all servers with ServiceLevel = ‘Silver’, to script out database objects.
 

CollectorDBScript-BRONZE

Calls the executable DBScript.exe for all servers with ServiceLevel = ‘Bronze’, to script out database objects.
 

Executables

DBScript.exe

Performs the script object collection for each managed server. Logs the results to the Collector.DBObjectScripts table, or to flat files, or both.

Input parameters:
  • $Query – This parameter refers to the service level that the collector should operate on. Examples: Gold, Silver, Bronze. For more information on this topic, see the article “Executables and Service Levels”.
  • $SaveToSQL – This parameter determines whether to save the scripts to the SQL Server table Collector.DBObjectScripts. Valid values: 1, 0. By default, $SaveToSQL = 1.
  • $SaveToFile – This parameter determines whether to save the job scripts to files. Valid values: 1, 0. By default, $SaveToFile is 0. If $SaveToFile is enabled, jobs are scripted out to files in C:\MinionByMidnightDBA\ServerName\DBName\ExecutionDateTime\Tables (or, …\Indexes, …\Triggers, etc. as appropriate).
Example execution:
C:\MinionByMidnightDBA\Collector\DBScript.exe Gold 1 0
 
 

Script to files, tables, or both

You have the option of scripting your objects to files, to a table, or both!  To change the Script Schemas behavior for “Gold” level servers, edit the script call in the “CollectorDBScript-GOLD” job; the first parameter is always the service level; the second parameter is the Save to SQL bit; and the third parameter is the Save to File bit.
  • To script object schemas to files, edit the script call in the job “CollectorDBScript-GOLD”: C:\MinionByMidnightDBA\Collector\DBScript.exe Gold 0 1
  • To script object schemas to tables, edit the script call in the job “CollectorDBScript-GOLD”: C:\MinionByMidnightDBA\Collector\DBScript.exe Gold 1 0
  • To script object schemas to files and tables, edit the script call in the job “CollectorDBScript-GOLD”: C:\MinionByMidnightDBA\Collector\DBScript.exe Gold 1 1

IMPORTANT: Be sure to use spaces between your parameters in the script call; do not use commas or other delimiters!

Of course, the same principles apply to the Silver and Bronze level jobs.

To disable all object scripting, disable the “CollectorDBScript-%” jobs.
 

Make an exception for a single server

Minion Enterprise gives you the ability to make an exception for any server, for any collection, using the dbo.CollectionExceptionsServer table.  If Svr1 is a Gold level server, but you don’t want any object scripts generated for it, insert a row to dbo.CollectionExceptionsServer (using the InstanceID for Svr1 from dbo.Servers:

INSERT INTO dbo.CollectionExceptionsServer ( InstanceID, CollectionName )
VALUES  ( 99 , 'DBScript'  );
 
Now, InstanceID 99 (which is Svr1) will no longer take part in the Script Schemas collection!

Change the Default Location for Scripted Objects

To move the default location of scripted objects, configure the “$DBScriptBasePath” variable in the “ServerConn.ps1” configuration file, located at C:\MinionByMidnightDBA\Includes.  For example, you might change the default code: 

$DBScriptBasePath = "C:\MinionByMidnightDBA\DBScriptBackups" 

To use a different drive: 

$DBScriptBasePath = "D:\MinionByMidnightDBA\DBScriptBackups" 

IMPORTANT: Be sure that your target directory exists.
To disable this feature entirely, disable the “CollectorDBScriptGOLD” job.


https://minionware.desk.com/customer/portal/articles/2378893-script-schemas-module
http://www.MinionWare.net