Disable Temporal History Retention in Visual Studio SQL Server 2017 Database Project

user_1760167 0
Viewed 243

Is there any way to set the database option for Temporal History Retention to off in a Visual Studio SQL Server 2017 Database project?

When I generate a script from my publish profile it adds the code below and I'm unable to find a way to prevent it. 

ALTER DATABASE [$(DatabaseName)]
    SET TEMPORAL_HISTORY_RETENTION ON 
    WITH ROLLBACK IMMEDIATE;

I don't want to prevent it from setting all db options as I do use others. I only want to set this one to OFF so the script doesn't produce the output above.

  • Visual Studio Version - Professional 2019 (16.4.5)
  • SQL Server Data Tools - 16.0.62002.03150
  • SQL Server Version - 2017 (14.0.3223.3)

I feel like i'm just missing a tick box somewhere?!?

Any help would be very much appreciated.

sql-server-data-tools sql-server-2017 visual-studio-2019
2 Answers

Add this parameter /p:"ScriptDatabaseOptions=false"

user_166349600

I think this is a bug in the Sql Server Database Tools.

There is a MSBuid property TemporalHistoryRetentionEnabled that is passed to the SlqBuildTask so I tried adding the TemporalHistoryRetentionEnabled property to the SQL Server Database Project's sqlproj file:

<TemporalHistoryRetentionEnabled>Off</TemporalHistoryRetentionEnabled>

Unfortunately, I found that when I did this I got a build error:

C:\Program Files\Microsoft Visual Studio\2022\Professional\MSBuild\Microsoft\VisualStudio\v17.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets(507,5): Error:  MSB4018: The "SqlBuildTask" task failed unexpectedly.
System.Reflection.TargetException: Object does not match target type.
   at System.Reflection.RuntimeMethodInfo.CheckConsistency(Object target)
   at System.Reflection.RuntimeMethodInfo.InvokeArgumentsCheck(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.Reflection.RuntimePropertyInfo.SetValue(Object obj, Object value, Object[] index)
   at Microsoft.Data.Tools.Schema.Sql.Build.SqlTaskHost.SetFieldInModelOptionsObject(KeyValuePair`2 propItem, SqlModelOptions modelOptions)
   at Microsoft.Data.Tools.Schema.Sql.Build.SqlTaskHost.InitializeDatabaseOptions(String propertyThatChanged)
   at Microsoft.Data.Tools.Schema.Sql.Build.SqlTaskHost.Set(String propertyName, Object value)
   at Microsoft.Data.Tools.Schema.Tasks.Sql.TaskHostLoader.SetDatabaseModelOptionsProperties(SqlTaskHost host, IDictionary`2 properties)
   at Microsoft.Data.Tools.Schema.Tasks.Sql.TaskHostLoader.ValidateHost(SqlTaskHost& host, ErrorManager& errors)
   at Microsoft.Data.Tools.Schema.Tasks.Sql.TaskHostLoader.LoadImpl(ITaskHost providedHost, TaskLoggingHelper providedLogger)
   at Microsoft.Data.Tools.Schema.Tasks.Sql.TaskHostLoader.Load(ITaskHost providedHost, TaskLoggingHelper providedLogger)
   at Microsoft.Data.Tools.Schema.Tasks.Sql.SqlBuildTask.ExecuteLoadTaskHostStep()
   at Microsoft.Data.Tools.Schema.Tasks.Sql.SqlBuildTask.ExecuteStep(Func`1 step)
   at Microsoft.Data.Tools.Schema.Tasks.Sql.SqlBuildTask.Execute()
   at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()
   at Microsoft.Build.BackEnd.TaskBuilder.<ExecuteInstantiatedTask>d__26.MoveNext()

The build error goes away when the Target Platform is set to Microsoft Azure SQL Database. Any other Target Platform setting seems to cause problems.

Unfortunately there doesn't seem to be a way to prevent TEMPORAL_HISTORY_RETENTION from being set ON at lower Target Platform versions if the target database has the TEMPORAL_HISTORY_RETENTION set to OFF.

user_7454140
Related