Since we are on the 2012 forum, here is a LEAD version
😎
USE TEEST;
GO
SET NOCOUNT ON;

DECLARE @ProductCode VARCHAR(256) = 'Organitation/My Companies/company...

Piling on
😎

This returns a DATETIME with time set at midnight:
SELECT CONVERT(DATETIME,CONVERT(DATE,GETDATE()),0)

This should get you passed this hurdle
😎

IF OBJECT_ID('tempdb..#urlset') IS NOT NULL
drop table #urlset
CREATE TABLE #UrlSet(
[Tip] [smallint] NOT NULL,
[xml] NULL

You can use the DelimitedSplit8K or the DelimitedSplit8K_LEAD functions
😎

DECLARE @ProductCode VARCHAR(256) = 'Organitation/My Companies/company ABC/My company divisoin/my company place/department/place of work 234/678/98as/sdfd45679'

Further on Luis's respond, what have you tried so far?
😎

If you want to stay safe then check the the actual length of the values, here is an example
😎
IF OBJECT_ID(N'DBO.TXX') IS NOT NULL DROP TABLE...

Does the T-SQL TRY/CATCH only suppress the errors?
😎

Further on the subject, if the validity of the backups isn't tested by restoring then there is a potential risk, how do you validate that the backups can actually be...