functions/Copy-DbaSysDbUserObject.ps1
|
function Copy-DbaSysDbUserObject { <# .SYNOPSIS Imports *all* user objects found in source SQL Server's master, msdb and model databases to the destination. .DESCRIPTION Imports *all* user objects found in source SQL Server's master, msdb and model databases to the destination. This is useful because many DBA's store backup/maintenance procs/tables/triggers/etc (among other things) in master or msdb. It is also useful for migrating objects within the model database. .PARAMETER Source Source SQL Server.You must have sysadmin access and server version must be SQL Server version 2000 or greater. .PARAMETER SourceSqlCredential Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted. To use: $scred = Get-Credential, this pass $scred object to the param. Windows Authentication will be used if DestinationSqlCredential is not specified. To connect as a different Windows user, run PowerShell as that user. .PARAMETER Destination Destination SQL Server. You must have sysadmin access and server version must be SQL Server version 2000 or greater. .PARAMETER DestinationSqlCredential Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted. To use: $dcred = Get-Credential, this pass this $dcred to the param. Windows Authentication will be used if DestinationSqlCredential is not specified. To connect as a different Windows user, run PowerShell as that user. .PARAMETER WhatIf Shows what would happen if the command were to run. No actions are actually performed. .PARAMETER Confirm Prompts you for confirmation before executing any changing operations within the command. .PARAMETER Silent Use this switch to disable any kind of verbose messages .NOTES Tags: Migration, SystemDatabase, UserObject Website: https://dbatools.io Copyright: (C) Chrissy LeMaire, clemaire@gmail.com License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0 .LINK https://dbatools.io/Copy-DbaSysDbUserObject .EXAMPLE Copy-DbaSysDbUserObject $sourceServer $destserve Copies user objects from source to destination #> [CmdletBinding(SupportsShouldProcess = $true)] param ( [Parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [DbaInstanceParameter]$Source, [PSCredential] $SourceSqlCredential, [Parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [DbaInstanceParameter]$Destination, [PSCredential] $DestinationSqlCredential, [switch]$Silent ) process { $sourceServer = Connect-SqlInstance -SqlInstance $Source -SqlCredential $SourceSqlCredential $destServer = Connect-SqlInstance -SqlInstance $Destination -SqlCredential $DestinationSqlCredential $source = $sourceServer.DomainInstanceName $destination = $destServer.DomainInstanceName if (!(Test-SqlSa -SqlInstance $sourceServer -SqlCredential $SourceSqlCredential)) { Stop-Function -Message "Not a sysadmin on $source. Quitting." return } if (!(Test-SqlSa -SqlInstance $destServer -SqlCredential $DestinationSqlCredential)) { Stop-Function -Message "Not a sysadmin on $destination. Quitting." return } $systemDbs = "master", "model", "msdb" foreach ($systemDb in $systemDbs) { $sysdb = $sourceServer.databases[$systemDb] $transfer = New-Object Microsoft.SqlServer.Management.Smo.Transfer $sysdb $transfer.CopyAllObjects = $false $transfer.CopyAllDatabaseTriggers = $true $transfer.CopyAllDefaults = $true $transfer.CopyAllRoles = $true $transfer.CopyAllRules = $true $transfer.CopyAllSchemas = $true $transfer.CopyAllSequences = $true $transfer.CopyAllSqlAssemblies = $true $transfer.CopyAllSynonyms = $true $transfer.CopyAllTables = $true $transfer.CopyAllViews = $true $transfer.CopyAllStoredProcedures = $true $transfer.CopyAllUserDefinedAggregates = $true $transfer.CopyAllUserDefinedDataTypes = $true $transfer.CopyAllUserDefinedTableTypes = $true $transfer.CopyAllUserDefinedTypes = $true $transfer.CopyAllUserDefinedFunctions = $true $transfer.CopyAllUsers = $true $transfer.PreserveDbo = $true $transfer.Options.AllowSystemObjects = $false $transfer.Options.ContinueScriptingOnError = $true $transfer.Options.IncludeDatabaseRoleMemberships = $true $transfer.Options.Indexes = $true $transfer.Options.Permissions = $true $transfer.Options.WithDependencies = $false Write-Message -Level Output -Message "Copying from $systemDb" try { $sqlQueries = $transfer.ScriptTransfer() foreach ($sql in $sqlQueries) { Write-Message -Level Debug -Message $sql if ($PSCmdlet.ShouldProcess($destServer, $sql)) { try { $destServer.Query($sql,$systemDb) } catch { # Don't care - long story having to do with duplicate stuff } } } } catch { # Don't care - long story having to do with duplicate stuff } } } end { Test-DbaDeprecation -DeprecatedOn "1.0.0" -Silent:$false -Alias Copy-SqlSysDbUserObjects } } |