[CmdletBinding()]

Param(

[string]$SQLInstance = "localhost",

[string]$myuser,

[string]$mypass,

[string]$myDatabase,

[string]$mytable

)

{

Import-Module ".\SQLTranscriptase.psm1" -ErrorAction Stop

}

{

Throw('SQLTranscriptase.psm1 not found')

}

LoadSQLSMO

Set-StrictMode -Version latest;

[string]$BaseFolder = (Get-Item -Path ".\" -Verbose).FullName

Write-Host -f Yellow -b Black "30 - DataBase Objects"

Write-Output("Server: [{0}]" -f $SQLInstance)

if ($myTable.Length -gt 0 -and $myDatabase.Length -eq 0)

{

Write-Output ("Please specify the -MyDatabase parameter when using -myTable with {0}" -f $mytable)

exit

}

$SQLCMD1 = "select serverproperty('productversion') as 'Version'"

{

if ($mypass.Length -ge 1 -and $myuser.Length -ge 1)

{

Write-Output "Testing SQL Auth"

$myver = ConnectSQLAuth -SQLInstance $SQLInstance -Database "master" -SQLExec $SQLCMD1 -User $myuser -Password $mypass -ErrorAction Stop| select -ExpandProperty Version

$serverauth="sql"

}

else

{

Write-Output "Testing Windows Auth"

$myver = ConnectWinAuth -SQLInstance $SQLInstance -Database "master" -SQLExec $SQLCMD1 -ErrorAction Stop | select -ExpandProperty Version

$serverauth = "win"

}

if($myver -ne $null)

{

Write-Output ("SQL Version: {0}" -f $myver)

}

}

{

Write-Host -f red "$SQLInstance appears offline."

Set-Location $BaseFolder

exit

}

[int]$ver = GetSQLNumericalVersion $myver

function CopyObjectsToFiles($objects, $outDir) {

if (-not (Test-Path $outDir)) {

[System.IO.Directory]::CreateDirectory($outDir) | out-null

}

foreach ($o in $objects) {

if ($o -ne $null) {

$schemaPrefix = ""

try

{

if ($o.Schema -ne $null -and $o.Schema -ne "")

{

$schemaPrefix = $o.Schema + "."

}

}

catch

{

}

$fixedOName = $o.name.replace('\','_')

$scripter.Options.FileName = $outDir + $schemaPrefix + $fixedOName + ".sql"

try

{

$urn = new-object Microsoft.SQlserver.Management.sdk.sfc.urn($o.Urn);

$scripter.Script($urn)

}

catch

{

$msg = "Cannot script this element:"+$o

Write-Output $msg

}

}

}

}

if ($serverauth -eq "win")

{

$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $SQLInstance

$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($SQLInstance)

}

{

$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $SQLInstance

$srv.ConnectionContext.LoginSecure=$false

$srv.ConnectionContext.set_Login($myuser)

$srv.ConnectionContext.set_Password($mypass)

$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($srv)

}

Write-Output "Looking for Objects..."

$head = "<style type='text/css'>"

$RunTime = Get-date

$sqlCMD2 =

if ($serverauth -eq "win")

{

$sqlresults1 = ConnectWinAuth -SQLInstance $SQLInstance -Database "master" -SQLExec $sqlCMD2

}

{

$sqlresults1 = ConnectSQLAuth -SQLInstance $SQLInstance -Database "master" -SQLExec $sqlCMD2 -User $myuser -Password $mypass

}

$RunTime = Get-date

$FullFolderPath = "$BaseFolder\$SQLInstance\30 - DataBase Objects\"

if(!(test-path -path $FullFolderPath))

{

mkdir $FullFolderPath | Out-Null

}

$myoutputfile4 = $FullFolderPath+"\Database_Summary.html"

$myHtml1 = $sqlresults1 | select Database_Name,file_id, Name, FileName, Type, State, growth, growth_in_mb, DB_Size_in_MB | ConvertTo-Html -Fragment -as table -PreContent "<h1>Server: $SqlInstance</H1><H2>Database Summary</h2>"

Convertto-Html -head $head -Body "$myHtml1" -Title "Database Summary" -PostContent "<h3>Ran on : $RunTime</h3>" | Set-Content -Path $myoutputfile4

"Database Object Reconstruction Order" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"`n " | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"01) Database itself with Filegroups and Files" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"02) .NET Assemblies" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"03) Linked Servers" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"04) Logins" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"05) Sequences" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"06) Synonyms" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"07) Schemas" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"08) UDFs (Table-Valued and Scalar Functions)" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"09) User-Defined Table Types" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"10) Tables (with DRI Dependencies)" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"11) Views" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"12) Stored Procedures" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"13) Full-Text Catalogs" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"14) Table Triggers" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

"15) Database Triggers" | out-file "$FullFolderPath\Database_Reconstruction_Hints.txt" -Encoding ascii -Append

$scripter.Options.AllowSystemObjects = $false

$scripter.Options.AnsiFile = $true

$scripter.Options.ClusteredIndexes = $true

$scripter.Options.DriAllKeys = $true

$scripter.Options.DriForeignKeys = $true

$scripter.Options.DriChecks = $true

$scripter.Options.DriPrimaryKey = $true

$scripter.Options.DriUniqueKeys = $true

$scripter.Options.DriWithNoCheck = $true

$scripter.Options.DriAllConstraints = $true

$scripter.Options.DriIndexes = $true

$scripter.Options.DriClustered = $true

$scripter.Options.DriNonClustered = $true

$scripter.Options.EnforceScriptingOptions = $true

$scripter.Options.ExtendedProperties = $true

$scripter.Options.FullTextCatalogs = $true

$scripter.Options.FullTextIndexes = $true

$scripter.Options.FullTextStopLists = $true

$scripter.Options.IncludeFullTextCatalogRootPath= $true

$scripter.Options.IncludeHeaders = $true

$scripter.Options.IncludeDatabaseRoleMemberships= $true

$scripter.Options.IncludeDatabaseContext = $true;

$scripter.Options.Indexes = $true;

$scripter.Options.NoCommandTerminator = $false;

$scripter.Options.NonClusteredIndexes = $true

$scripter.Options.NoTablePartitioningSchemes = $false

$scripter.Options.Permissions = $true

$scripter.Options.SchemaQualify = $true

$scripter.Options.SchemaQualifyForeignKeysReferences = $true

$scripter.Options.ToFileOnly = $true

$scripter.Options.Triggers = $true

$scripter.Options.WithDependencies = $false # Leave OFF - creates issues - Jan 2016 we script out the DRI Table Order down below

$scripter.Options.XmlIndexes = $true

$scripter.Options.ScriptSchema = $true;

$scripter.Options.ScriptData = $false;

if ($myDatabase.Length -gt 0)

{

Write-Output ("Only scripting Database {0}"-f $myDatabase)

}

foreach($sqlDatabase in $srv.databases)

{

# If only one database secified on the command-line, ignore/skip all others

if ($myDatabase.Length -gt 0)

{

if ($sqlDatabase.Name -ne $myDatabase) {continue}

}

# Skip System Databases

if ($sqlDatabase.Name -in ('Model','MSDB','TempDB','SSISDB','distribution','Master')) {continue}

# Skip Offline Databases (SMO still enumerates them, but cant retrieve the objects)

if ($sqlDatabase.Status -ne 'Normal')

{

Write-Output ("Skipping Offline: {0}" -f $sqlDatabase.Name)

continue

}

# Script out objects for each DB

$db = $sqlDatabase

$fixedDBName = $db.name.replace('[','')

$fixedDBName = $fixedDBName.replace(']','')

$output_path = "$BaseFolder\$SQLInstance\30 - DataBase Objects\$fixedDBname"

# paths

$DB_Path = "$output_path\"

$table_path = "$output_path\Tables\"

$TableTriggers_path = "$output_path\TableTriggers\"

$views_path = "$output_path\Views\"

$storedProcs_path = "$output_path\StoredProcedures\"

$udfs_path = "$output_path\UserDefinedFunctions\"

$textCatalog_path = "$output_path\FullTextCatalogs\"

$udtts_path = "$output_path\UserDefinedTableTypes\"

$DBTriggers_path = "$output_path\DBTriggers\"

$Schemas_path = "$output_path\Schemas\"

$Filegroups_path = "$output_path\Filegroups\"

$Sequences_path = "$output_path\Sequences\"

$Synonyms_path = "$output_path\Synonyms\"

$DBScoped_Configs_path = "$output_path\DBScopedConfigs\"

$DBScoped_Creds_path = "$output_path\DBScopedCredentials\"

$QueryStore_path = "$output_path\QueryStore\"

$DBEDS_path = "$output_path\ExternalDataSources\"

$DBExtFF_path = "$output_path\ExternalFileFormats\"

$DBSecPol_path = "$output_path\SecurityPolicies\"

$XMLSC_path = "$output_path\XMLSchemaCollections\"

$DBColumnEncryptionKey_path = "$output_path\ColumnEncryptionKeys\"

$DBColumnMasterKey_path = "$output_path\ColumnMasterKeys\"

$DBRole_path = "$output_path\DBRoles\"

# --------------------------------

# Start Exporting Database Objects

# --------------------------------

# Main DB Export Folder

if(!(test-path -path $DB_Path))

{

mkdir $DB_Path | Out-Null

}

# Export Main Database Itself with Files and FileGroups

Write-Output "$fixedDBName - Database"

$MainDB = $db | Where-object { -not $_.IsSystemObject }

$myoutputfile = $DB_Path + $fixedDBName + ".sql"

$MainDB.Script() | out-file -FilePath $myoutputfile -encoding ascii -Force

# 2016+ Only Features

if ($myver -like "13.0*" -or $myver -like "14.0*")

{

# Database Scoped Credentials

Write-Output "$fixedDBName - Database Scoped Credentials"

$DBScopedCreds = $db.DatabaseScopedCredentials

CopyObjectsToFiles $DBScopedCreds $DBScoped_Creds_path

# Database Scoped Configs

Write-Output "$fixedDBName - Database Scoped Configs"

if(!(test-path -path $DBScoped_Configs_path))

{

mkdir $DBScoped_Configs_path | Out-Null

}

$myDBScopedOutfile =$DBScoped_Configs_path+"\DBScopedConfigs.sql"

"Use "+$DB.Name+"; `r`n" | Out-File -filepath $myDBScopedOutfile -encoding ascii -force

"ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = "+$Db.Maxdop | Out-File -filepath $myDBScopedOutfile -encoding ascii -Append

"ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = " +$db.MaxDopForSecondary | Out-File -filepath $myDBScopedOutfile -encoding ascii -Append

"ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = "+$db.LegacyCardinalityEstimation | Out-File -filepath $myDBScopedOutfile -encoding ascii -Append

"ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = "+$db.LegacyCardinalityEstimationForSecondary | Out-File -filepath $myDBScopedOutfile -encoding ascii -Append

"ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = "+$db.ParameterSniffing | Out-File -filepath $myDBScopedOutfile -encoding ascii -Append

"ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = "+$db.ParameterSniffingForSecondary | Out-File -filepath $myDBScopedOutfile -encoding ascii -Append

"ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = "+$db.QueryOptimizerHotfixes | Out-File -filepath $myDBScopedOutfile -encoding ascii -Append

"ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = "+$db.QueryOptimizerHotfixesForSecondary | Out-File -filepath $myDBScopedOutfile -encoding ascii -Append

# QueryStore Options

Write-Output "$fixedDBName - Query Store Options"

$myoutputfile = $QueryStore_path + "Query_Store.sql"

$QueryStore = $db.QueryStoreOptions

if ($QueryStore -ne $null)

{

if(!(test-path -path $QueryStore_path))

{

mkdir $QueryStore_path | Out-Null

}

$QueryStore.Script() | out-file -FilePath $myoutputfile -append -encoding ascii

}

# External Data Sources

Write-Output "$fixedDBName - External Data Sources"

$DB_EDS = $db.ExternalDataSources

CopyObjectsToFiles $DB_EDS $DBEDS_path

# External File Formats

Write-Output "$fixedDBName - External File Formats"

$DBExtFF = $db.ExternalFileFormats

CopyObjectsToFiles $DBExtFF $DBExtFF_path

# Security Policies

Write-Output "$fixedDBName - Database Security Policies"

$DBSecPol = $db.SecurityPolicies

CopyObjectsToFiles $DBSecPol $DBSecPol_path

# XMLSchema Collections

Write-Output "$fixedDBName - XML Schema Collections"

$DBXML_SC = $db.XmlSchemaCollections

CopyObjectsToFiles $DBXML_SC $XMLSC_path

# Always Encrypted Column Encryption Keys

Write-Output "$fixedDBName - Column Encryption Keys"

$DBAE_CEK = $db.ColumnEncryptionKeys

CopyObjectsToFiles $DBAE_CEK $DBColumnEncryptionKey_path

# Always Encrypted Column Master Keys

Write-Output "$fixedDBName - Column Master Keys"

$DBAE_CMK = $db.ColumnMasterKeys

CopyObjectsToFiles $DBAE_CMK $DBColumnMasterKey_path

}

# Create Settings Path

$DBSettingsPath = $output_path+"\Settings"

if(!(test-path -path $DBSettingsPath))

{

mkdir $DBSettingsPath | Out-Null

}

# Database Settings

Write-Output "$fixedDBName - Settings"

$mySettings = $db.Properties

$myoutputfile4 = $DBSettingsPath+"\Database_Settings.html"

$myHtml1 = $mySettings | sort-object Name | select Name, Value | ConvertTo-Html -Fragment -as table -PreContent "<h3>Database Settings for: $SQLInstance </h3>"

Convertto-Html -head $head -Body "$myHtml1" -Title "Database Settings" -PostContent "<h3>Ran on : $RunTime</h3>" | Set-Content -Path $myoutputfile4

# DBRoles

Write-Output "$fixedDBName - Roles"

# Create Output Sub Folder

if(!(test-path -path $DBRole_path))

{

mkdir $DBRole_path | Out-Null

}

# Get Role Properties and Members

foreach ($role in $db.Roles)

{

# Create Role File

$RoleFile = $DBRole_path+$role.name+".sql"

$RoleName = $role.Name

"Use "+$db.name | out-file $RoleFile -Encoding ascii -Append

"GO " | out-file $RoleFile -Encoding ascii -Append

" " | out-file $RoleFile -Encoding ascii -Append

"CREATE ROLE "+$RoleName | out-file $RoleFile -Encoding ascii -Append

" " | out-file $RoleFile -Encoding ascii -Append

foreach ($Roleproperty in $role.Properties)

{

"--- Role Property {0}={1}" -f $RoleProperty.name,$RoleProperty.value | out-file $RoleFile -Encoding ascii -Append

}

" " | out-file $RoleFile -Encoding ascii -Append

$RolePermissions = $role.EnumObjectPermissions()

foreach ($RolePermission in $RolePermissions)

{

"Role Permission {0}={1}" -f $RoleProperty.name,$RoleProperty.value | out-file $RoleFile -Encoding ascii -Append

}

"---Members:" | out-file $RoleFile -Encoding ascii -Append

$members = $role.EnumMembers()

foreach ($member in $members)

{

"EXEC sp_addrolemember '$RoleName','$member'" | out-file $RoleFile -Encoding ascii -Append

}

}

# Tables

Write-Output "$fixedDBName - Tables"

if ($mytable.Length -gt 0 -and $myDatabase -eq $sqldatabase.name)

{

Write-Output ("Only for table {0}"-f $mytable)

$tblSchema = ($mytable -split {$_ -eq "."})[0]

$tblTable = ($mytable -split {$_ -eq "."})[1]

$tbl = $db.Tables | Where-Object {$_.schema -eq $tblSchema -and $_.name -eq $tblTable}

}

else

# Get all Tables

{

$tbl = $db.Tables | Where-object { -not $_.IsSystemObject }

}

CopyObjectsToFiles $tbl $table_path

# Stored Procs

Write-Output "$fixedDBName - Stored Procs"

$storedProcs = $db.StoredProcedures | Where-object {-not $_.IsSystemObject }

CopyObjectsToFiles $storedProcs $storedProcs_path

# Views

Write-Output "$fixedDBName - Views"

$views = $db.Views | Where-object { -not $_.IsSystemObject }

CopyObjectsToFiles $views $views_path

# UDFs

Write-Output "$fixedDBName - UDFs"

$udfs = $db.UserDefinedFunctions | Where-object { -not $_.IsSystemObject }

CopyObjectsToFiles $udfs $udfs_path

# Table Types

Write-Output "$fixedDBName - Table Types"

$udtts = $db.UserDefinedTableTypes

CopyObjectsToFiles $udtts $udtts_path

# FullTextCats

Write-Output "$fixedDBName - FullTextCatalogs"

$catalog = $db.FullTextCatalogs

CopyObjectsToFiles $catalog $textCatalog_path

# DB Triggers

Write-Output "$fixedDBName - Database Triggers"

$DBTriggers = $db.Triggers

CopyObjectsToFiles $DBTriggers $DBTriggers_path

# Schemas

Write-Output "$fixedDBName - Schemas"

$Schemas = $db.Schemas | Where-object { -not $_.IsSystemObject }

CopyObjectsToFiles $Schemas $Schemas_path

# Sequences

Write-Output "$fixedDBName - Sequences"

$Sequences = $db.Sequences

CopyObjectsToFiles $Sequences $Sequences_path

# Synonyms

Write-Output "$fixedDBName - Synonyms"

$Synonyms = $db.Synonyms

CopyObjectsToFiles $Synonyms $Synonyms_path

# List Filegroups, Files and Path

Write-Output "$fixedDBName - FileGroups"

# Process FileGroups

$myoutputfile = $Filegroups_path+"Filegroups.txt"

if(!(test-path -path $Filegroups_path))

{

mkdir $Filegroups_path | Out-Null

}

# Create Output File

out-file -filepath $myoutputfile -encoding ascii -Force

Add-Content -path $myoutputfile -value "FileGroupName: DatabaseFileName: FilePath:"

# Prep SQL for Filegroups

$sqlCMD3 = "USE $db; SELECT `

# Run SQL

if ($serverauth -eq "win")

{

$sqlresults3 = ConnectWinAuth -SQLInstance $SQLInstance -Database "master" -SQLExec $sqlCMD3

}

else

{

$sqlresults3 = ConnectSQLAuth -SQLInstance $SQLInstance -Database "master" -SQLExec $sqlCMD3 -User $myuser -Password $mypass

}

# Script Out

foreach ($FG in $sqlresults3)

{

$myoutputstring = $FG.FileGroupName+$FG.DatabaseFileName+$FG.DatabaseFilePath

$myoutputstring | out-file -FilePath $myoutputfile -append -encoding ascii -width 500

}

# Table Creation in Dependency Order to maintain DRI

Write-Output "$fixedDBName - DRI Create Table Order"

# Create Database Summary Listing

$sqlCMD4 =

"

# Run SQL

if ($serverauth -eq "win")

{

$sqlresults4 = ConnectWinAuth -SQLInstance $SQLInstance -Database "master" -SQLExec $sqlCMD4

}

else

{

$sqlresults4 = ConnectSQLAuth -SQLInstance $SQLInstance -Database "master" -SQLExec $sqlCMD4 -User $myuser -Password $mypass

}

$RunTime = Get-date

$FullFolderPath = "$BaseFolder\$SQLInstance\30 - DataBase Objects\"

if(!(test-path -path $FullFolderPath))

{

mkdir $FullFolderPath | Out-Null

}

"Create Your Tables in this order to maintain Declarative Referential Integrity`r`n" | out-file "$output_path\DRI_Table_Creation_Order.txt" -Encoding ascii

$sqlresults4 | select Ordinal, TableName | out-file "$output_path\DRI_Table_Creation_Order.txt" -Encoding ascii -Append

# -------------------------------------------------------------------------

# Force GC

# March 10, 2015 - can we manually kick off a GC pass?

# Tested with Perfmon - GC has a delay in releasing memory as the allocations bubble up the generations, AKA, its slow

# Seems only ending the script/session releases memory

# Release Memory Benchmarking - Test setting a variable to $null vs using Remove-Variable

# -------------------------------------------------------------------------

$tbl = $null

$storedProcs = $null

$views = $null

$udfs = $null

$udtts = $null

$catalog = $null

$DBTriggers = $null

$TableTriggers = $null

$Schemas = $null

$Sequences = $null

$Synonyms = $null

[System.GC]::Collect()

}

set-location $BaseFolder