sql-server-2012 - studio - installer une base de données sql server 2008



Comment générer des scripts sql server 2012 dans sql server 2008 r2? (1)

J'ai un script de SQL Server 2012 pour créer une base de données et j'ai besoin de le générer dans SQL Server 2008 R2. Il crée la base de données, mais pas de tables et de vues, etc.

C'est mon script:

/*
Deployment script for CMS

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/
GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, 
CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;
GO

:setvar DatabaseName "CMS"
:setvar DefaultFilePrefix "CMS"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL 
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL 
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
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

USE [master];
GO

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    DROP DATABASE [$(DatabaseName)];
END
GO

PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
ON 
PRIMARY(NAME = [$(DatabaseName)], FILENAME = 
N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
LOG ON (NAME = [$(DatabaseName)_log], FILENAME = 
N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE 
SQL_Latin1_General_CP1_CI_AS
GO
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,
            NUMERIC_ROUNDABORT OFF,
            QUOTED_IDENTIFIER ON,
            ANSI_NULL_DEFAULT ON,
            CURSOR_DEFAULT LOCAL,
            RECOVERY FULL,
            CURSOR_CLOSE_ON_COMMIT OFF,
            AUTO_CREATE_STATISTICS ON,
            AUTO_SHRINK OFF,
            AUTO_UPDATE_STATISTICS ON,
            RECURSIVE_TRIGGERS OFF 
        WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [$(DatabaseName)]
        SET AUTO_CLOSE OFF 
        WITH ROLLBACK IMMEDIATE;
END


GO
IF EXISTS (SELECT 1
       FROM   [master].[dbo].[sysdatabases]
       WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET ALLOW_SNAPSHOT_ISOLATION OFF;
END


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


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


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
BEGIN
    IF EXISTS (SELECT 1
               FROM   [master].[dbo].[sysdatabases]
               WHERE  [name] = N'$(DatabaseName)')
        BEGIN
            EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
SET TRUSTWORTHY OFF,
    DB_CHAINING OFF 
WITH ROLLBACK IMMEDIATE';
        END
END
ELSE
BEGIN
    PRINT N'The database settings cannot be modified. You must be a SysAdmin 
to apply these settings.';
END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
BEGIN
    IF EXISTS (SELECT 1
               FROM   [master].[dbo].[sysdatabases]
               WHERE  [name] = N'$(DatabaseName)')
        BEGIN
            EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
SET HONOR_BROKER_PRIORITY OFF 
WITH ROLLBACK IMMEDIATE';
        END
END
ELSE
BEGIN
    PRINT N'The database settings cannot be modified. You must be a SysAdmin 
to apply these settings.';
END


GO
ALTER DATABASE [$(DatabaseName)]
SET TARGET_RECOVERY_TIME = 0 SECONDS 
WITH ROLLBACK IMMEDIATE;


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


GO
IF EXISTS (SELECT 1
       FROM   [master].[dbo].[sysdatabases]
       WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF),
            MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = OFF,
            DELAYED_DURABILITY = DISABLED 
        WITH ROLLBACK IMMEDIATE;
END


GO
USE [$(DatabaseName)];


GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
EXECUTE sp_fulltext_database 'enable';


GO
PRINT N'Creating [Evangelism]...';


GO
CREATE SCHEMA [Evangelism]
AUTHORIZATION [dbo];


GO
PRINT N'Creating [Evangelism].[tbl_Note]...';


GO
CREATE TABLE [Evangelism].[tbl_Note] (
[NoteId]      INT             IDENTITY (1, 1) NOT NULL,
[Description] NVARCHAR (1000) NOT NULL,
[CreatedDate] DATETIME        NOT NULL,
[UpdatedDate] DATETIME        NULL,
[ProspectId]  INT             NOT NULL,
[CreatedBy]   INT             NOT NULL,
[UpdatedBy]   INT             NULL,
CONSTRAINT [pk_Note_NoteId] PRIMARY KEY CLUSTERED ([NoteId] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Address]...';


GO
CREATE TABLE [dbo].[tbl_Address] (
[AddressId]    INT            IDENTITY (1, 1) NOT NULL,
[AddressLine1] NVARCHAR (100) NOT NULL,
[AddressLine2] NVARCHAR (100) NULL,
[Postcode]     NVARCHAR (10)  NOT NULL,
[BarangayId]   INT            NULL,
[DistrictId]   INT            NULL,
[SuburbId]     INT            NULL,
[CityId]       INT            NULL,
[ProvinceId]   INT            NULL,
[StateId]      INT            NULL,
[CountryId]    INT            NULL,
CONSTRAINT [pk_Address_AddressId] PRIMARY KEY CLUSTERED ([AddressId] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Barangay]...';


GO
CREATE TABLE [dbo].[tbl_Barangay] (
[BarangayId] INT            IDENTITY (1, 1) NOT NULL,
[Name]       NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Barangay_BarangayId] PRIMARY KEY CLUSTERED ([BarangayId] 
ASC),
CONSTRAINT [uq_Barangay_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Church]...';


GO
CREATE TABLE [dbo].[tbl_Church] (
[ChurchId]      INT            IDENTITY (1, 1) NOT NULL,
[Code]          NVARCHAR (50)  NOT NULL,
[Name]          NVARCHAR (100) NOT NULL,
[ContactPerson] NVARCHAR (100) NOT NULL,
[AddressId]     INT            NOT NULL,
[ContactId]     INT            NOT NULL,
CONSTRAINT [pk_Church_ChurchId] PRIMARY KEY CLUSTERED ([ChurchId] ASC),
CONSTRAINT [uq_Church_Code] UNIQUE NONCLUSTERED ([Code] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_City]...';


GO
CREATE TABLE [dbo].[tbl_City] (
[CityId] INT            IDENTITY (1, 1) NOT NULL,
[Name]   NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_City_CityId] PRIMARY KEY CLUSTERED ([CityId] ASC),
CONSTRAINT [uq_City_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Contact]...';


GO
CREATE TABLE [dbo].[tbl_Contact] (
[ContactId]     INT            IDENTITY (1, 1) NOT NULL,
[Description]   NVARCHAR (100) NOT NULL,
[ContactTypeId] INT            NOT NULL,
CONSTRAINT [pk_Contact_ContactId] PRIMARY KEY CLUSTERED ([ContactId] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_ContactType]...';


GO
CREATE TABLE [dbo].[tbl_ContactType] (
[ContactTypeId] INT            IDENTITY (1, 1) NOT NULL,
[Name]          NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_ContactType_ContactTypeId] PRIMARY KEY CLUSTERED 
([ContactTypeId] ASC),
CONSTRAINT [uq_ContactType_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Country]...';


GO
CREATE TABLE [dbo].[tbl_Country] (
[CountryId] INT            IDENTITY (1, 1) NOT NULL,
[Code]      NVARCHAR (3)   NOT NULL,
[Name]      NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Country_CountryId] PRIMARY KEY CLUSTERED ([CountryId] ASC),
CONSTRAINT [uq_Country_Code] UNIQUE NONCLUSTERED ([Code] ASC),
CONSTRAINT [uq_Country_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_District]...';


GO
CREATE TABLE [dbo].[tbl_District] (
[DistrictId] INT            IDENTITY (1, 1) NOT NULL,
[Name]       NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_District_DistrictId] PRIMARY KEY CLUSTERED ([DistrictId] 
ASC),
CONSTRAINT [uq_District_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Member]...';


GO
CREATE TABLE [dbo].[tbl_Member] (
[MemberId]    INT           IDENTITY (1, 1) NOT NULL,
[FirstName]   NVARCHAR (50) NOT NULL,
[MiddleName]  NVARCHAR (50) NULL,
[LastName]    NVARCHAR (50) NOT NULL,
[BaptismDate] DATE          NULL,
[ChurchId]    INT           NOT NULL,
[AddressId]   INT           NOT NULL,
[ContactId]   INT           NOT NULL,
CONSTRAINT [pk_Member_MemberId] PRIMARY KEY CLUSTERED ([MemberId] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Prospect]...';


GO
CREATE TABLE [dbo].[tbl_Prospect] (
[ProspectId]       INT           IDENTITY (1, 1) NOT NULL,
[FirstName]        NVARCHAR (50) NOT NULL,
[MiddleName]       NVARCHAR (50) NULL,
[LastName]         NVARCHAR (50) NOT NULL,
[LastContactDate]  DATE          NOT NULL,
[ChurchId]         INT           NOT NULL,
[AssignedTo]       INT           NULL,
[AddressId]        INT           NOT NULL,
[ContactId]        INT           NOT NULL,
[ProspectStatusId] INT           NOT NULL,
CONSTRAINT [pk_Prospect_ProspectId] PRIMARY KEY CLUSTERED ([ProspectId] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_ProspectStatus]...';


GO
CREATE TABLE [dbo].[tbl_ProspectStatus] (
[ProspectStatusId] INT            IDENTITY (1, 1) NOT NULL,
[Description]      NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_ProspectStatus_ProspectStatusId] PRIMARY KEY CLUSTERED 
([ProspectStatusId] ASC),
CONSTRAINT [uq_ProspectStatus_Description] UNIQUE NONCLUSTERED 
([Description] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Province]...';


GO
CREATE TABLE [dbo].[tbl_Province] (
[ProvinceId] INT            IDENTITY (1, 1) NOT NULL,
[Name]       NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Province_ProvinceId] PRIMARY KEY CLUSTERED ([ProvinceId] 
ASC),
CONSTRAINT [uq_Province_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_State]...';


GO
CREATE TABLE [dbo].[tbl_State] (
[StateId] INT            IDENTITY (1, 1) NOT NULL,
[Name]    NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_State_StateId] PRIMARY KEY CLUSTERED ([StateId] ASC),
CONSTRAINT [uq_State_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Suburb]...';


GO
CREATE TABLE [dbo].[tbl_Suburb] (
[SuburbId] INT            IDENTITY (1, 1) NOT NULL,
[Name]     NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Suburb_SuburbId] PRIMARY KEY CLUSTERED ([SuburbId] ASC),
CONSTRAINT [uq_Suburb_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [Evangelism].[fk_Note_ProspectId]...';


GO
ALTER TABLE [Evangelism].[tbl_Note]
ADD CONSTRAINT [fk_Note_ProspectId] FOREIGN KEY ([ProspectId]) REFERENCES 
[dbo].[tbl_Prospect] ([ProspectId]);


GO
PRINT N'Creating [Evangelism].[fk_Note_CreatedBy]...';


GO
ALTER TABLE [Evangelism].[tbl_Note]
ADD CONSTRAINT [fk_Note_CreatedBy] FOREIGN KEY ([CreatedBy]) REFERENCES 
[dbo].[tbl_Member] ([MemberId]);


GO
PRINT N'Creating [Evangelism].[fk_Note_UpdatedBy]...';


GO
ALTER TABLE [Evangelism].[tbl_Note]
ADD CONSTRAINT [fk_Note_UpdatedBy] FOREIGN KEY ([UpdatedBy]) REFERENCES 
[dbo].[tbl_Member] ([MemberId]);


GO
PRINT N'Creating [dbo].[fk_Address_BarangayId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_BarangayId] FOREIGN KEY ([BarangayId]) REFERENCES 
[dbo].[tbl_Barangay] ([BarangayId]);


GO
PRINT N'Creating [dbo].[fk_Address_DistrictId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_DistrictId] FOREIGN KEY ([DistrictId]) REFERENCES 
[dbo].[tbl_District] ([DistrictId]);


GO
PRINT N'Creating [dbo].[fk_Address_SuburbId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_SuburbId] FOREIGN KEY ([SuburbId]) REFERENCES 
[dbo].[tbl_Suburb] ([SuburbId]);


GO
PRINT N'Creating [dbo].[fk_Address_CityId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_CityId] FOREIGN KEY ([CityId]) REFERENCES [dbo].
[tbl_City] ([CityId]);


GO
PRINT N'Creating [dbo].[fk_Address_ProvinceId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_ProvinceId] FOREIGN KEY ([ProvinceId]) REFERENCES 
[dbo].[tbl_Province] ([ProvinceId]);


GO
PRINT N'Creating [dbo].[fk_Address_StateId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_StateId] FOREIGN KEY ([StateId]) REFERENCES 
[dbo].[tbl_State] ([StateId]);


GO
PRINT N'Creating [dbo].[fk_Address_CountryId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_CountryId] FOREIGN KEY ([CountryId]) REFERENCES 
[dbo].[tbl_Country] ([CountryId]);


GO
PRINT N'Creating [dbo].[fk_Church_AddressId]...';


GO
ALTER TABLE [dbo].[tbl_Church]
ADD CONSTRAINT [fk_Church_AddressId] FOREIGN KEY ([AddressId]) REFERENCES 
[dbo].[tbl_Address] ([AddressId]);


GO
PRINT N'Creating [dbo].[fk_Church_ContactId]...';


GO
ALTER TABLE [dbo].[tbl_Church]
ADD CONSTRAINT [fk_Church_ContactId] FOREIGN KEY ([ContactId]) REFERENCES 
[dbo].[tbl_Contact] ([ContactId]);


GO
PRINT N'Creating [dbo].[fk_Contact_ContactTypeId]...';


GO
ALTER TABLE [dbo].[tbl_Contact]
ADD CONSTRAINT [fk_Contact_ContactTypeId] FOREIGN KEY ([ContactTypeId]) 
REFERENCES [dbo].[tbl_ContactType] ([ContactTypeId]);


GO
PRINT N'Creating [dbo].[fk_Member_ChurchId]...';


GO
ALTER TABLE [dbo].[tbl_Member]
ADD CONSTRAINT [fk_Member_ChurchId] FOREIGN KEY ([ChurchId]) REFERENCES 
[dbo].[tbl_Church] ([ChurchId]);


GO
PRINT N'Creating [dbo].[fk_Member_AddressId]...';


GO
ALTER TABLE [dbo].[tbl_Member]
ADD CONSTRAINT [fk_Member_AddressId] FOREIGN KEY ([AddressId]) REFERENCES 
[dbo].[tbl_Address] ([AddressId]);


GO
PRINT N'Creating [dbo].[fk_Member_ContactId]...';


GO
ALTER TABLE [dbo].[tbl_Member]
ADD CONSTRAINT [fk_Member_ContactId] FOREIGN KEY ([ContactId]) REFERENCES 
[dbo].[tbl_Contact] ([ContactId]);


GO
PRINT N'Creating [dbo].[fk_Prospect_ChurchId]...';


GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_ChurchId] FOREIGN KEY ([ChurchId]) REFERENCES 
[dbo].[tbl_Church] ([ChurchId]);


GO
PRINT N'Creating [dbo].[fk_Prospect_AssignedTo]...';


GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_AssignedTo] FOREIGN KEY ([AssignedTo]) 
REFERENCES [dbo].[tbl_Member] ([MemberId]);


GO
PRINT N'Creating [dbo].[fk_Prospect_AddressId]...';


GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_AddressId] FOREIGN KEY ([AddressId]) REFERENCES 
[dbo].[tbl_Address] ([AddressId]);


GO
PRINT N'Creating [dbo].[fk_Prospect_ContactId]...';


GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_ContactId] FOREIGN KEY ([ContactId]) REFERENCES 
[dbo].[tbl_Contact] ([ContactId]);


GO
PRINT N'Creating [dbo].[fk_Prospect_ProspectStatusId]...';


GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_ProspectStatusId] FOREIGN KEY 
([ProspectStatusId]) REFERENCES [dbo].[tbl_ProspectStatus] 
([ProspectStatusId]);


GO
DECLARE @VarDecimalSupported AS BIT;

SELECT @VarDecimalSupported = 0;

IF ((ServerProperty(N'EngineEdition') = 3)
AND (((@@microsoftversion / power(2, 24) = 9)
      AND (@@microsoftversion & 0xffff >= 3024))
     OR ((@@microsoftversion / power(2, 24) = 10)
         AND (@@microsoftversion & 0xffff >= 1600))))
SELECT @VarDecimalSupported = 1;

IF (@VarDecimalSupported > 0)
BEGIN
    EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
END


GO
PRINT N'Update complete.';

GO

J'ai des erreurs:

Msg 102, niveau 15, état 6, ligne 2
Syntaxe incorrecte près de 'TARGET_RECOVERY_TIME'.

Msg 319, niveau 15, état 1, ligne 3
Syntaxe incorrecte près du mot clé 'avec'. Si cette instruction est une expression de table commune, une clause xmlnamespaces ou une clause de contexte de suivi des modifications, l'instruction précédente doit être terminée par un point-virgule.

Msg 102, niveau 15, état 1, ligne 3
Syntaxe incorrecte près de "IMMEDIATE".

Une erreur s'est produite lors de l'exécution du lot. Quitter

J'espère que quelqu'un pourrait aider. Merci!


Commenter certaines de ces déclarations fonctionne pour moi.

/*
Deployment script for CMS

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "CMS"
:setvar DefaultFilePrefix "CMS"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"

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
USE [master];


GO

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
ON 
PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS
GO
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,
            NUMERIC_ROUNDABORT OFF,
            QUOTED_IDENTIFIER ON,
            ANSI_NULL_DEFAULT ON,
            CURSOR_DEFAULT LOCAL,
            RECOVERY FULL,
            CURSOR_CLOSE_ON_COMMIT OFF,
            AUTO_CREATE_STATISTICS ON,
            AUTO_SHRINK OFF,
            AUTO_UPDATE_STATISTICS ON,
            RECURSIVE_TRIGGERS OFF 
        WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [$(DatabaseName)]
        SET AUTO_CLOSE OFF 
        WITH ROLLBACK IMMEDIATE;
END


GO
IF EXISTS (SELECT 1
       FROM   [master].[dbo].[sysdatabases]
       WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET ALLOW_SNAPSHOT_ISOLATION OFF;
END


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


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


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
BEGIN
    IF EXISTS (SELECT 1
               FROM   [master].[dbo].[sysdatabases]
               WHERE  [name] = N'$(DatabaseName)')
        BEGIN
            EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
SET TRUSTWORTHY OFF,
    DB_CHAINING OFF 
WITH ROLLBACK IMMEDIATE';
        END
END
ELSE
BEGIN
    PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
BEGIN
    IF EXISTS (SELECT 1
               FROM   [master].[dbo].[sysdatabases]
               WHERE  [name] = N'$(DatabaseName)')
        BEGIN
            EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
SET HONOR_BROKER_PRIORITY OFF 
WITH ROLLBACK IMMEDIATE';
        END
END
ELSE
BEGIN
    PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
END


--GO
--ALTER DATABASE [$(DatabaseName)]
--    SET TARGET_RECOVERY_TIME = 0 SECONDS 
--    WITH ROLLBACK IMMEDIATE;


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


--GO
--IF EXISTS (SELECT 1
--           FROM   [master].[dbo].[sysdatabases]
--           WHERE  [name] = N'$(DatabaseName)')
--    BEGIN
--        ALTER DATABASE [$(DatabaseName)]
--            SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF),
--                MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = OFF,
--                DELAYED_DURABILITY = DISABLED 
--            WITH ROLLBACK IMMEDIATE;
--    END


GO
USE [$(DatabaseName)];


GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
EXECUTE sp_fulltext_database 'enable';


GO
PRINT N'Creating [Evangelism]...';


GO
CREATE SCHEMA [Evangelism]
AUTHORIZATION [dbo];


GO
PRINT N'Creating [Evangelism].[tbl_Note]...';


GO
CREATE TABLE [Evangelism].[tbl_Note] (
[NoteId]      INT             IDENTITY (1, 1) NOT NULL,
[Description] NVARCHAR (1000) NOT NULL,
[CreatedDate] DATETIME        NOT NULL,
[UpdatedDate] DATETIME        NULL,
[ProspectId]  INT             NOT NULL,
[CreatedBy]   INT             NOT NULL,
[UpdatedBy]   INT             NULL,
CONSTRAINT [pk_Note_NoteId] PRIMARY KEY CLUSTERED ([NoteId] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Address]...';


GO
CREATE TABLE [dbo].[tbl_Address] (
[AddressId]    INT            IDENTITY (1, 1) NOT NULL,
[AddressLine1] NVARCHAR (100) NOT NULL,
[AddressLine2] NVARCHAR (100) NULL,
[Postcode]     NVARCHAR (10)  NOT NULL,
[BarangayId]   INT            NULL,
[DistrictId]   INT            NULL,
[SuburbId]     INT            NULL,
[CityId]       INT            NULL,
[ProvinceId]   INT            NULL,
[StateId]      INT            NULL,
[CountryId]    INT            NULL,
CONSTRAINT [pk_Address_AddressId] PRIMARY KEY CLUSTERED ([AddressId] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Barangay]...';


GO
CREATE TABLE [dbo].[tbl_Barangay] (
[BarangayId] INT            IDENTITY (1, 1) NOT NULL,
[Name]       NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Barangay_BarangayId] PRIMARY KEY CLUSTERED ([BarangayId] ASC),
CONSTRAINT [uq_Barangay_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Church]...';


GO
CREATE TABLE [dbo].[tbl_Church] (
[ChurchId]      INT            IDENTITY (1, 1) NOT NULL,
[Code]          NVARCHAR (50)  NOT NULL,
[Name]          NVARCHAR (100) NOT NULL,
[ContactPerson] NVARCHAR (100) NOT NULL,
[AddressId]     INT            NOT NULL,
[ContactId]     INT            NOT NULL,
CONSTRAINT [pk_Church_ChurchId] PRIMARY KEY CLUSTERED ([ChurchId] ASC),
CONSTRAINT [uq_Church_Code] UNIQUE NONCLUSTERED ([Code] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_City]...';


GO
CREATE TABLE [dbo].[tbl_City] (
[CityId] INT            IDENTITY (1, 1) NOT NULL,
[Name]   NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_City_CityId] PRIMARY KEY CLUSTERED ([CityId] ASC),
CONSTRAINT [uq_City_Name] UNIQUE NONCLUSTERED ([Name] ASC)

)

GO
PRINT N'Creating [dbo].[tbl_Contact]...';


GO
CREATE TABLE [dbo].[tbl_Contact] (
[ContactId]     INT            IDENTITY (1, 1) NOT NULL,
[Description]   NVARCHAR (100) NOT NULL,
[ContactTypeId] INT            NOT NULL,
CONSTRAINT [pk_Contact_ContactId] PRIMARY KEY CLUSTERED ([ContactId] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_ContactType]...';


GO
CREATE TABLE [dbo].[tbl_ContactType] (
[ContactTypeId] INT            IDENTITY (1, 1) NOT NULL,
[Name]          NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_ContactType_ContactTypeId] PRIMARY KEY CLUSTERED ([ContactTypeId] ASC),
CONSTRAINT [uq_ContactType_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Country]...';


GO
CREATE TABLE [dbo].[tbl_Country] (
[CountryId] INT            IDENTITY (1, 1) NOT NULL,
[Code]      NVARCHAR (3)   NOT NULL,
[Name]      NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Country_CountryId] PRIMARY KEY CLUSTERED ([CountryId] ASC),
CONSTRAINT [uq_Country_Code] UNIQUE NONCLUSTERED ([Code] ASC),
CONSTRAINT [uq_Country_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_District]...';


GO
CREATE TABLE [dbo].[tbl_District] (
[DistrictId] INT            IDENTITY (1, 1) NOT NULL,
[Name]       NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_District_DistrictId] PRIMARY KEY CLUSTERED ([DistrictId] ASC),
CONSTRAINT [uq_District_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Member]...';


GO
CREATE TABLE [dbo].[tbl_Member] (
[MemberId]    INT           IDENTITY (1, 1) NOT NULL,
[FirstName]   NVARCHAR (50) NOT NULL,
[MiddleName]  NVARCHAR (50) NULL,
[LastName]    NVARCHAR (50) NOT NULL,
[BaptismDate] DATE          NULL,
[ChurchId]    INT           NOT NULL,
[AddressId]   INT           NOT NULL,
[ContactId]   INT           NOT NULL,
CONSTRAINT [pk_Member_MemberId] PRIMARY KEY CLUSTERED ([MemberId] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Prospect]...';


GO
CREATE TABLE [dbo].[tbl_Prospect] (
[ProspectId]       INT           IDENTITY (1, 1) NOT NULL,
[FirstName]        NVARCHAR (50) NOT NULL,
[MiddleName]       NVARCHAR (50) NULL,
[LastName]         NVARCHAR (50) NOT NULL,
[LastContactDate]  DATE          NOT NULL,
[ChurchId]         INT           NOT NULL,
[AssignedTo]       INT           NULL,
[AddressId]        INT           NOT NULL,
[ContactId]        INT           NOT NULL,
[ProspectStatusId] INT           NOT NULL,
CONSTRAINT [pk_Prospect_ProspectId] PRIMARY KEY CLUSTERED ([ProspectId] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_ProspectStatus]...';


GO
CREATE TABLE [dbo].[tbl_ProspectStatus] (
[ProspectStatusId] INT            IDENTITY (1, 1) NOT NULL,
[Description]      NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_ProspectStatus_ProspectStatusId] PRIMARY KEY CLUSTERED ([ProspectStatusId] ASC),
CONSTRAINT [uq_ProspectStatus_Description] UNIQUE NONCLUSTERED ([Description] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Province]...';


GO
CREATE TABLE [dbo].[tbl_Province] (
[ProvinceId] INT            IDENTITY (1, 1) NOT NULL,
[Name]       NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Province_ProvinceId] PRIMARY KEY CLUSTERED ([ProvinceId] ASC),
CONSTRAINT [uq_Province_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_State]...';


GO
CREATE TABLE [dbo].[tbl_State] (
[StateId] INT            IDENTITY (1, 1) NOT NULL,
[Name]    NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_State_StateId] PRIMARY KEY CLUSTERED ([StateId] ASC),
CONSTRAINT [uq_State_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [dbo].[tbl_Suburb]...';


GO
CREATE TABLE [dbo].[tbl_Suburb] (
[SuburbId] INT            IDENTITY (1, 1) NOT NULL,
[Name]     NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Suburb_SuburbId] PRIMARY KEY CLUSTERED ([SuburbId] ASC),
CONSTRAINT [uq_Suburb_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [Evangelism].[fk_Note_ProspectId]...';


GO
ALTER TABLE [Evangelism].[tbl_Note]
ADD CONSTRAINT [fk_Note_ProspectId] FOREIGN KEY ([ProspectId]) REFERENCES [dbo].[tbl_Prospect] ([ProspectId]);


GO
PRINT N'Creating [Evangelism].[fk_Note_CreatedBy]...';


GO
ALTER TABLE [Evangelism].[tbl_Note]
ADD CONSTRAINT [fk_Note_CreatedBy] FOREIGN KEY ([CreatedBy]) REFERENCES [dbo].[tbl_Member] ([MemberId]);


GO
PRINT N'Creating [Evangelism].[fk_Note_UpdatedBy]...';


GO
ALTER TABLE [Evangelism].[tbl_Note]
ADD CONSTRAINT [fk_Note_UpdatedBy] FOREIGN KEY ([UpdatedBy]) REFERENCES [dbo].[tbl_Member] ([MemberId]);


GO
PRINT N'Creating [dbo].[fk_Address_BarangayId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_BarangayId] FOREIGN KEY ([BarangayId]) REFERENCES [dbo].[tbl_Barangay] ([BarangayId]);


GO
PRINT N'Creating [dbo].[fk_Address_DistrictId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_DistrictId] FOREIGN KEY ([DistrictId]) REFERENCES [dbo].[tbl_District] ([DistrictId]);


GO
PRINT N'Creating [dbo].[fk_Address_SuburbId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_SuburbId] FOREIGN KEY ([SuburbId]) REFERENCES [dbo].[tbl_Suburb] ([SuburbId]);


GO
PRINT N'Creating [dbo].[fk_Address_CityId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_CityId] FOREIGN KEY ([CityId]) REFERENCES [dbo].[tbl_City] ([CityId]);


GO
PRINT N'Creating [dbo].[fk_Address_ProvinceId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_ProvinceId] FOREIGN KEY ([ProvinceId]) REFERENCES [dbo].[tbl_Province] ([ProvinceId]);


GO
PRINT N'Creating [dbo].[fk_Address_StateId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_StateId] FOREIGN KEY ([StateId]) REFERENCES [dbo].[tbl_State] ([StateId]);


GO
PRINT N'Creating [dbo].[fk_Address_CountryId]...';


GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_CountryId] FOREIGN KEY ([CountryId]) REFERENCES [dbo].[tbl_Country] ([CountryId]);


GO
PRINT N'Creating [dbo].[fk_Church_AddressId]...';


GO
ALTER TABLE [dbo].[tbl_Church]
ADD CONSTRAINT [fk_Church_AddressId] FOREIGN KEY ([AddressId]) REFERENCES [dbo].[tbl_Address] ([AddressId]);


GO
PRINT N'Creating [dbo].[fk_Church_ContactId]...';


GO
ALTER TABLE [dbo].[tbl_Church]
ADD CONSTRAINT [fk_Church_ContactId] FOREIGN KEY ([ContactId]) REFERENCES [dbo].[tbl_Contact] ([ContactId]);


GO
PRINT N'Creating [dbo].[fk_Contact_ContactTypeId]...';


GO
ALTER TABLE [dbo].[tbl_Contact]
ADD CONSTRAINT [fk_Contact_ContactTypeId] FOREIGN KEY ([ContactTypeId]) REFERENCES [dbo].[tbl_ContactType] ([ContactTypeId]);


GO
PRINT N'Creating [dbo].[fk_Member_ChurchId]...';


GO
ALTER TABLE [dbo].[tbl_Member]
ADD CONSTRAINT [fk_Member_ChurchId] FOREIGN KEY ([ChurchId]) REFERENCES [dbo].[tbl_Church] ([ChurchId]);


GO
PRINT N'Creating [dbo].[fk_Member_AddressId]...';


GO
ALTER TABLE [dbo].[tbl_Member]
ADD CONSTRAINT [fk_Member_AddressId] FOREIGN KEY ([AddressId]) REFERENCES [dbo].[tbl_Address] ([AddressId]);


GO
PRINT N'Creating [dbo].[fk_Member_ContactId]...';


GO
ALTER TABLE [dbo].[tbl_Member]
ADD CONSTRAINT [fk_Member_ContactId] FOREIGN KEY ([ContactId]) REFERENCES 
[dbo].[tbl_Contact] ([ContactId]);


GO
PRINT N'Creating [dbo].[fk_Prospect_ChurchId]...';


GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_ChurchId] FOREIGN KEY ([ChurchId]) REFERENCES [dbo].[tbl_Church] ([ChurchId]);


GO
PRINT N'Creating [dbo].[fk_Prospect_AssignedTo]...';


GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_AssignedTo] FOREIGN KEY ([AssignedTo]) REFERENCES [dbo].[tbl_Member] ([MemberId]);


GO
PRINT N'Creating [dbo].[fk_Prospect_AddressId]...';


GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_AddressId] FOREIGN KEY ([AddressId]) REFERENCES [dbo].[tbl_Address] ([AddressId]);


GO
PRINT N'Creating [dbo].[fk_Prospect_ContactId]...';


GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_ContactId] FOREIGN KEY ([ContactId]) REFERENCES [dbo].[tbl_Contact] ([ContactId]);


GO
PRINT N'Creating [dbo].[fk_Prospect_ProspectStatusId]...';


GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_ProspectStatusId] FOREIGN KEY ([ProspectStatusId]) REFERENCES [dbo].[tbl_ProspectStatus] ([ProspectStatusId]);


GO
DECLARE @VarDecimalSupported AS BIT;

SELECT @VarDecimalSupported = 0;

IF ((ServerProperty(N'EngineEdition') = 3)
AND (((@@microsoftversion / power(2, 24) = 9)
      AND (@@microsoftversion & 0xffff >= 3024))
     OR ((@@microsoftversion / power(2, 24) = 10)
         AND (@@microsoftversion & 0xffff >= 1600))))
SELECT @VarDecimalSupported = 1;

IF (@VarDecimalSupported > 0)
BEGIN
    EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
END


GO
PRINT N'Update complete.';


GO

Merci de votre aide!





sql-server-2008-r2