These SQL scripts delete ALL Personally Identifiable Information from a Kentico database. Use this to clean up a database before pulling it from production to test or dev and still be GDPR-safe.

Anonymize non-admin users.sql

  UPDATE [dbo].[CMS_User]
  SET
	UserName=CONCAT('U',UserID),
	FirstName= CONCAT('U',UserID),
	MiddleName=NULL,
	LastName='Anoniem',
	FullName= CONCAT('U',UserID,' Anoniem'),
	Email=CONCAT('U',UserID,'@localhost'),
	UserPassword=CONVERT(NVARCHAR(32),HashBytes('SHA1', 'Super secret password'),2),
	UserPasswordFormat='SHA1'
  where UserName <> 'administrator' 
    AND UserName<>'public' 
    AND UserPrivilegeLevel<>3 -- skip global admins

Clear email.sql

-- Clear e-mail queue and everyting associated with it
Truncate table [CMS_AttachmentForEmail]
-- Delete user binding
Truncate table [CMS_EmailUser] 
-- Delete all attachments that have no bindings
DELETE FROM  CMS_EmailAttachment
-- Delete e-mails
DELETE FROM [CMS_Email]

Clear form data.sql

-- Clear out form data

declare @formname nvarchar(max),@tablename nvarchar(max), @id int
declare @tmp table
(
  id int not null,
  formname nvarchar(max),
  tablename nvarchar(max),
  primary key(id)
)

insert @tmp
select FormId, FormName, ClassTableName from CMS_Form
inner join CMS_Class class on class.ClassID=FormClassID

select top 1 @id=id, @formname=formname, @tablename=tablename from @tmp

while (@@rowcount > 0)
begin
  print 'Truncate '+@formname
  exec ('truncate table '+@tablename)
  delete from @tmp where id=@id
  select top 1 @id=id, @formname=formname, @tablename=tablename from @tmp
end

UPDATE CMS_Form SET FormItems=0

Purge all Kentico EMS data.sql

-- =========================================================================================== --
-- Purge all EMS data
alter table om_scorecontactrule nocheck constraint all
alter table om_account nocheck constraint all

declare @breakbyerror int = 0, @deleted_rows int = 1;

-- om_account
print convert(varchar(25), getdate(), 121) + ': Start deleting om_account records'
set @deleted_rows = 1;
while (@deleted_rows > 0 and @breakbyerror = 0)
begin
	begin try
	begin tran
	delete top(10000) from om_account
	set @deleted_rows = @@rowcount;
	print convert(varchar(25), getdate(), 121) + ':   ' + cast(@deleted_rows as varchar(max)) + ' records deleted'

	print convert(varchar(25), getdate(), 121) + ':   Commit transaction'
	commit tran
	end try
	begin catch
	select  ERROR_NUMBER() AS ErrorNumber
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_PROCEDURE() AS ErrorProcedure
			,ERROR_LINE() AS ErrorLine
			,ERROR_MESSAGE() AS ErrorMessage;
	  
	print convert(varchar(25), getdate(), 121) + ':   Rollback transaction'
	rollback tran
	set @breakbyerror = 1
	end catch
end

-- om_accountcontact
print convert(varchar(25), getdate(), 121) + ': Start deleting om_accountcontact records'
set @deleted_rows = 1;
while (@deleted_rows > 0 and @breakbyerror = 0)
begin
	begin try
	begin tran
	delete top(10000) from om_accountcontact
	set @deleted_rows = @@rowcount;
    print convert(varchar(25), getdate(), 121) + ':   ' + cast(@deleted_rows as varchar(max)) + ' records deleted'

	print convert(varchar(25), getdate(), 121) + ':   Commit transaction'
	commit tran
	end try
	begin catch
	select  ERROR_NUMBER() AS ErrorNumber
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_PROCEDURE() AS ErrorProcedure
			,ERROR_LINE() AS ErrorLine
			,ERROR_MESSAGE() AS ErrorMessage;
	  
	print convert(varchar(25), getdate(), 121) + ':   Rollback transaction'
	rollback tran
	set @breakbyerror = 1
	end catch
end

-- om_contactgroupmember 
print convert(varchar(25), getdate(), 121) + ': Start deleting om_contactgroupmember records'
set @deleted_rows = 1;
while (@deleted_rows > 0 and @breakbyerror = 0)
begin
	begin try
	begin tran
	delete top(10000) from om_contactgroupmember
	set @deleted_rows = @@rowcount;
	print convert(varchar(25), getdate(), 121) + ':   ' + cast(@deleted_rows as varchar(max)) + ' records deleted'

	print convert(varchar(25), getdate(), 121) + ':   Commit transaction'
	commit tran
	end try
	begin catch
	select  ERROR_NUMBER() AS ErrorNumber
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_PROCEDURE() AS ErrorProcedure
			,ERROR_LINE() AS ErrorLine
			,ERROR_MESSAGE() AS ErrorMessage;
	  
	print convert(varchar(25), getdate(), 121) + ':   Rollback transaction'
	rollback tran
	set @breakbyerror = 1
	end catch
end

-- om_membership 
print convert(varchar(25), getdate(), 121) + ': Start deleting om_membership records'
set @deleted_rows = 1;
while (@deleted_rows > 0 and @breakbyerror = 0)
begin
	begin try
	begin tran
	delete top(10000) from om_membership
	set @deleted_rows = @@rowcount;
	print convert(varchar(25), getdate(), 121) + ':   ' + cast(@deleted_rows as varchar(max)) + ' records deleted'

	print convert(varchar(25), getdate(), 121) + ':   Commit transaction'
	commit tran
	end try
	begin catch
	select  ERROR_NUMBER() AS ErrorNumber
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_PROCEDURE() AS ErrorProcedure
			,ERROR_LINE() AS ErrorLine
			,ERROR_MESSAGE() AS ErrorMessage;
	  
	print convert(varchar(25), getdate(), 121) + ':   Rollback transaction'
	rollback tran
	set @breakbyerror = 1
	end catch
end

-- om_scorecontactrule
print convert(varchar(25), getdate(), 121) + ': Start deleting om_scorecontactrule records'
set @deleted_rows = 1;
while (@deleted_rows > 0 and @breakbyerror = 0)
begin
	begin try
	begin tran
	delete top(10000) from om_scorecontactrule
	set @deleted_rows = @@rowcount;
	print convert(varchar(25), getdate(), 121) + ':   ' + cast(@deleted_rows as varchar(max)) + ' records deleted'

	print convert(varchar(25), getdate(), 121) + ':   Commit transaction'
	commit tran
	end try
	begin catch
	select  ERROR_NUMBER() AS ErrorNumber
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_PROCEDURE() AS ErrorProcedure
			,ERROR_LINE() AS ErrorLine
			,ERROR_MESSAGE() AS ErrorMessage;
	  
	print convert(varchar(25), getdate(), 121) + ':   Rollback transaction'
	rollback tran
	set @breakbyerror = 1
	end catch
end

-- om_activity 
print convert(varchar(25), getdate(), 121) + ': Start deleting om_activity records'

set @deleted_rows = 1;
while (@deleted_rows > 0 and @breakbyerror = 0)
begin
	begin try
	begin tran
	delete top(10000) from om_activity
	set @deleted_rows = @@rowcount;
	print convert(varchar(25), getdate(), 121) + ':   ' + cast(@deleted_rows as varchar(max)) + ' records deleted'

	print convert(varchar(25), getdate(), 121) + ':   Commit transaction'
	commit tran
	end try
	begin catch
	select  ERROR_NUMBER() AS ErrorNumber
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_PROCEDURE() AS ErrorProcedure
			,ERROR_LINE() AS ErrorLine
			,ERROR_MESSAGE() AS ErrorMessage;
	  
	print convert(varchar(25), getdate(), 121) + ':   Rollback transaction'
	rollback tran
	set @breakbyerror = 1
	end catch
end

-- om_contact 
print convert(varchar(25), getdate(), 121) + ': Start deleting om_contact records'

set @deleted_rows = 1;
while (@deleted_rows > 0 and @breakbyerror = 0)
begin
	begin try
	begin tran
	delete top(10000) from om_contact
	set @deleted_rows = @@rowcount;
	print convert(varchar(25), getdate(), 121) + ':   ' + cast(@deleted_rows as varchar(max)) + ' records deleted'

	print convert(varchar(25), getdate(), 121) + ':   Commit transaction'
	commit tran
	end try
	begin catch
	select  ERROR_NUMBER() AS ErrorNumber
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_PROCEDURE() AS ErrorProcedure
			,ERROR_LINE() AS ErrorLine
			,ERROR_MESSAGE() AS ErrorMessage;
	  
	print convert(varchar(25), getdate(), 121) + ':   Rollback transaction'
	rollback tran
	set @breakbyerror = 1
	end catch
end

alter table om_membership check constraint all
alter table om_scorecontactrule check constraint all
alter table om_account check constraint all


	print convert(varchar(25), getdate(), 121) + ': Rebuild indexes for table om_accountcontact '
	dbcc dbreindex(om_accountcontact,' ',90) with no_infomsgs
	print convert(varchar(25), getdate(), 121) + ': Rebuild indexes completed'

	print convert(varchar(25), getdate(), 121) + ': Rebuild indexes for table om_contactgroupmember '
	dbcc dbreindex(om_contactgroupmember,' ',90) with no_infomsgs
	print convert(varchar(25), getdate(), 121) + ': Rebuild indexes completed'

	print convert(varchar(25), getdate(), 121) + ': Rebuild indexes for table om_membership '
	dbcc dbreindex(om_membership,' ',90) with no_infomsgs
	print convert(varchar(25), getdate(), 121) + ': Rebuild indexes completed'

	print convert(varchar(25), getdate(), 121) + ': Rebuild indexes for table om_scorecontactrule '
	dbcc dbreindex(om_scorecontactrule,' ',90) with no_infomsgs
	print convert(varchar(25), getdate(), 121) + ': Rebuild indexes completed'

	print convert(varchar(25), getdate(), 121) + ': Rebuild indexes for table om_activity '
	dbcc dbreindex(om_activity,' ',90) with no_infomsgs
	print convert(varchar(25), getdate(), 121) + ': Rebuild indexes completed'

	print convert(varchar(25), getdate(), 121) + ': Rebuild indexes for table om_contact '
	dbcc dbreindex(om_contact,' ',90) with no_infomsgs
	print convert(varchar(25), getdate(), 121) + ': Rebuild indexes completed'

	print convert(varchar(25), getdate(), 121) + ': Update statistics ' 
	print ''
	exec sp_updatestats
	print convert(varchar(25), getdate(), 121) + ': Update statistics completed' 
	print ''

-- =========================================================================================== --

Purge e-commerce.sql

-- Purge E-Commerce
TRUNCATE TABLE COM_OrderItemSKUFile
DELETE COM_OrderItem
TRUNCATE TABLE COM_OrderStatusUser
DELETE COM_Order
DELETE COM_OrderAddress
TRUNCATE TABLE COM_ShoppingCartSKU
TRUNCATE TABLE COM_ShoppingCartCouponCode
DELETE COM_ShoppingCart
TRUNCATE TABLE COM_Wishlist
TRUNCATE TABLE COM_CustomerCreditHistory
DELETE COM_Address