Sql-server – Deployment script always re-create constraints and indexes

I'm unable to reproduce the issue you describe. Here's simplified script I used to test from 2012 and 2008 R2 to a 2008 R2 server. Comment/Uncomment the add-type section as needed. One thought is that using deprecated LoadWithPartialName could be causing issues if you have both 2008 and 2012 assemblies on same machine.

#SQL 2008 R2
#add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
#add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
#add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

#SQL 2012
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

$InstanceName = "myservername"

$query = @"
CREATE TABLE dbo.authors(
    au_id int NOT NULL,
    au_lname varchar(40) NOT NULL,
    au_fname varchar(20) NOT NULL

 CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED 
(
    au_id ASC
)
) 
"@

$InstanceObject = New-Object "Microsoft.SqlServer.Management.SMO.Server" "$InstanceName"

$db = $InstanceObject.Databases['tempdb']
$db.ExecuteNonQuery($query)

$MyScripter = New-Object "Microsoft.SqlServer.Management.SMO.Scripter"

$MyScripter.Server = $InstanceObject

$MyScripter.Options.DriAll = $true
#$MyScripter.Options.DriPrimaryKey = $false
#$MyScripter.Options.DriClustered = $false

$MyScripter.Script($InstanceObject.Databases['tempdb'].Tables['authors'])

SQL Server Management Studio (SSMS) only supports a subset of the SQLCMD commands, and SQL Server Data Tools (SSDT) only supports a subset of the SQLCMD commands that are allowed in SSMS. The [:]!! SQLCMD command, while supported by SSMS, is not supported in SSDT Pre-Deployment or Post-Deployment SQL scripts. This is most likely due to the large amount of confusion that would be caused by people not realizing that the command that is executed by the [:]!! SQLCMD command actually runs on the machine where SQLCMD is running, which is not guaranteed to be consistent across executions, and is not necessarily the same machine that SQL Server is running on.

For example, when doing development in Visual Studio on your local workstation or laptop and running this deployment, the command would execute on your local workstation or laptop. Then, when deploying to testing / QA, it would run on whatever machine does that deployment, which is typically not your local workstation. And then when deploying to Production, it would again run on the build server, which may or may not be the same server that handled the deployment to the QA server.

If you change your Post-Deployment to do just the following and then do a build:

:!! dir

then you would see this error:

72006: Fatal scripting error: Command Execute is not supported.

You have three choices (that I can think of, at least):

1. Run aspnet_regsql.exe via xp_cmdshell (which will run under the security context of the Log On account associated with the SQLSERVER service). This option allows you to manage the call to aspnet_regsql.exe within the deployment SQL script and will get executed whenever you do a build.

2. Edit the "Post-build event" to call this same command line. This will allow aspnet_regsql.exe to be executed in the security context of whatever Windows / Active Directory Login runs the deployment. But, the execution of aspnet_regsql.exe is not in the SQL script, so the publish process needs to use SqlPackage.exe so that it knows to execute whatever commands are in the "Post-build event". But, this is at least still part of the SSDT publish process and will get executed when you do a build.

3. Handle this outside of the SSDT deployment by creating a CMD script that will be executed on the server running SQL Server, and that calls SQLCMD to run your deployment SQL script and then aspnet_regsql.exe. This option, however, is outside of the SSDT publish process and won't get executed when doing a build in Visual Studio.