Create and Import Sitecore 9.3 Database using DacPac and Powershell

Create and Import Sitecore Database.ps1

#Get the DAC assemly Location
$DacFxLocation = "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin"

#Get the Dac File Location
$DacFileLocation = "C:\Sitecore9.3\DBs"


#Get SQL Server Name
$sqlserver = Read-Host 'What is the SQL servername?' #'nlgsusnpscmi1'
#Get SQL Server Username
$sqlUsername = Read-Host 'What is the SQL Username?'
#Get SQL Server Password
$sqlPassword = Read-Host 'What is the SQL User Password?' -AsSecureString
#Get Database Prefix
$prefix = Read-Host 'What is the database prefix?' 


# PowerShell function 
# DeploySql
Function DeploySql {
[cmdletbinding()]
param(
    [string]$dacpac = $( throw "Missing: parameter dacpac"),
    [string]$dbname = $( throw "Missing: parameter dbname") )

# End of Parameters
 Process {

        if ($DacFxLocation -eq "" -or $DacFxLocation -eq $null)
        {
            Write-Host "Error: DacFxLocation folder location is empty"
            return;
        }
        
        Write-Host "Deploying the DB with the following settings"
        Write-Host "sqlserver:   $sqlserver"
        Write-Host "dacpac: $dacpac"
        Write-Host "dbname: $dbname"

       	Add-Type -Path "$DacFxLocation\Microsoft.SqlServer.Dac.dll"
           
        $DacService = New-Object Microsoft.SqlServer.Dac.DacServices "Data Source=$sqlserver;User ID=$sqlUsername;Password=$sqlPassword"
        $ProgressEvent = Register-ObjectEvent -InputObject $DacService -EventName 'Message' -Action { Out-Host -InputObject $Event.SourceArgs[1].Message.Message }

        Write-Host "Start Loading the DacPac"
        # Load dacpac from file & deploy to database named pubsnew
        $DacPackage = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpac)

        Write-Host "Deploy the DacPac"
        $DacService.Deploy($DacPackage, $dbname, $false)
            
        UnRegister-Event $ProgressEvent.Name
   } 
}

DeploySql -dacpac "$DacFileLocation\Sitecore.Core.dacpac" -dbname $prefix + '.local.sc_Core'
DeploySql -dacpac "$DacFileLocation\Sitecore.Exm.master.dacpac" -dbname $prefix + '.local.sc_EXM.Master'
DeploySql -dacpac "$DacFileLocation\Sitecore.Experienceforms.dacpac" -dbname $prefix + '.local.sc_ExperienceForms'
DeploySql -dacpac "$DacFileLocation\Sitecore.Marketingautomation.dacpac" -dbname $prefix + '.local.sc_MarketingAutomation'
DeploySql -dacpac "$DacFileLocation\Sitecore.Master.dacpac" -dbname $prefix + '.local.sc_Master'
DeploySql -dacpac "$DacFileLocation\Sitecore.Messaging.dacpac" -dbname $prefix + '.local.sc_Messaging'
DeploySql -dacpac "$DacFileLocation\Sitecore.Processing.pools.dacpac" -dbname $prefix + '.local.sc_Processing.Pools'
DeploySql -dacpac "$DacFileLocation\Sitecore.Processing.tasks.dacpac" -dbname $prefix + '.local.sc_Processing.Tasks'
DeploySql -dacpac "$DacFileLocation\Sitecore.Processing.Engine.Storage.dacpac" -dbname $prefix + '.local.sc_ProcessingEngineStorage'
DeploySql -dacpac "$DacFileLocation\Sitecore.Processing.Engine.Tasks.dacpac" -dbname $prefix + '.local.sc_ProcessingEngineTasks'
DeploySql -dacpac "$DacFileLocation\Sitecore.Referencedata.dacpac" -dbname $prefix + '.local.sc_ReferenceData'
DeploySql -dacpac "$DacFileLocation\Sitecore.Reporting.dacpac" -dbname $prefix + '.local.sc_Reporting'
DeploySql -dacpac "$DacFileLocation\Sitecore.Web.dacpac" -dbname $prefix + '.local.sc_Web'
DeploySql -dacpac "$DacFileLocation\Sitecore.Xdb.Collection.Shard0.dacpac" -dbname $prefix + '.local.sc_Xdb.Collection.Shard0'
DeploySql -dacpac "$DacFileLocation\Sitecore.Xdb.Collection.Shard1.dacpac" -dbname $prefix + '.local.sc_Xdb.Collection.Shard1'
DeploySql -dacpac "$DacFileLocation\Sitecore.Xdb.Collection.Database.Sql.dacpac" -dbname $prefix + '.local.sc_Xdb.Collection.ShardMapManager'
DeploySql -dacpac "$DacFileLocation\Sitecore.Sessions.dacpac" -dbname $prefix + '.local.sc_Session'