SQLPackage resets database recovery model

Question

I am using SQLPackage.exe with database publish profiles to deploy database changes to DEV and QC instances. I have databases in simple recovery mode. But when I deploy changes using SQLPackage, it reverts them to full recovery mode.

This is what I am using,

"C:\Program Files (x86)\Microsoft SQL Server110円\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:"FILE PATH TO .DACPAC" /Profile:"PUBLISH PROFILE.XML"

If I use Visual Studio to deploy project changes using same publish project, same happens.

What am I missing here? As far as I can tell, there is no such flag in Profile. Is this expected behavior of SQLPackage?

Answer 1

You're right, as far as I know, that there's nothing in the sqlpackage publish options that would change the recovery model. That property is set in the dacpac. If you have access to the source code of the dacpac - the SQL Server Data Tools (SSDT, .sqlproj) project - make sure that the "Recovery Model" setting is configured to "Simple" rather than "Full."

You can do that in Visual Studio by right-clicking on the project, going to the "Project Settings" tab, and clicking on "Database Settings." This dialog should pop up:

Screenshot of database settings dialog in Visual Studio, with the "Recovery" dropdown option highlighted

If you don't have access to the source code, you can unzip the .dacpac file (it's just a compressed folder), and look in "model.xml" which should have the following for simple:

screenshot of xml "Model" node showing the "RecoveryMode" property set to 1

That line (under DataSchemaModel -> Model):

<Property Name="RecoveryMode" Value="1" />

...is omitted if the recovery model is Full. It's set to "2" for bulk-logged.

• JackLock: Exactly what I was missing. Once I updated recovery mode in database project property to "simple", project gets deployed properly.
• Josh Darnell: @JackLock Excellent! Glad I could help.
• Chris Lamothe: I had a similar issue where I had set a parameter /p:ScriptDatabaseOptions=false it ignored my recovery mode in the dacpac /p:ScriptDatabaseOptions=true fixed it
• Ram Mehta: So what if after running the DAC I dont want the property to change ? What do I need to keep as the setting ?