functions/Copy-SqlSysDbUserObjects.ps1
|
Function Copy-SqlSysDbUserObjects { <# .SYNOPSIS 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. .EXAMPLE Copy-SqlSysDbUserObjects $sourceserver $destserver dbatools PowerShell module (https://dbatools.io, clemaire@gmail.com) Copyright (C) 2016 Chrissy LeMaire This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>. .LINK https://dbatools.io/Get-DetachedDbInfo #> [CmdletBinding(SupportsShouldProcess = $true)] param ( [Parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [object]$source, [Parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [object]$destination, [System.Management.Automation.PSCredential]$SourceSqlCredential, [System.Management.Automation.PSCredential]$DestinationSqlCredential ) $sourceserver = Connect-SqlServer -SqlServer $Source -SqlCredential $SourceSqlCredential $destserver = Connect-SqlServer -SqlServer $Destination -SqlCredential $DestinationSqlCredential $source = $sourceserver.DomainInstanceName $destination = $destserver.DomainInstanceName if (!(Test-SqlSa -SqlServer $sourceserver -SqlCredential $SourceSqlCredential)) { throw "Not a sysadmin on $source. Quitting." } if (!(Test-SqlSa -SqlServer $destserver -SqlCredential $DestinationSqlCredential)) { throw "Not a sysadmin on $destination. Quitting." } $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-Output "Copying from $systemdb" try { $sqlQueries = $transfer.scriptTransfer() foreach ($query in $sqlQueries) { if ($PSCmdlet.ShouldProcess($DestServer, $query)) { try { $destserver.Databases[$systemdb].ExecuteNonQuery($query) } catch { } # This usually occurs if there are existing objects in destination } } } catch { Write-Output "Exception caught." } } Write-Output "Migrating user objects in system databases finished" } |