Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
You need dbatools PowerShell module. This script will copy all missing 'View', 'TableValuedFunction', 'DefaultConstraint', 'StoredProcedure', 'Rule', 'InlineTableValuedFunction', 'ScalarFunction', 'Database Trigger', 'Table/View Triggers' from a source database to a destination one within same instance. No error handling if compilation fails (Ex…
#Vars
$srcInstance = "localhost"
$destInstance = "localhost"
$srcDatabase = "db1"
$destDatabase = "db2"
# Will create SMO instance object that we will use to run script out the definition of the objects
$sqlSrcInst = Connect-DbaInstance $srcInstance
# Will create SMO instance object that we will use later to run the scripts on the destination database
$sqlDestInst = Connect-DbaInstance $destInstance
# Get existing modules on source database
$dbSrcModules = Get-DbaModule -SqlInstance $sqlSrcInst -Database $srcDatabase -ExcludeSystemObjects -Type 'View', 'TableValuedFunction', 'DefaultConstraint', 'StoredProcedure', 'Rule', 'InlineTableValuedFunction', 'ScalarFunction'
# Get existing modules on destination database
$dbDestModules = Get-DbaModule -SqlInstance $sqlDestInst -Database $destDatabase -ExcludeSystemObjects -Type 'View', 'TableValuedFunction', 'DefaultConstraint', 'StoredProcedure', 'Rule', 'InlineTableValuedFunction', 'ScalarFunction'
# Create smo object for the source database to get SMOs objects for each type of module
$smoSrcDb = $sqlSrcInst.Databases[$srcDatabase]
# Create smo object for the destination database to get SMOs objects for each type of module
$smoDestDb = $sqlSrcInst.Databases[$destDatabase]
# Get database triggers from source
$dbSrcModules += $smoSrcDb.Triggers | Where-Object IsSystemObject -ne $true | Select-Object Name, @{l = 'SchemaName'; e = { $null } } , @{l = 'Type'; e = { 'SQL_TRIGGER' } }, @{l = 'Database'; e = { $srcDatabase } }
# Get database triggers from destination
$dbDestModules += $smoDestDb.Triggers | Where-Object IsSystemObject -ne $true | Select-Object Name, @{l = 'SchemaName'; e = { $null } } , @{l = 'Type'; e = { 'SQL_TRIGGER' } }, @{l = 'Database'; e = { $destDatabase } }
# Get table triggers from source
$dbSrcModules += $smoSrcDb.Tables.Triggers | Where-Object IsSystemObject -ne $true | Select-Object Name, @{l = 'SchemaName'; e = { $null } } , @{l = 'Type'; e = { 'SQL_TABLE_TRIGGER' } }, @{l = 'Database'; e = { $srcDatabase } }
# Get table triggers from destination
$dbDestModules += $smoDestDb.Tables.Triggers | Where-Object IsSystemObject -ne $true | Select-Object Name, @{l = 'SchemaName'; e = { $null } } , @{l = 'Type'; e = { 'SQL_TABLE_TRIGGER' } }, @{l = 'Database'; e = { $destDatabase } }
# Filter only missing objects on destination
$missingObjects = $dbSrcModules | Where-Object {$dbDestModules.Name -notcontains $_.Name}
# List of missing objects
#$missingObjects | Select name, type
$results = @()
foreach ($moduleObject in $missingObjects) {
$smObject = switch ($moduleObject.Type) {
"TABLE" { $smoSrcDb.Tables.Item($moduleObject.Name, $moduleObject.SchemaName) }
"VIEW" { $smoSrcDb.Views.Item($moduleObject.Name, $moduleObject.SchemaName) }
"SQL_STORED_PROCEDURE" { $smoSrcDb.StoredProcedures.Item($moduleObject.Name, $moduleObject.SchemaName) }
"RULE" { $smoSrcDb.Rules.Item($moduleObject.Name, $moduleObject.SchemaName) }
"SQL_TRIGGER" { $smoSrcDb.Triggers.Item($moduleObject.Name) }
"SQL_TABLE_TRIGGER" { Get-DbaDbObjectTrigger -SqlInstance $srcInstance -Database $srcDatabase | Where-Object Name -eq $moduleObject.Name }
"SQL_TABLE_VALUED_FUNCTION" { $smoSrcDb.UserDefinedFunctions.Item($moduleObject.Name, $moduleObject.SchemaName) }
"SQL_INLINE_TABLE_VALUED_FUNCTION" { $smoSrcDb.UserDefinedFunctions.Item($moduleObject.Name, $moduleObject.SchemaName) }
"SQL_SCALAR_FUNCTION" { $smoSrcDb.UserDefinedFunctions.Item($moduleObject.Name, $moduleObject.SchemaName) }
}
$results += $smObject
}
# Generate the scripting options
$ScriptingOptionsObject = New-DbaScriptingOption
$ScriptingOptionsObject.IncludeDatabaseContext = $false
$ScriptingOptionsObject.ScriptBatchTerminator = $true
$ScriptingOptionsObject.AnsiFile = $true
$export = @{
NoPrefix = $NoPrefix
ScriptingOptions = $ScriptingOptionsObject
}
# Generated T-SQL Script all objects
foreach($r in $results) {
try {
Write-Host "Exporting $($r.Type) : $($r.Name)"
$exportedScript = $r | Export-DbaScript @export -PassThru
# Invoke-DbaQuery is not suitable for this (due GO's - will run each batch)
# Workaround - Run the changed script using the ExecuteNonQuery method (we don't need to remove the 'GO's
$sqlDestInst.Databases["$destDatabase"].ExecuteNonQuery($exportedScript)
Write-Host "Executing $($r.Type) : $($r.Name)"
}
catch {
Write-Host "Failed executing $($r.Type) : $($r.Name)" -ForegroundColor Red
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment