SQL Project Dynamically set Recovery Model

Question

We have a SQL Server Database Project (.sqlproj) in Visual Studio 2012 that we use as source control for our database schema. One of the cool things that it does is generate the SQL to update the schema when we release code.

We have 3 profiles setup - dev, test, live - which is all working fine.

Recently we changed our live database from "Simple" Recovery to "Full" Recovery. Everything was great until we tried to run our next deploy to dev and test. We don't want to change the recovery mode from Simple to Full on dev and test - there is no need for us to change it. However when we publish the database project it now wants to set it.

I want to set the recovery model based on which publish config I am using. I have tried creating a variable and assigning that in the projects xml:

<Recovery>$(RecoveryModel)</Recovery>

but it still tries to set it to "Full" in the deploy script:

:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\"
:setvar RecoveryModel "Simple"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET RECOVERY FULL 
            WITH ROLLBACK IMMEDIATE;
    END

My current work around is to create a script in the Post-Deployment folder to work out which server I'm on and then set the recovery model back to simple if it's dev or test. This doesn't seem like the best solution.

Is there a way to set database properties with SQLCMD Variables?

Immodest

I know this post is several years old, but we are experiencing the same issue. I've been taking the same approach as Greg; Setting the recovery model back to simple for our non-prod environments. Greg or anybody- please tell me you've found an alternate solution! :) – Complacency

@Complacency I don't have a solution yet – Immodest

Answer 1

I know this is an old question but I thought I would suggest this solution. Why can't you just run a Post Deployment script to set the recovery mode.

USE [MYDATABASE];
IF @@SERVERNAME= 'DEVSQLSERVER'
BEGIN;
    ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE ;  
END;

Ipecac

Because they already are but want a better solution: My current work around is to create a script in the Post-Deployment folder to work out which server I'm on and then set the recovery model back to simple if it's dev or test. This doesn't seem like the best solution. – Psychologism

Answer 2

If one were using VSTS: As I see it there are three ways to go about handling this situation (i.e. Recovery mode setting in DACPAC overwriting Recovery Mode in your database(s)).

1. Alter your VSTS Build definition to replace your 'Database Option:Recovery' with the desired state in your *.sqlproj before the msbuild step (I'm not sure you can change this per Release environment or based on the branch you're building on, so kinda ugly and unworkable to start with unless you have a separate build definition per environment):

   Replace <Recovery>SIMPLE</Recovery> with <Recovery>FULL</Recovery> or vice versa in the *.sqlproj file

2. Modify your VSTS Release to use the "SQL Server Database Deploy" task (i.e. don't use "Run a DACPAC file") AND specify in "Additional Arguments":

   /p:ScriptDatabaseOptions=false

3. Modify your VSTS Release to use "SQL Server Database Deploy" AND specify a "Publish Profile". I think the "Deploy database properties" option in the profile is equivalent to "Script Database Options" but I've not tested it because 2 was a reasonable solution for me. One could pre-setup the different profiles before build time or attempt to change a master profile with variables for each Release environment. I really didn't want to complicate my life with this...

So in the end I chose option (2) above and tested with and without the /p:ScriptDatabaseOptions=false setting. It worked as expected. Choosing option (2) means that you'll need to setup your environment databases ahead of time. We use a Recovery mode of SIMPLE in our non-Prod environments and FULL in our Prod environment. To verify either open up the database options in MSSQL Mgt Studio or view the SQL Server Logs to see if a message like so appeared:

Setting database option RECOVERY to SIMPLE for datatbase 'WhatACoolNameForADatabase'.

If I needed to be able to stand up new databases as part of my release process then I may have to venture into the realm of option 3. If that is where you're at, then good luck and let us know what you had to do to make it work.

Recover

Answer 3

You can go to the project settings in visual studio. Click Database Settings > Operational and change the recovery model to SIMPLE.

Indestructible

That will change it, but I want to change it dynamically based on a variable. Our dev environment is simple but production is full, we want the one project to deploy both – Immodest

What if I dont want to change the mode ? What should be my setting value ? – Photojournalism