CODEWITHSUNDEEP
sql-serverpowershellazure-sql-database
Owain Esau
Owain Esau

Reputation: 1922

Uploading local database from SQL Server on-prem to Azure

At the moment I am doing the following:

  1. Check if SQL package is installed and get the directory
  2. Use SQL Package to create a bacpac of the database
  3. Import the database using New-AzSQLDatabaseImport
    ## Check SQL Server package installation
    $Global:folder = Get-ChildItem "C:\Program Files\Microsoft SQL Server\" | Where { Is-Numeric $_.name -ne $null}
    $Global:folder = ($folder | Sort-Object  @{e={$_.name -as [int]}} -Descending)[0].name

    if (!(Test-Path "C:\Program Files\Microsoft SQL Server\$folder\DAC\bin\sqlpackage.exe") ) {
        Set-Output "[!] SqlPackage is not installed" -colour red -time;
        Set-Output "[!] Install SQLPackage and re-run script" -colour red -time;
        break;
    }
    else {
        Set-Output '[+] Sqlpackage is installed' -colour green -time;
    }

    ##### Generate bacpac file for upload
    Try {
        $BacPac_Backup = '& "C:\Program Files\Microsoft SQL Server\ENTER-NUM\DAC\bin\sqlpackage.exe" /Action:Export /ssn:. /sdn:"ENTER-DB" /tf:"c:\ENTER-DB.bacpac"'
        $BacPac_Backup = $BacPac_Backup -replace('ENTER-NUM', $folder)
        $BacPac_Backup = $BacPac_Backup -replace('ENTER-DB' , $TRIS5_DatabaseName)

        Invoke-Expression $BacPac_Backup > $null 2>&1
        Set-Output "[+] Bacpac file created" -colour green -time;
    }
    Catch {
        Set-Output "[+] Unable to create bacpac file" -colour red -time;
        break;
    }

What I am wanting to know is if there is a way I can skip #2 and just upload the database to Azure directly from SQL Server using PowerShell. I know you can do this within SSMS, but I cannot find a way to automate this inside PowerShell.

Upvotes: 1

Views: 246

Answers (1)

Jim Xu
Jim Xu

Reputation: 23111

According to my research, we have no way to skip #2 (create a bacpac file) and SQLPackage is the only way to create bacpac file with the command line. For more details, please refer to https://learn.microsoft.com/en-us/sql/relational-databases/data-tier-applications/deploy-a-database-by-using-a-dac?view=sql-server-ver15#using-a-net-framework-application.

Besides, we also can Azure Database Migration Service to migrate on-premises SQL to Azure. Regarding how to implement it with PowerShell, please refer to the document.

Upvotes: 1

Related Questions