Report Server Migration

Hi lherbert501​,

Parameters are the right long-term fix. Here are two more options for the reports that are already built, plus one that avoids the problem next time.

1. Published to the Power BI Service: you don't need to open the PBIX at all. The REST API can rewrite the server on every semantic model in a workspace in one loop:

Connect-PowerBIServiceAccount
$ws = "<workspace-id>"
$body = @{
  updateDetails = @(@{
    datasourceSelector = @{ datasourceType = "Sql"
      connectionDetails = @{ server = "OLD-SQL01"; database = "Sales" } }
    connectionDetails  = @{ server = "NEW-SQL01"; database = "Sales" }
  })
} | ConvertTo-Json -Depth 6

$models = (Invoke-PowerBIRestMethod -Method Get -Url "groups/$ws/datasets" | ConvertFrom-Json).value
foreach ($m in $models) {
  Invoke-PowerBIRestMethod -Method Post -Body $body `
    -Url "groups/$ws/datasets/$($m.id)/Default.UpdateDatasources"
}

This works for SQL, Analysis Services, OData and SharePoint sources. If a model already uses parameters, call Default.UpdateParameters instead. Re-map the gateway connection and refresh afterwards.

2. Files on disk (Desktop): save each report as a Power BI Project (.pbip). The connection then lives in plain-text TMDL files, so one script can do a find/replace across all of them:

Get-ChildItem -Path "C:\Reports" -Recurse -Filter *.tmdl |
  ForEach-Object {
    (Get-Content $_.FullName -Raw) -replace 'OLD-SQL01', 'NEW-SQL01' |
      Set-Content $_.FullName -NoNewline
  }

One caveat: if you publish to Power BI Report Server, check that your Report Server version of Desktop supports PBIP before relying on this.

3. Next time: ask your DBA for a DNS alias (CNAME) such as sql-sales.company.local and use that name in every report. A future server move is then a DNS change, and no PBIX needs to be touched.

So my overall approach would be:

published models -> REST UpdateDatasources / UpdateParameters loop
local PBIX files  -> save as PBIP -> scripted find/replace in TMDL
going forward     -> server/db parameters + DNS alias, not host names

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.