param (

[string] $_instanceName,

[string] $_databaseName,

[string] $_outputRoot

)

$currentPath = [System.IO.Directory]::GetCurrentDirectory();

[void][System.Reflection.Assembly]::LoadFile([System.IO.Path]::Combine($currentPath,"log4net.dll"));

$log4netLogManager = "PowerShell";

$log4netConfigName = "powershell.config";

$script:log=[log4net.LogManager]::GetLogger("PowerShell");

$private:fileInfo = new-object System.IO.FileInfo([System.IO.Path]::Combine($currentPath,$log4netConfigName));

[log4net.Config.XmlConfigurator]::Configure($fileInfo);

function ScriptSqlObject($_scripter,$_smoObject,$_targetPath,$_includeDrop,$_scriptToFileOnly,$_smoObjectTypetitle)

{

$log.Info(($_smoObjectTypetitle + ": " + $_smoObject))

$actualFullPathToScriptFile = [System.IO.Path]::Combine($_targetPath, $_smoObject.Name + ".sql")

if ( [System.IO.File]::Exists($actualFullPathToScriptFile) -eq $true )

{

[System.IO.File]::Delete($actualFullPathToScriptFile)

}

$_scripter.Options.FileName = $actualFullPathToScriptFile

$_scripter.Options.IncludeHeaders = $true

$_scripter.Options.ToFileOnly = $_scriptToFileOnly

if ( $_includeDrop -eq $true )

{

$_scripter.Options.AppendToFile = $true

$_scripter.Options.ScriptDrops = $scriptDrop

$_scripter.Options.IncludeIfNotExists = $true

# script the drop

$_scripter.Script($_smoObject.Urn)

}

$_scripter.Options.ScriptDrops = $false

$_scripter.Options.IncludeIfNotExists = $false

# script the create

$_scripter.Script($_smoObject.Urn)

trap

{

$log.Error("$error[0].exception");

}

}

# WARNING: this deletes the directory and all contents

function ZapDirectory ([string] $scriptPath)

{

del -force -recurse $scriptPath

mkdir $scriptPath

}

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$smoServer = new-object ( 'Microsoft.SqlServer.Management.Smo.Server') $_instanceName

$smoDatabase = $smoServer.Databases[$_databaseName]

$smoScripter = new-object ( 'Microsoft.SqlServer.Management.Smo.Scripter') ($smoServer)

#configure the generic SMO ScriptingOptions

#$smoScripter.Options.Permissions = $true

$smoScripter.Options.WithDependencies = $false

$smoScripter.Options.DriChecks = $false

$smoScripter.Options.DriWithNoCheck = $false;

$smoScripter.Options.SchemaQualify = $true;

$smoScripter.Options.NoExecuteAs = $false;

$smoScripter.Options.AllowSystemObjects = $false;

ZapDirectory ( $_outputRoot + "\FileGroups" )

ZapDirectory ( $_outputRoot + "\Users" )

ZapDirectory ( $_outputRoot + "\Data" )

ZapDirectory ( $_outputRoot + "\Tables" )

ZapDirectory ( $_outputRoot + "\StoredProcedures" )

ZapDirectory ( $_outputRoot + "\CheckConstraints" )

ZapDirectory ( $_outputRoot + "\Defaults" )

ZapDirectory ( $_outputRoot + "\Synonyms" )

ZapDirectory ( $_outputRoot + "\Functions" )

ZapDirectory ( $_outputRoot + "\Indexes" )

ZapDirectory ( $_outputRoot + "\PrimaryKeys" )

ZapDirectory ( $_outputRoot + "\ForeignKeys" )

ZapDirectory ( $_outputRoot + "\Schemas" )

ZapDirectory ( $_outputRoot + "\UserDefinedTypes" )

ZapDirectory ( $_outputRoot + "\UserDefinedDataTypes" )

ZapDirectory ( $_outputRoot + "\XmlSchemas" )

ZapDirectory ( $_outputRoot + "\Views" )

ZapDirectory ( $_outputRoot + "\Roles" )

ZapDirectory ( $_outputRoot + "\Users" )

ZapDirectory ( $_outputRoot + "\DmlTriggers" )

ZapDirectory ( $_outputRoot + "\DdlTriggers" )

$smoServer.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.StoredProcedure], "IsSystemObject" )

foreach ( $smoRole in $smoDatabase.Roles ) { if ( $smoRole.IsFixedRole -eq $false ) {ScriptSqlObject $smoScripter $smoRole "$_outputRoot\Roles" $true $true "ROLE"} }

foreach ( $smoDefault in $smoDatabase.Defaults ) { ScriptSqlObject $smoScripter $smoDefault "$_outputRoot\Defaults" $true $true "DEFAULT" }

foreach ( $smoSynonym in $smoDatabase.Synonyms ){ ScriptSqlObject $smoScripter $smoSynonym "$_outputRoot\Synonyms" $true $true "SYNONYM"}

$smoDatabase.PrefetchObjects([Microsoft.SqlServer.Management.Smo.View])

foreach ( $smoView in $smoDatabase.Views ) { if ( $smoView.IsSystemObject -eq $false ) { ScriptSqlObject $smoScripter $smoView "$_outputRoot\Views" $true $true "VIEW" } }

foreach ( $smoDdlTrigger in $smoDatabase.Triggers ) { ScriptSqlObject $smoScripter $smoDdlTrigger "$_outputRoot\DdlTriggers" $true $true "TRIGGER" }

$smoDatabase.PrefetchObjects([Microsoft.SqlServer.Management.Smo.UserDefinedFunction])

foreach ( $smoUDF in $smoDatabase.UserDefinedFunctions ) { if ( $smoUDF.IsSystemObject -eq $false ) { ScriptSqlObject $smoScripter $smoUDF "$_outputRoot\Functions" $true $true "FUNCTION" }}

foreach ( $smoUDDT in $smoDatabase.UserDefinedDataTypes ){ ScriptSqlObject $smoScripter $smoUDDT "$_outputRoot\UserDefinedDataTypes" $true $true "DATA TYPE"}

foreach ( $smoUDT in $smoDatabase.UserDefinedTypes ){ ScriptSqlObject $smoScripter $smoUDT "$_outputRoot\UserDefinedTypes" $true $true "TYPE"}

foreach ( $smoXmlSchema in $smoDatabase.XmlSchemaCollections ){ ScriptSqlObject $smoScripter $smoXmlSchema "$_outputRoot\XmlSchemas" $true $true "XML SCHEMA"}

foreach ( $smoUser in $smoDatabase.Users ){ ScriptSqlObject $smoScripter $smoUser "$_outputRoot\Users" $true $true "USER" }

$smoDatabase.PrefetchObjects([Microsoft.SqlServer.Management.Smo.Table])

foreach ( $smoTable in $smoDatabase.Tables )

{

ScriptSqlObject $smoScripter $smoTable "$_outputRoot\Tables" $true $true "TABLE"

$smoScripter.Options.DriForeignKeys = $true

foreach ( $smoForeignKey in $smoTable.ForeignKeys ) { ScriptSqlObject $smoScripter $smoForeignKey "$_outputRoot\ForeignKeys" $true $true "`tFORIEGN KEY" }

$smoScripter.Options.DriForeignKeys = $false

$smoScripter.Options.DriPrimaryKey = $true

foreach ( $smoIndex in $smoTable.Indexes )

{

if( $smoIndex.IndexKeyType -eq [Microsoft.SqlServer.Management.Smo.IndexKeyType]::DriPrimaryKey )

{

ScriptSqlObject $smoScripter $smoIndex "$_outputRoot\PrimaryKeys" $true $true "`tPRIMARY KEY"

}

else

{

ScriptSqlObject $smoScripter $smoIndex "$_outputRoot\Indexes" $true $true "`tINDEX"

}

}

$smoScripter.Options.DriPrimaryKey = $false

foreach ( $smoCheck in $smoTable.Checks )

{

$smoScripter.Options.DriChecks = $true

ScriptSqlObject $smoScripter $smoCheck "$_outputRoot\CheckConstraints" $true $true "`tCHECK CONSTRAINT"

$smoScripter.Options.DriChecks = $false

}

foreach ( $smoDmlTrigger in $smoTable.Triggers ) { ScriptSqlObject $smoScripter $smoDmlTrigger "$_outputRoot\DmlTriggers" $true $true "`tTRIGGER" }

foreach ( $smoColumn in $smoTable.Columns ) { if ( $smoColumn.DefaultConstraint -ne $null ) { ScriptSqlObject $smoScripter $smoColumn.DefaultConstraint "$_outputRoot\Defaults" $false $true "`tDEFAULT" } }

}

foreach ( $smoTable in $smoDatabase.Tables )

{

$log.Info("BULK DATA: $smoTable")

cd "$_outputRoot\Data"

bcp "$_databaseName.$smoTable" out $smoTable -S $_instanceName -T -w | out-null

}

$smoDatabase.PrefetchObjects([Microsoft.SqlServer.Management.Smo.StoredProcedure])

foreach ( $sp in $smoDatabase.StoredProcedures ){ if ( $sp.IsSystemObject -eq $false ) { ScriptSqlObject $smoScripter $sp "$_outputRoot\StoredProcedures" $true $true "STORED PROCEDURE" }}