Export sql server database powershell

I did this powershell to restore a database from SQL Server 2022 to a lower version, I had to drop the users, because it got error:

Error SQL74502: The element [ user01] cannot be deployed. This element contains state that cannot be recreated in the target database.

$PathVariables=$env:Path
$PathVariables

IF (-not $PathVariables.Contains( "C:\Program Files\Microsoft SQL Server\160\DAC\bin"))
{
write-host "SQLPackage.exe path is not found, Update the environment variable"
$env:Path = $env:Path + ";C:\Program Files\Microsoft SQL Server\160\DAC\bin;"
}

$BackupDirectory="C:\SQLServer\backup\"
$DatabaseName="DB_pruebas"

##Source SQL Server name

$SourceServerName="ServerDB"

##Nombre del archivo backup

$dirName = [io.path]::GetDirectoryName($BackupDirectory)
$filename = "DB_pruebas"

$ext= "bacpac"

##target filepath is a combination of the directory and filename appended with year month and day hour minutes and seconds

$TargetFilePath = "$dirName\$filename-$(get-date -f yyyyMMddhhmmss).$ext"

##Ejecutar Export

SqlPackage.exe /a:Export /ssn:$SourceServerName /sdn:$DatabaseName /tf:$TargetFilePath /sec:"Optional" /p:VerifyExtraction=False;IgnoreUserSettingsObjects=true;CompressionOption=Fast;ExcludeObjectTypes="Users,ServerRoleMembership,ServerRoles,DatabaseRoles"

Deja un comentario