Red Gate Script Example

-- Alter Database Recovery Models to Bulk_Logged

alter database Warehouse set recovery BULK_LOGGED
go
select databasepropertyex('Warehouse','recovery')
go


-- warehouse 2.4.5.0

SET NUMERIC_ROUNDABORT OFF
go
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
go
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
go
CREATE TABLE #tmpErrors (Error int)
go
SET XACT_ABORT ON
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
BEGIN TRANSACTION
go

print 'Drop cronstraint DAF_DEELTREGELS_NOTA_FK on DAF_DEELTREGELS'
go
alter table DAF_DEELTREGELS drop CONSTRAINT DAF_DEELTREGELS_NOTA_FK
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Drop cronstraint DAF_NOTA_REL_FK on DAF_NOTA'
go
alter table DAF_NOTA drop CONSTRAINT DAF_NOTA_REL_FK
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Drop cronstraint DAF_REL_PK on DAF_REL'
go
alter table DAF_REL drop CONSTRAINT DAF_REL_PK
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Drop cronstraint PK_DAF_DEELTREGELS on DAF_DEELTREGELS'
go
alter table DAF_DEELTREGELS drop CONSTRAINT PK_DAF_DEELTREGELS
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Drop cronstraint DAF_NOTA_PK on DAF_NOTA'
go
alter table DAF_NOTA drop CONSTRAINT DAF_NOTA_PK
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Adding cronstraint DAF_REL_PK to DAF_REL'
go
alter table DAF_REL add CONSTRAINT DAF_REL_PK PRIMARY KEY CLUSTERED (DAF_REL_REKNR, DAF_REL_REKSRT, DAF_REL_REKVAL, DAF_REL_DAGAFNR)
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Adding cronstraint DAF_NOTA_PK on DAF_NOTA'
go
alter table DAF_NOTA add CONSTRAINT DAF_NOTA_PK PRIMARY KEY CLUSTERED (DAF_REL_REKNR, DAF_REL_REKSRT, DAF_REL_REKVAL, DAF_REL_DAGAFNR, DAF_NOTA_ID)
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Adding cronstraint DAF_NOTA_REL_FK on DAF_NOTA'
go
alter table DAF_NOTA add CONSTRAINT DAF_NOTA_REL_FK FOREIGN KEY (DAF_REL_REKNR, DAF_REL_REKSRT, DAF_REL_REKVAL, DAF_REL_DAGAFNR) references DAF_REL
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Adding cronstraint DAF_DEELTREGELS_PK on DAF_DEELTREGELS'
go
alter table DAF_DEELTREGELS add CONSTRAINT DAF_DEELTREGELS_PK PRIMARY KEY CLUSTERED (DAF_REL_REKNR, DAF_REL_REKSRT, DAF_REL_REKVAL, DAF_REL_DAGAFNR, DAF_NOTA_ID, DAF_DEELTREGELS_ID)
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Adding cronstraint DAF_DEELTREGELS_NOTA_FK on DAF_DEELTREGELS'
go
alter table DAF_DEELTREGELS add CONSTRAINT DAF_DEELTREGELS_NOTA_FK FOREIGN KEY (DAF_REL_REKNR, DAF_REL_REKSRT, DAF_REL_REKVAL, DAF_REL_DAGAFNR, DAF_NOTA_ID) references DAF_NOTA
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
go
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
go
DROP TABLE #tmpErrors
go

-- Alter Database Recovery Models to Full
alter database Warehouse set recovery FULL
go
select databasepropertyex('Warehouse','recovery')
go