-- 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
Abonneren op:
Reacties posten (Atom)
Geen opmerkingen:
Een reactie posten