Sql-server – How to turn off ALTER DATABASE script output in VS 2012

configurationsql serverssdt

I am using SQL Server 2005 in conjunction with Visual Studio 2012 and a SSDT database project.

When I publish my VS project and generate the script to run against my database, VS includes the following in the script. I really don't want the script to go changing database properties, particularly the PAGE_VERIFY setting. I notice that the script doesn't set the properties back the way it found them. How do I turn this off? I have spent time in the Tools->Options dialog and I just don't see the setting.

IF EXISTS (SELECT 1
       FROM   [master].[dbo].[sysdatabases]
       WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET ANSI_NULLS ON,
            ANSI_PADDING ON,
            ANSI_WARNINGS ON,
            ARITHABORT ON,
            CONCAT_NULL_YIELDS_NULL ON,
            QUOTED_IDENTIFIER ON,
            ANSI_NULL_DEFAULT ON,
            CURSOR_DEFAULT LOCAL 
        WITH ROLLBACK IMMEDIATE;

END
GO

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

Best Answer

I am using Visual Studio 2010 and SQL 2008 database projects, but the idea remains the same. In your solution go to the project and do the following steps:

  • right click the project -> Properties
  • Project Settings (1st tab in this setting)
  • go to section Catalog Properties file: make sure a settings file is already selected (by default there's a file \Properties\Database.sqlsettings if it wasn't previously changed)
  • click Edit for the sqlsettings file: there you'll see all the options that are deployed each time to your target database. It should look like the following snap: enter image description here

If you'd like to NOT deploy any of the project default settings, you should do the following steps:

  • go to tab Deploy
  • go to section Deployment configuration file (default it is Properties\Database.sqldeployment)
  • deselect the option "Deploy database properties". It should look something like the following snap: enter image description here

That should help you change/remove the current project settings that you're now deploying.