19

I'm using Windows 7 (x64) and I have an auto generated script for creating a SQL Server Express 2012 database.

The script starts as follows:

USE [master]
GO

 CREATE DATABASE [Example]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Example', FILENAME = N'D:\Example.mdf' , SIZE = 4544KB ,
MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Example_log', FILENAME = N'D:\Example_log.ldf' , SIZE = 3136KB ,
MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
    ALTER DATABASE [Example] SET COMPATIBILITY_LEVEL = 100
    GO
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [Example].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    GO
    ...

The script first error is

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'CONTAINMENT'.

The idea is to pass a database from one server to the other with different SQL Server 2012 versions. I wanted to keep the scripts in case I needed to create the DB somewhere else.

Why does the automatically generated script cause this error?

John Woo
  • 258,903
  • 69
  • 498
  • 492
Pimenta
  • 1,029
  • 2
  • 13
  • 32

1 Answers1

25

Get rid of the CONTAINMENT = NONE

It is the default so you don't need it and SQL Server Express seems to choke on it.

Sebastian Meine
  • 11,260
  • 29
  • 41
  • 1
    Thanks @Sebastian Meine you are correct. I also went back to server and created the script with the version 2012, it was being created as 2008 R2 by default, this was the main issue. – Pimenta Mar 14 '13 at 19:11
  • @Pimenta How did you create the scripts as 2012 instead of 2008 on a 2008 server? – slayernoah Feb 02 '17 at 21:48
  • @slayernoah The server is 2012, but the deefault configuration was set to 2008. – Pimenta Feb 23 '17 at 19:48