sql-server-2012 - tabla - sql script with data

¿Cómo generar SQL Server 2012 scripts en sql server 2008 r2?

Question

Tengo un script de SQL Server 2012 para crear una base de datos y necesito generarlo en SQL Server 2008 R2. Crea la base de datos pero no se crean tablas ni vistas, etc.

Este es mi guion:

/*
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

Tengo errores:

Msg 102, nivel 15, estado 6, línea 2
Sintaxis incorrecta cerca de ''TARGET_RECOVERY_TIME''.

Msg 319, nivel 15, estado 1, línea 3
Sintaxis incorrecta cerca de la palabra clave ''con''. Si esta instrucción es una expresión de tabla común, una cláusula xmlnamespaces o una cláusula de contexto de seguimiento de cambios, la instrucción anterior debe terminarse con un punto y coma.

Msg 102, nivel 15, estado 1, línea 3
Sintaxis incorrecta cerca de ''INMEDIATO''.

Se encontró un error durante la ejecución del lote. Saliendo

Espero que alguien pueda ayudar. ¡Gracias!

Answer 1

Comentando algunas de esas declaraciones funciona para mí.

/*
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

¡Gracias por tu ayuda!