/*
Target database: AdventureWorks
Target instance: MYDBSERVER
Generated date: 26/07/2012 2:40:35 PM
Generated on: WIN-DGKEMY3SQW2
ReadyRoll version: 1.3.0.0
Migrations pending: 2
IMPORTANT! "SQLCMD Mode" must be activated prior to execution (under the Query menu in SSMS).
BEFORE EXECUTING THIS SCRIPT, WE STRONGLY RECOMMEND YOU TAKE A BACKUP OF YOUR DATABASE.
This SQLCMD script is designed to be executed through MSBuild (via the .dbproj Deploy target) however
it can also be run manually using SQL Management Studio.
It was generared by the ReadyRoll build task and contains logic to deploy the database, ensuring that
each of the contained "Deploy-Once" scripts is executed a single time only in alphabetical (filename)
order. If any errors occur within those scripts, the deployment will be aborted and the transaction
rolled-back.
NOTE: Automatic transaction management is provided for Deploy-Once migrations, so you don't need to
add any special BEGIN TRAN/COMMIT/ROLLBACK logic in those script files.
However if you require transaction handling in your Pre/Post-Deployment scripts, you will
need to add this logic to the source .sql files yourself.
*/
:setvar DatabaseName "AdventureWorks"
--======================================================================================================================
:on error exit -- Instructs SQLCMD to abort execution as soon as an erroneous batch is encountered
GO
SET IMPLICIT_TRANSACTIONS, NUMERIC_ROUNDABORT OFF;
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NOCOUNT, QUOTED_IDENTIFIER ON;
SET XACT_ABORT ON; -- Abort the current batch immediately if a statement raises a run-time error and rollback any open transaction(s)
IF N'$(DatabaseName)' = N'$' + N'(DatabaseName)' -- Is SQLCMD mode enabled within the execution context (eg. SSMS)
BEGIN
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
BEGIN -- User is sysadmin; abort execution by disconnect the script from the database server
RAISERROR(N'This script must be run in SQLCMD Mode (under the Query menu in SSMS). Aborting connection to suppress subsequent errors.', 20, 127, N'UNKNOWN') WITH LOG;
END
ELSE
BEGIN -- User is not sysadmin; abort execution by switching off statement execution (script will continue to the end without performing any actual deployment work)
RAISERROR(N'This script must be run in SQLCMD Mode (under the Query menu in SSMS). Script execution has been halted.', 16, 127, N'UNKNOWN') WITH NOWAIT;
END
END
GO
IF @@ERROR != 0
BEGIN
SET NOEXEC ON; -- SQLCMD is NOT enabled so prevent any further statements from executing
END
GO
-- Beyond this point, no further explicit error handling is required because it can be assumed that SQLCMD mode is enabled
IF DB_NAME() != 'master'
BEGIN
USE [master];
END
GO
-- As this script has been generated for a specific server instance/database combination, stop execution if there is a mismatch
IF (@@SERVERNAME != 'MYDBSERVER' OR '$(DatabaseName)' != 'AdventureWorks')
BEGIN
RAISERROR(N'This script should only be executed on the following server/instance: [MYDBSERVER] (Database: [AdventureWorks]). Halting deployment.', 16, 127, N'UNKNOWN') WITH NOWAIT;
RETURN;
END
GO
------------------------------------------------------------------------------------------------------------------------
------------------------------------------ PRE-DEPLOYMENT SCRIPTS ------------------------------------------
------------------------------------------------------------------------------------------------------------------------
SET IMPLICIT_TRANSACTIONS, NUMERIC_ROUNDABORT OFF;
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NOCOUNT, QUOTED_IDENTIFIER ON;
--------------------------------- BEGIN PRE-DEPLOYMENT SCRIPT: "01_Create_Database.sql" ----------------------------------
GO
IF (DB_ID(N'$(DatabaseName)') IS NULL)
BEGIN
PRINT N'Creating $(DatabaseName)...';
END
GO
IF (DB_ID(N'$(DatabaseName)') IS NULL)
BEGIN
CREATE DATABASE [$(DatabaseName)]; -- MODIFY THIS STATEMENT TO SPECIFY A COLLATION FOR YOUR DATABASE
END
GO
---------------------------------- END PRE-DEPLOYMENT SCRIPT: "01_Create_Database.sql" -----------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------ DEPLOY-ONCE SCRIPTS ------------------------------------------
------------------------------------------------------------------------------------------------------------------------
SET IMPLICIT_TRANSACTIONS, NUMERIC_ROUNDABORT OFF;
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NOCOUNT, QUOTED_IDENTIFIER ON;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
BEGIN TRANSACTION;
GO
IF DB_NAME() != '$(DatabaseName)'
USE [$(DatabaseName)];
GO
IF EXISTS (SELECT *
FROM [$(DatabaseName)].[dbo].[__MigrationLog]
WHERE [migration_id] = CAST ('1df05a28-d89f-4e87-b41a-40efc3c18d81' AS UNIQUEIDENTIFIER))
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK;
RAISERROR ('This Deploy-Once script "0002_Add-MyTable.sql" has already been executed within the "$(DatabaseName)" database on this server. Halting deployment.', 16, 127);
RETURN;
END
GO
PRINT '
***** EXECUTING DEPLOY-ONCE SCRIPT ''0002_Add-MyTable.sql'', ID: {1df05a28-d89f-4e87-b41a-40efc3c18d81} *****';
GO
----------------------------------- BEGIN DEPLOY-ONCE SCRIPT: "0002_Add-MyTable.sql" -------------------------------------
GO
-- !!! THIS METADATA ENSURES THAT THIS SCRIPT IS EXECUTED A SINGLE TIME ONLY
CREATE TABLE [dbo].[MyTable] (
[Id] INT NOT NULL CONSTRAINT [MyConstraintName] DEFAULT (0),
CONSTRAINT [MyConstraintName2] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
ALTER TABLE [dbo].[MyTable] WITH NOCHECK
ADD CONSTRAINT [MyConstraintName1] CHECK (1=2);
GO
------------------------------------ END DEPLOY-ONCE SCRIPT: "0002_Add-MyTable.sql" --------------------------------------
GO
INSERT [$(DatabaseName)].[dbo].[__MigrationLog] ([migration_id], [script_checksum], [script_filename], [complete_dt], [applied_by])
VALUES (CAST ('1df05a28-d89f-4e87-b41a-40efc3c18d81' AS UNIQUEIDENTIFIER), 'ED1D63BAC3E07091A7C98CC412E6FD388D3F46E1883094CFC9F0CA62B26B8120', '0002_Add-MyTable.sql', getdate(), SYSTEM_USER);
PRINT '***** FINISHED EXECUTING DEPLOY-ONCE SCRIPT ''0002_Add-MyTable.sql'', ID: {1df05a28-d89f-4e87-b41a-40efc3c18d81} *****
';
GO
SET IMPLICIT_TRANSACTIONS, NUMERIC_ROUNDABORT OFF;
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NOCOUNT, QUOTED_IDENTIFIER ON;
GO
IF DB_NAME() != '$(DatabaseName)'
USE [$(DatabaseName)];
GO
IF EXISTS (SELECT *
FROM [$(DatabaseName)].[dbo].[__MigrationLog]
WHERE [migration_id] = CAST ('214706bc-df78-4918-b6c2-ede8c1ffd1c4' AS UNIQUEIDENTIFIER))
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK;
RAISERROR ('This Deploy-Once script "0003_Alter-MyTable.sql" has already been executed within the "$(DatabaseName)" database on this server. Halting deployment.', 16, 127);
RETURN;
END
GO
PRINT '
***** EXECUTING DEPLOY-ONCE SCRIPT ''0003_Alter-MyTable.sql'', ID: {214706bc-df78-4918-b6c2-ede8c1ffd1c4} *****';
GO
---------------------------------- BEGIN DEPLOY-ONCE SCRIPT: "0003_Alter-MyTable.sql" ------------------------------------
GO
-- !!! THIS METADATA ENSURES THAT THIS SCRIPT IS EXECUTED A SINGLE TIME ONLY
ALTER TABLE [dbo].[MyTable]
ADD [MyCol] NCHAR (10) CONSTRAINT [DF_MyTable_MyCol] DEFAULT '' NOT NULL;
GO
----------------------------------- END DEPLOY-ONCE SCRIPT: "0003_Alter-MyTable.sql" -------------------------------------
GO
INSERT [$(DatabaseName)].[dbo].[__MigrationLog] ([migration_id], [script_checksum], [script_filename], [complete_dt], [applied_by])
VALUES (CAST ('214706bc-df78-4918-b6c2-ede8c1ffd1c4' AS UNIQUEIDENTIFIER), '29A230B7D929BE994B88D0C6E6E58F8B3930A48D38C0CC6F31B46E8775340552', '0003_Alter-MyTable.sql', getdate(), SYSTEM_USER);
PRINT '***** FINISHED EXECUTING DEPLOY-ONCE SCRIPT ''0003_Alter-MyTable.sql'', ID: {214706bc-df78-4918-b6c2-ede8c1ffd1c4} *****
';
GO
COMMIT TRANSACTION;
GO
PRINT '2 migration(s) deployed successfully';
GO
------------------------------------------------------------------------------------------------------------------------
------------------------------------------ POST-DEPLOYMENT SCRIPTS ------------------------------------------
------------------------------------------------------------------------------------------------------------------------
SET IMPLICIT_TRANSACTIONS, NUMERIC_ROUNDABORT OFF;
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NOCOUNT, QUOTED_IDENTIFIER ON;
IF DB_NAME() != '$(DatabaseName)'
USE [$(DatabaseName)];
------------------------------ BEGIN POST-DEPLOYMENT SCRIPT: "01_Finalize_Deployment.sql" --------------------------------
GO
/*
Post-Deployment Script Template
--------------------------------------------------------------------------------------
This file contains SQL statements that will be appended to the build script.
Use SQLCMD syntax to include a file in the post-deployment script.
Example: :r .\myfile.sql
Use SQLCMD syntax to reference a variable in the post-deployment script.
Example: :setvar TableName MyTable
SELECT * FROM [$(TableName)]
--------------------------------------------------------------------------------------
*/
GO
------------------------------- END POST-DEPLOYMENT SCRIPT: "01_Finalize_Deployment.sql" ---------------------------------
SET NOEXEC OFF; -- Resume statement execution if an error occurred within the script pre-amble