5

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 Server\110\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?

Improve this question

1 Answer 1

Reset to default
8

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.

Improve this answer
4
  • Exactly what I was missing. Once I updated recovery mode in database project property to "simple", project gets deployed properly.
    – JackLock
    Commented Jan 28, 2020 at 20:45
  • @JackLock Excellent! Glad I could help.
    – Josh Darnell
    Commented Jan 28, 2020 at 20:50
  • 2
    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
    – Chris Lamothe
    Commented Jun 4, 2021 at 19:59
  • So what if after running the DAC I dont want the property to change ? What do I need to keep as the setting ?
    – Ram Mehta
    Commented Jul 18, 2022 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.