Skip to content

Instantly share code, notes, and snippets.

@brazilnut2000
Last active August 29, 2015 14:21
Show Gist options
  • Save brazilnut2000/cc5f41d48d0a3ce5346b to your computer and use it in GitHub Desktop.
Save brazilnut2000/cc5f41d48d0a3ce5346b to your computer and use it in GitHub Desktop.
Powershell: Generate scripts for database
<#
SOURCE: https://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/
See script options and defaults in comment block at end of file
or look here for the official list:
https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions.aspx
Override any defaaults you want in the script below
#>
$Filepath='E:\MyScriptsDirectory' # local directory to save build-scripts to
$DataSource='MyServer' # server name and instance
$Database='MyDatabase'# the database to copy from
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
}
$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource
if ($s.Version -eq $null ){Throw "Can't find the instance $Datasource"}
$db= $s.Databases[$Database]
if ($db.name -ne $Database){Throw "Can't find the database '$Database' in $Datasource"};
$transfer = new-object ("$My.Transfer") $db
$CreationScriptOptions = new-object ("$My.ScriptingOptions")
$CreationScriptOptions.ExtendedProperties= $true # yes, we want these
$CreationScriptOptions.DRIAll= $true # and all the constraints
$CreationScriptOptions.Indexes= $true # Yup, these would be nice
$CreationScriptOptions.Triggers= $true # This should be included when scripting a database
$CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
$CreationScriptOptions.IncludeHeaders = $true; # of course
$CreationScriptOptions.ToFileOnly = $true #no need of string output as well
$CreationScriptOptions.IncludeIfNotExists = $true # not necessary but it means the script can be more versatile
$CreationScriptOptions.Filename = "$($FilePath)\$($Database)_Build.sql";
$transfer = new-object ("$My.Transfer") $s.Databases[$Database]
$transfer.options=$CreationScriptOptions # tell the transfer object of our preferences
$transfer.EnumScriptTransfer()
"All done"
<#
Encoding System.Text.UnicodeEncoding
DriWithNoCheck False
IncludeFullTextCatalogRootPath False
BatchSize 1
ScriptDrops False
TargetServerVersion Version110
TargetDatabaseEngineType Standalone
AnsiFile False
AppendToFile False
ToFileOnly False
SchemaQualify True
IncludeHeaders False
IncludeIfNotExists False
WithDependencies False
DriPrimaryKey False
DriForeignKeys False
DriUniqueKeys False
DriClustered False
DriNonClustered False
DriChecks False
DriDefaults False
Triggers False
Statistics False
ClusteredIndexes False
NonClusteredIndexes False
NoAssemblies False
PrimaryObject True
Default True
XmlIndexes False
FullTextCatalogs False
FullTextIndexes False
FullTextStopLists False
Indexes False
DriIndexes False
DriAllKeys False
DriAllConstraints False
DriAll False
Bindings False
NoFileGroup False
NoFileStream False
NoFileStreamColumn False
NoCollation False
ContinueScriptingOnError False
IncludeDatabaseRoleMemberships False
Permissions False
AllowSystemObjects True
NoIdentities False
ConvertUserDefinedDataTypesToBaseType False
TimestampToBinary False
AnsiPadding False
ExtendedProperties False
DdlHeaderOnly False
DdlBodyOnly False
NoViewColumns False
SchemaQualifyForeignKeysReferences False
AgentAlertJob False
AgentJobId True
AgentNotify False
LoginSid False
NoCommandTerminator False
NoIndexPartitioningSchemes False
NoTablePartitioningSchemes False
IncludeDatabaseContext False
NoXmlNamespaces False
DriIncludeSystemNames False
OptimizerData False
NoExecuteAs False
EnforceScriptingOptions False
NoMailProfileAccounts False
NoMailProfilePrincipals False
NoVardecimal True
ChangeTracking False
ScriptDataCompression True
ScriptSchema True
ScriptData False
ScriptBatchTerminator False
ScriptOwner False
#>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment