Ola Hallengren Scripts – Uninstall

Background

Ola Hallengren’s scripts has everything.

But, unfortunately I just installed it on the master DB.

But, our policy is to install it against a user database (AdminDB, UtilDB) or whatever you call it.

Here is a Stored Procedure that will uninstall it and allow you to go ahead it and start a new install.

Code

Stored Procedure

[dbo].[sp_OlaHallengrenScriptsUninstall]




use [master]
go

if object_id('[dbo].[sp_OlaHallengrenScriptsUninstall]') is null
begin

	exec('create procedure [dbo].[sp_OlaHallengrenScriptsUninstall] as ')

end
go


alter procedure [dbo].[sp_OlaHallengrenScriptsUninstall] 
(
	  @scriptOnly bit = 1
	, @categoryID int = 3
)
as

begin

	set nocount on;

	declare @tblJob TABLE
	(	
		  [id]				int not null identity(1,1)
		, [jobID]			uniqueIdentifier
		, [name]			sysname
		, [description]		varchar(600)
	)


	declare @tblObject TABLE
	(	
		  [id]			int not null identity(1,1)

		, [schema]		sysname

		, [name]		sysname

		, [objectName]
			as quoteName([schema]) + '.' + quoteName([name])

		, [type]		sysname

		, [type_desc]	sysname

		, [dropTarget] as
			case [type]
					when 'U' then 'TABLE'
					when 'P' then 'PROCEDURE'
			end
	)


	declare @tblObjectSought TABLE
	(
		  [id]     int not null identity(1,1)
		, [schema] sysname
		, [name]   sysname
	)

	declare @jobDescription sysname
	declare @idJob			int
	declare @idJobMax		int
	declare @jobName		sysname


	declare @SQL_FORMAT_DELETE_JOB		nvarchar(600)
	declare @SQL_FORMAT_DROP_OBJECT		nvarchar(600)

	declare @idObject			int
	declare @idObjectMax		int
	declare @objectSchema		sysname
	declare @objectName			sysname
	declare @objectType			sysname
	declare @objectTypeLiteral	sysname
	declare @dropTarget			varchar(60)

	declare @sql			nvarchar(4000)

	declare @CHAR_TAB	  char(1)
	declare @CHAR_NEWLINE char(2)

	set @CHAR_TAB = char(9)
	set @CHAR_NEWLINE = char(13)+ char(10)

	set @SQL_FORMAT_DELETE_JOB = 'if exists ( select * from [msdb].[dbo].[sysjobs] where [name] = ''#JOB_NAME#'' ) '
									+ ' begin '
									+ '		EXEC [msdb].[dbo].[sp_delete_job]  @job_name = ''#JOB_NAME#'' '
									+ ' end '

	set @SQL_FORMAT_DROP_OBJECT = 'if object_id(''[#OBJECT_NAME#]'') is not null begin DROP [#TYPE_DESC] [#OBJECT_NAME#] end'

	set @jobDescription = 'Source: https://ola.hallengren.com'


	/*
		Prepare a list of Ola's Objects
	*/
	insert into @tblObjectSought
	(
		  [schema]
		, [name]  
	)
	select 'dbo', 'CommandLog'
	union
	select 'dbo', 'CommandExecute'
	union
	select 'dbo', 'DatabaseBackup'
	union
	select 'dbo', 'DatabaseIntegrityCheck'
	union
	select 'dbo', 'IndexOptimize'

	/*
		Deduce Ola's DB Job
			a) Based on Job's Description 
	*/
	insert into @tblJob
	(	
		  [jobID] 
		, [name]	
		, [description]
	)
	select 
			  tblSJ.[job_id]
			, tblSJ.[name]
			, tblSJ.[description]
	from   [msdb].[dbo].[sysjobs] tblSJ

	where  (
				( tblSJ.[description] = @jobDescription )
			)

	and		(
				( tblSJ.originating_server_id = 0 )
			)

	set @idJob =1
	set @idJobMax = ( select max([id]) from @tblJob )

	print '--Jobs'
	print replicate('-', 4)

	/*
		Ola's DB Job
			a) Script Job Deletion
	*/
	while (@idJob <= @idJobMax)
	begin

		select 
				@jobName = [name]
		from    @tblJob 
		where   [id] = @idJob	

		set @sql = replace(@SQL_FORMAT_DELETE_JOB, '#JOB_NAME#', @jobName)

		print @sql

		if (@scriptOnly = 0)
		begin

			--print @CHAR_TAB + 'Executing ' + @sql

			exec(@sql)

			print @CHAR_NEWLINE

		end

		set @idJob = @idJob + 1

	end

	/*
		Ola's DB Job
			a) Get List of Objects
	*/
	insert into @tblObject
	(	
		  [schema]	
		, [name]	
		, [type]	
		, [type_desc]
	)

	select 
			  tblSS.[name]
			, tblSO.[name]
			, tblSO.[type]
			, tblSO.[type_desc]

	from   [sys].[objects] tblSO

	inner join [sys].[schemas] tblSS

			on tblSO.[schema_id] = tblSS.[schema_id]

	inner join @tblObjectSought tblOS

			on   tblSS.[name] = tblOS.[schema]
			and  tblSO.[name] = tblOS.[name]

	set @idObject =1
	set @idObjectMax = ( select max([id]) from @tblObject )

	/*
		Ola's DB Job
			a) Script Object Deletion
	*/
	print '--Objects'
	print replicate('-', 9)

	while (@idObject <= @idObjectMax)
	begin

		select 
				  @objectName = [objectName]
				, @objectTypeLiteral = [type_desc]
				, @dropTarget = [dropTarget]

		from    @tblObject
		where   [id] = @idObject

		set @sql = @SQL_FORMAT_DROP_OBJECT

		set @sql = replace(@sql, '[#TYPE_DESC]', @dropTarget)

		set @sql = replace(@sql, '[#OBJECT_NAME#]', @objectName)

		print @sql

		if (@scriptOnly = 0)
		begin

			exec(@sql)

			print @CHAR_NEWLINE

		end

		set @idObject = @idObject + 1

	end


end
go

exec sys.sp_MS_marksystemobject '[dbo].[sp_OlaHallengrenScriptsUninstall]'
go


Lab

Invoke



declare @scriptOnly bit
declare @categoryID int

set @scriptOnly = 0
set @categoryID = 3

begin tran

   exec [dbo].[sp_OlaHallengrenScriptsUninstall] 
	  @scriptOnly = @scriptOnly
	, @categoryID = @categoryID

commit tran


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s