1

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?

3

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 | |
  • Exactly what I was missing. Once I updated recovery mode in database project property to "simple", project gets deployed properly. – JackLock Jan 28 at 20:45
  • @JackLock Excellent! Glad I could help. – Josh Darnell Jan 28 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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