powerbi-sql.ps1

Login-PowerBI

$workspaces = Get-PowerBIWorkspace

$sourceServer = server01.database.windows.net 
$sourceDatabase = database01.database.windows.net 
                        
$destinationServer = server02.database.windows.net 
$destinationDatabase = database02.database.windows.net 


foreach ($workspace in $workspaces) {
    $workspace
    $datasets = Get-PowerBIDataset -WorkspaceId $workspace.Id

    foreach ($dataset in $datasets | Where-Object{$_.IsRefreshable -eq $true}) {

        Write-Host "Start updating dataset " $dataset.Name " in workspace " $workspace.Name

        Get-PowerBIDatasource -DatasetId $dataset.Id

        # $urlTakeOver = "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.Id)/datasets/$($dataset.Id)/Default.TakeOver"
        # Invoke-PowerBIRestMethod -Url $urlTakeOver -Method Get

        $url = "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.Id)/datasets/$($dataset.Id)/Default.UpdateDatasources"
        $body = '{
            "updateDetails": [
                {
                    "datasourceSelector": {
                        "datasourceType": "Sql",
                        "connectionDetails": {
                            "server": "$sourceServer",
                            "database": "$sourceDatabase"
                        }
                    },
                    "connectionDetails": {
                        "server": "$destinationServer",
                        "database": "$destinationDatabase"
                    }
                }
            ]
        }'

        Invoke-PowerBIRestMethod -Url $url -Method Post -Body $body

        Write-Host "Finished updating dataset " $dataset.Name " in workspace " $workspace.Name
    }
}