none
POWER SHELL ERROR RRS feed

  • Question

  • WE ARE PLANNING TO USE migration by below script but unable to achieve it , can u please let me know where is the issue with the script 

    SCRIPT ;

    function Start-DbaMigration {
        [CmdletBinding(DefaultParameterSetName = "Default", SupportsShouldProcess = $true)]
        param (
            [parameter(Position = 1, Mandatory)]
            [DbaInstanceParameter]$Source,
            [parameter(Position = 2, Mandatory)]
            [DbaInstanceParameter[]]$Destination,
            [parameter(Position = 3, Mandatory, ParameterSetName = "DbAttachDetach")]
            [switch]$DetachAttach,
            [parameter(Position = 4, ParameterSetName = "DbAttachDetach")]
            [switch]$Reattach,
            [parameter(Position = 5, Mandatory, ParameterSetName = "DbBackup")]
            [switch]$BackupRestore,
            [parameter(Position = 6, ParameterSetName = "DbBackup",
                HelpMessage = "Specify a valid network share in the format \\ME-NB312\share that can be accessed by your account and both Sql Server service accounts.")]
            [string]$NetworkShare,
            [parameter(Position = 7, ParameterSetName = "DbBackup")]
            [switch]$WithReplace,
            [parameter(Position = 8, ParameterSetName = "DbBackup")]
            [switch]$NoRecovery,
            [parameter(Position = 9, ParameterSetName = "DbBackup")]
            [parameter(Position = 10, ParameterSetName = "DbAttachDetach")]
            [switch]$SetSourceReadOnly,
            [Alias("ReuseFolderStructure")]
            [parameter(Position = 11, ParameterSetName = "DbBackup")]
            [parameter(Position = 12, ParameterSetName = "DbAttachDetach")]
            [switch]$ReuseSourceFolderStructure,
            [parameter(Position = 13, ParameterSetName = "DbBackup")]
            [parameter(Position = 14, ParameterSetName = "DbAttachDetach")]
            [switch]$IncludeSupportDbs,
            [parameter(Position = 15)]
            [PSCredential]$SourceSqlCredential,
            [parameter(Position = 16)]
            [PSCredential]$DestinationSqlCredential,
            [Alias("SkipDatabases")]
            [switch]$NoDatabases,
            [switch]$NoLogins,
            [Alias("SkipJobServer", "NoJobServer")]
            [switch]$NoAgentServer,
            [Alias("SkipCredentials")]
            [switch]$NoCredentials,
            [Alias("SkipLinkedServers")]
            [switch]$NoLinkedServers,
            [Alias("SkipSpConfigure")]
            [switch]$NoSpConfigure,
            [Alias("SkipCentralManagementServer")]
            [switch]$NoCentralManagementServer,
            [Alias("SkipDatabaseMail")]
            [switch]$NoDatabaseMail,
            [Alias("SkipSysDbUserObjects")]
            [switch]$NoSysDbUserObjects,
            [Alias("SkipSystemTriggers")]
            [switch]$NoSystemTriggers,
            [Alias("SkipBackupDevices")]
            [switch]$NoBackupDevices,
            [switch]$NoAudits,
            [switch]$NoEndpoints,
            [switch]$NoExtendedEvents,
            [switch]$NoPolicyManagement,
            [switch]$NoResourceGovernor,
            [switch]$NoServerAuditSpecifications,
            [switch]$NoCustomErrors,
            [switch]$NoDataCollector,
            [switch]$DisableJobsOnDestination,
            [switch]$DisableJobsOnSource,
            [switch]$NoSaRename,
            [switch]$UseLastBackups,
            [switch]$Continue,
            [switch]$Force,
            [switch]$EnableException
        )

        begin {
            $elapsed = [System.Diagnostics.Stopwatch]::StartNew()
            $started = Get-Date
            $sourceserver = Connect-SqlInstance -SqlInstance $Source -SqlCredential $SourceSqlCredential

            if ($BackupRestore -eq $false -and $DetachAttach -eq $false -and $NoDatabases -eq $false) {
                Stop-Function -Message "You must specify a database migration method (-BackupRestore or -DetachAttach) or -NoDatabases"
                return
            }
            if (-not $NoDatabases) {
                if (-not $DetachAttach -and !$BackupRestore) {
                    Stop-Function -Message "You must specify a migration method using -BackupRestore or -DetachAttach."
                    return
                }
            }
            if ($BackupRestore -and (-not $NetworkShare -and -not $UseLastBackups)) {
                Stop-Function -Message "When using -BackupRestore, you must specify -NetworkShare or -UseLastBackups"
                return
            }
            if ($NetworkShare -and $UseLastBackups) {
                Stop-Function -Message "-NetworkShare cannot be used with -UseLastBackups because the backup path is determined by the paths in the last backups"
                return
            }
            if ($DetachAttach -and -not $Reattach -and $Destination.Count -gt 1) {
                Stop-Function -Message "When using -DetachAttach with multiple servers, you must specify -Reattach to reattach database at source"
                return
            }
            if ($Continue -and -not $UseLastBackups) {
                Stop-Function -Message "-Continue cannot be used without -UseLastBackups"
                return
            }
        }

        process {
            if (Test-FunctionInterrupt) { return }

            # testing twice for whatif reasons
            if ($BackupRestore -and (-not $NetworkShare -and -not $UseLastBackups)) {
                Stop-Function -Message "When using -BackupRestore, you must specify -NetworkShare or -UseLastBackups"
                return
            }
            if ($NetworkShare -and $UseLastBackups) {
                Stop-Function -Message "-NetworkShare cannot be used with -UseLastBackups because the backup path is determined by the paths in the last backups"
                return
            }
            if ($DetachAttach -and -not $Reattach -and $Destination.Count -gt 1) {
                Stop-Function -Message "When using -DetachAttach with multiple servers, you must specify -Reattach to reattach database at source"
                return
            }
            if (-not $NoSpConfigure) {
                Write-Message -Level Verbose -Message "Migrating SQL Server Configuration"
                Copy-DbaSpConfigure -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential
            }

            if (-not $NoCustomErrors) {
                Write-Message -Level Verbose -Message "Migrating custom errors (user defined messages)"
                Copy-DbaCustomError -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoCredentials) {
                Write-Message -Level Verbose -Message "Migrating SQL credentials"
                Copy-DbaCredential -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoDatabaseMail) {
                Write-Message -Level Verbose -Message "Migrating database mail"
                Copy-DbaDbMail -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoCentralManagementServer) {
                Write-Message -Level Verbose -Message "Migrating Central Management Server"
                Copy-DbaCentralManagementServer -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoBackupDevices) {
                Write-Message -Level Verbose -Message "Migrating Backup Devices"
                Copy-DbaBackupDevice -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoLinkedServers) {
                Write-Message -Level Verbose -Message "Migrating linked servers"
                Copy-DbaLinkedServer -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoSystemTriggers) {
                Write-Message -Level Verbose -Message "Migrating System Triggers"
                Copy-DbaServerTrigger -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoDatabases) {
                # Do it
                Write-Message -Level Verbose -Message "Migrating databases"
                if ($BackupRestore) {
                    if ($UseLastBackups) {
                        Copy-DbaDatabase -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -AllDatabases -SetSourceReadOnly:$SetSourceReadOnly -ReuseSourceFolderStructure:$ReuseSourceFolderStructure -BackupRestore -Force:$Force -NoRecovery:$NoRecovery -WithReplace:$WithReplace -IncludeSupportDbs:$IncludeSupportDbs -UseLastBackups:$UseLastBackups -Continue:$Continue
                    }
                    else {
                        Copy-DbaDatabase -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -AllDatabases -SetSourceReadOnly:$SetSourceReadOnly -ReuseSourceFolderStructure:$ReuseSourceFolderStructure -BackupRestore -NetworkShare $NetworkShare -Force:$Force -NoRecovery:$NoRecovery -WithReplace:$WithReplace -IncludeSupportDbs:$IncludeSupportDbs
                    }
                }
                else {
                    Copy-DbaDatabase -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -AllDatabases -SetSourceReadOnly:$SetSourceReadOnly -ReuseSourceFolderStructure:$ReuseSourceFolderStructure -DetachAttach:$DetachAttach -Reattach:$Reattach -Force:$Force -IncludeSupportDbs:$IncludeSupportDbs
                }
            }

            if (-not $NoLogins) {
                Write-Message -Level Verbose -Message "Migrating logins"
                $syncit = $NoSaRename -eq $false
                Copy-DbaLogin -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force -SyncSaName:$syncit
            }

            if (-not $NoLogins -and -not $NoDatabases -and -not $NoRecovery) {
                Write-Message -Level Verbose -Message "Updating database owners to match newly migrated logins"
                foreach ($dest in $Destination) {
                    $null = Update-SqlDbOwner -Source $sourceserver -Destination $dest -DestinationSqlCredential $DestinationSqlCredential
                }
            }

            if (-not $NoDataCollector) {
                Write-Message -Level Verbose -Message "Migrating Data Collector collection sets"
                Copy-DbaDataCollector -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoAudits) {
                Write-Message -Level Verbose -Message "Migrating Audits"
                Copy-DbaServerAudit -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoServerAuditSpecifications) {
                Write-Message -Level Verbose -Message "Migrating Server Audit Specifications"
                Copy-DbaServerAuditSpecification -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoEndpoints) {
                Write-Message -Level Verbose -Message "Migrating Endpoints"
                Copy-DbaEndpoint -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoPolicyManagement) {
                Write-Message -Level Verbose -Message "Migrating Policy Management"
                Copy-DbaPolicyManagement -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoResourceGovernor) {
                Write-Message -Level Verbose -Message "Migrating Resource Governor"
                Copy-DbaResourceGovernor -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoSysDbUserObjects) {
                Write-Message -Level Verbose -Message "Migrating user objects in system databases (this can take a second)."
                If ($Pscmdlet.ShouldProcess($destination, "Copying user objects.")) {
                    Copy-DbaSysDbUserObject -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$force
                }
            }

            if (-not $NoExtendedEvents) {
                Write-Message -Level Verbose -Message "Migrating Extended Events"
                Copy-DbaExtendedEvent -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -Force:$Force
            }

            if (-not $NoAgentServer) {
                Write-Message -Level Verbose -Message "Migrating job server"
                Copy-DbaAgentServer -Source $sourceserver -Destination $Destination -DestinationSqlCredential $DestinationSqlCredential -DisableJobsOnDestination:$DisableJobsOnDestination -DisableJobsOnSource:$DisableJobsOnSource -Force:$Force
            }
        }

        end {
            if (Test-FunctionInterrupt) { return }
            $totaltime = ($elapsed.Elapsed.toString().Split(".")[0])
            Write-Message -Level Verbose -Message "SQL Server migration complete."
            Write-Message -Level Verbose -Message "Migration started: $started"
            Write-Message -Level Verbose -Message "Migration completed: $(Get-Date)"
            Write-Message -Level Verbose -Message "Total Elapsed time: $totaltime"
        }
    }

    PS C:\> Start-DbaMigration -Verbose -Source SQL01 -Destination SQL02 -SourceSqlCredential $scred -ReuseSourceFolderStructure -DestinationSqlCredential $cred -Force -NetworkShare

    • Moved by Bill_Stewart Thursday, December 20, 2018 8:42 PM Unanswerable drive-by question
    Sunday, October 7, 2018 9:19 PM

All replies