Database Recovery Model alter script error

1
votes

I am trying to deploy one of our server via teamcity (db project in VS 2012), i am getting an error on Staging server

Error SQL72014: .Net SqlClient Data Provider:
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.

Error SQL72045: Script execution error.

The executed script:

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

Any ideas are appreciated.

1
sqlsql-server
Sorry, I forgot to mention that i am trying to publish this via High Availability DB, basically it mirror to other DB's – jks
Is your script running in SQLCMD mode? Otherwise, $(DatabaseName) is invalid syntax. – Dan
What's your DB context when you run this command? Try USE [master]; immediately before. – Bacon Bits
The scripts are executing without any problem with other servers, but on the Stage server we have Database High Availability server where this will mirror the 2 other database, The problem i see here visual studio generated DB script on deployment is trying to alter the server property, which is SET RECOVERY SIMPLE, if I exclude from the script, the problem goes away, but i am note sure where i can disable this option not to generate. – jks
Guys, I have fixed adding <ScriptDatabaseOptions>False</ScriptDatabaseOptions> in the publish.xml file – jks

1 Answers

0
votes

Don't just gloss over the problem: understand it.

It's because you have some sort of HA (you said mirroring in the comment above, but availability groups (for sure) or log shipping (I think) would also cause this) that requires that the database be in full recovery. When you try to set it to simple recovery, it fails because you can't set the recovery model to anything but full for such a database. You could guard against this by checking what environment you're deploying to from VS and act accordingly.