functions/Copy-SqlSysDbUserObjects.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
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"
}