BlogEngine.Net SQL Update Script

by Damiaan Peeters 2. January 2008 14:12

I am using a great tool called SQL Examiner. 

Using this tool i created a "update" script for BlogEngine 1.3.  (from version 1.2).

Replace the "yourdatabasename" by the name of your database.

[more]

 

/*
Script created by SQL Examiner 1.6.0.42 at 01/02/2008 13:43:38 
Upgrade SQL Database from 1.2 to version 1.3
*/
--step 1. Backup database
BACKUP DATABASE [yourDatabaseName] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\yourDatabaseName_db_200801021343.bak' WITH  INIT,  NOUNLOAD,  NAME = N'yourDatabaseName backup', NOSKIP, DESCRIPTION = N'SQL Examiner Backup', NOFORMAT
GO
USE [yourDatabaseName]
GO
SET NOCOUNT ON
SET NOEXEC OFF
SET ARITHABORT ON
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRAN
GO
--step 2: add columns to Table dbo.be_Categories----------------------------------------------------
GO
ALTER TABLE [dbo].[be_Categories] ADD 
[Description]	[nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 2 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 2 is completed with errors' SET NOEXEC ON END
GO
--step 3: alter column [CategoryName] of table [dbo].[be_Categories]--------------------------------
GO
ALTER TABLE [dbo].[be_Categories] ALTER COLUMN [CategoryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 3 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 3 is completed with errors' SET NOEXEC ON END
GO
--step 4: alter column [Title] of table [dbo].[be_Pages]--------------------------------------------
GO
ALTER TABLE [dbo].[be_Pages] ALTER COLUMN [Title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 4 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 4 is completed with errors' SET NOEXEC ON END
GO
--step 5: alter column [Description] of table [dbo].[be_Pages]--------------------------------------
GO
ALTER TABLE [dbo].[be_Pages] ALTER COLUMN [Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 5 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 5 is completed with errors' SET NOEXEC ON END
GO
--step 6: alter column [Keywords] of table [dbo].[be_Pages]-----------------------------------------
GO
ALTER TABLE [dbo].[be_Pages] ALTER COLUMN [Keywords] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 6 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 6 is completed with errors' SET NOEXEC ON END
GO
--step 7: alter column [Link] of table [dbo].[be_PingService]---------------------------------------
GO
ALTER TABLE [dbo].[be_PingService] ALTER COLUMN [Link] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 7 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 7 is completed with errors' SET NOEXEC ON END
GO
--step 8: dbo.be_PostCategory: add foreign key FK_be_PostCategory_be_Categories---------------------
GO
ALTER TABLE [dbo].[be_PostCategory] ADD CONSTRAINT [FK_be_PostCategory_be_Categories] FOREIGN KEY ([CategoryID]) REFERENCES [dbo].[be_Categories] ([CategoryID])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 8 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 8 is completed with errors' SET NOEXEC ON END
GO
--step 9: alter column [Title] of table [dbo].[be_Posts]--------------------------------------------
GO
ALTER TABLE [dbo].[be_Posts] ALTER COLUMN [Title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 9 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 9 is completed with errors' SET NOEXEC ON END
GO
--step 10: alter column [Description] of table [dbo].[be_Posts]-------------------------------------
GO
ALTER TABLE [dbo].[be_Posts] ALTER COLUMN [Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 10 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 10 is completed with errors' SET NOEXEC ON END
GO
--step 11: alter column [Author] of table [dbo].[be_Posts]------------------------------------------
GO
ALTER TABLE [dbo].[be_Posts] ALTER COLUMN [Author] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 11 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 11 is completed with errors' SET NOEXEC ON END
GO
--step 12: alter column [Slug] of table [dbo].[be_Posts]--------------------------------------------
GO
ALTER TABLE [dbo].[be_Posts] ALTER COLUMN [Slug] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 12 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 12 is completed with errors' SET NOEXEC ON END
GO
--step 13: dbo.be_PostCategory: add foreign key FK_be_PostCategory_be_Posts-------------------------
GO
ALTER TABLE [dbo].[be_PostCategory] ADD CONSTRAINT [FK_be_PostCategory_be_Posts] FOREIGN KEY ([PostID]) REFERENCES [dbo].[be_Posts] ([PostID])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 13 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 13 is completed with errors' SET NOEXEC ON END
GO
--step 14: add index FK_CategoryID to table dbo.be_PostCategory-------------------------------------
GO
CREATE NONCLUSTERED INDEX [FK_CategoryID] ON [dbo].[be_PostCategory]([CategoryID]) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 14 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 14 is completed with errors' SET NOEXEC ON END
GO
--step 15: add index FK_PostID to table dbo.be_PostCategory-----------------------------------------
GO
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[be_PostCategory]([PostID]) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 15 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 15 is completed with errors' SET NOEXEC ON END
GO
--step 16: alter column [Author] of table [dbo].[be_PostComment]------------------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ALTER COLUMN [Author] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 16 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 16 is completed with errors' SET NOEXEC ON END
GO
--step 17: alter column [Email] of table [dbo].[be_PostComment]-------------------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ALTER COLUMN [Email] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 17 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 17 is completed with errors' SET NOEXEC ON END
GO
--step 18: alter column [Website] of table [dbo].[be_PostComment]-----------------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ALTER COLUMN [Website] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 18 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 18 is completed with errors' SET NOEXEC ON END
GO
--step 19: alter column [Comment] of table [dbo].[be_PostComment]-----------------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ALTER COLUMN [Comment] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 19 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 19 is completed with errors' SET NOEXEC ON END
GO
--step 20: alter column [Country] of table [dbo].[be_PostComment]-----------------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ALTER COLUMN [Country] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 20 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 20 is completed with errors' SET NOEXEC ON END
GO
--step 21: alter column [Ip] of table [dbo].[be_PostComment]----------------------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ALTER COLUMN [Ip] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 21 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 21 is completed with errors' SET NOEXEC ON END
GO
--step 22: dbo.be_PostComment: add foreign key FK_be_PostComment_be_Posts---------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ADD CONSTRAINT [FK_be_PostComment_be_Posts] FOREIGN KEY ([PostID]) REFERENCES [dbo].[be_Posts] ([PostID])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 22 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 22 is completed with errors' SET NOEXEC ON END
GO
--step 23: add index FK_PostID to table dbo.be_PostComment------------------------------------------
GO
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[be_PostComment]([PostID]) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 23 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 23 is completed with errors' SET NOEXEC ON END
GO
--step 24: alter column [NotifyAddress] of table [dbo].[be_PostNotify]------------------------------
GO
ALTER TABLE [dbo].[be_PostNotify] ALTER COLUMN [NotifyAddress] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 24 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 24 is completed with errors' SET NOEXEC ON END
GO
--step 25: dbo.be_PostNotify: add foreign key FK_be_PostNotify_be_Posts-----------------------------
GO
ALTER TABLE [dbo].[be_PostNotify] ADD CONSTRAINT [FK_be_PostNotify_be_Posts] FOREIGN KEY ([PostID]) REFERENCES [dbo].[be_Posts] ([PostID])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 25 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 25 is completed with errors' SET NOEXEC ON END
GO
--step 26: add index FK_PostID to table dbo.be_PostNotify-------------------------------------------
GO
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[be_PostNotify]([PostID]) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 26 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 26 is completed with errors' SET NOEXEC ON END
GO
--step 27: alter column [Tag] of table [dbo].[be_PostTag]-------------------------------------------
GO
ALTER TABLE [dbo].[be_PostTag] ALTER COLUMN [Tag] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 27 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 27 is completed with errors' SET NOEXEC ON END
GO
--step 28: dbo.be_PostTag: add foreign key FK_be_PostTag_be_Posts-----------------------------------
GO
ALTER TABLE [dbo].[be_PostTag] ADD CONSTRAINT [FK_be_PostTag_be_Posts] FOREIGN KEY ([PostID]) REFERENCES [dbo].[be_Posts] ([PostID])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 28 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 28 is completed with errors' SET NOEXEC ON END
GO
--step 29: add index FK_PostID to table dbo.be_PostTag----------------------------------------------
GO
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[be_PostTag]([PostID]) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 29 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 29 is completed with errors' SET NOEXEC ON END
GO
--step 30: dbo.be_Settings: drop primary key PK_be_Settings-----------------------------------------
GO
ALTER TABLE [dbo].[be_Settings] DROP CONSTRAINT [PK_be_Settings]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 30 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 30 is completed with errors' SET NOEXEC ON END
GO
--step 31: alter column [SettingName] of table [dbo].[be_Settings]----------------------------------
GO
ALTER TABLE [dbo].[be_Settings] ALTER COLUMN [SettingName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 31 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 31 is completed with errors' SET NOEXEC ON END
GO
--step 32: alter column [SettingValue] of table [dbo].[be_Settings]---------------------------------
GO
ALTER TABLE [dbo].[be_Settings] ALTER COLUMN [SettingValue] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 32 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 32 is completed with errors' SET NOEXEC ON END
GO
--step 33: drop schema blog-dampee------------------------------------------------------------------
GO
DROP SCHEMA [blog-dampee]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 33 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 33 is completed with errors' SET NOEXEC ON END
GO
--step 34: dbo.be_Settings: add primary key PK_be_Settings------------------------------------------
GO
ALTER TABLE [dbo].[be_Settings] ADD CONSTRAINT [PK_be_Settings] PRIMARY KEY CLUSTERED ([SettingName])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 34 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 34 is completed with errors' SET NOEXEC ON END
GO
----------------------------------------------------------------------
IF @@TRANCOUNT > 0 BEGIN COMMIT TRAN PRINT 'Synchronization is successfully completed.' END
GO
blog comments powered by Disqus

Who.I.am

Certified Umbraco Master, Part of Umbraco Certified partner comm-it, .Net and Azure developer, seo lover. Magician in my spare time.

Month List