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?

Answer 1

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