SQL Project dynamically sets recovery model

Question

We have a SQL Server database project (.sqlproj) in Visual Studio 2012, which we use as a source for our database schema. One of the great things he does is create SQL to update the schema when you release the code.

We have 3 profiles - dev, test, live - everything works fine.

We recently changed our live database from “simple” recovery to “full” recovery. Everything was great until we tried to launch our next deployment for dev and test. We do not want to change the recovery mode from Simple to Full to dev and test - we do not need to change it. However, when we publish the database project, he now wants to install it.

I want to install a recovery model, based on which I use the publication configuration. I tried to create a variable and assign it in xml projects:

<Recovery>$(RecoveryModel)</Recovery> 

but he is still trying to set it to “Full” in the deployment 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 job is to create a script in the folder after deployment to determine which server I'm on, and then install the recovery model again if it is dev or test. This does not seem to be the best solution.

Is there a way to set database properties using SQLCMD variables?

Answer 1

If someone used VSTS: as I see, there are three ways to deal with this situation (that is, setting recovery mode in DACPAC, overwriting recovery mode in your databases).

1. Modify the VSTS Build definition to replace “Database Option: Recovery” with the required state in your * .sqlproj before the msbuild step (I'm not sure if you can change this for the release environment or based on the branch you are building on). so it's ugly and inoperable to start with unless you have a separate build definition for the environment):

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

2. Change your version of VSTS to use the "Deploy SQL Server Database" task (i.e. do not use the "Run DACPAC File") and specify in the "Additional Arguments":

   /p:ScriptDatabaseOptions=false

3. Change your version of VSTS to use “Deploy SQL Server Database” and specify “Publish Profile”. I think that the option “Expand database properties” in the profile is equivalent to “Script database parameters”, but I did not check it because 2 was a reasonable solution for me. You could preconfigure various profiles before building, or try to change the main profile with variables for each release environment. I really did not want to complicate my life with this ...

In the end, I chose option (2) above and tested with / p: ScriptDatabaseOptions = false and without it. It worked as expected. Selecting option (2) means that you will need to pre-configure the databases of your environment. We use SIMPLE recovery mode in our non-Prod and FULL environments in our Prod environment. To check if you open the database options in MSSQL Mgt Studio or look at the SQL Server logs to see if a similar message appears:

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

If I needed to be able to maintain new databases as part of the release process, then I might have to risk the scope of option 3. If you are there, then good luck and let us know what you had to do to make this work.

Answer 2

I know this is an old question, but I decided to offer this solution. Why can't you just run the Post Deployment script to set recovery mode.

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

Answer 3

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