How to delete SQL Server whole or partial historical call archive and re-import it from existing "Storico" file

Applies to: 

Imagicle Call Analytics Standard/Enterprise

Description:

How to remove calls from SQL Server database and reimport them from "Storico" text file

Solution: UC Suite starting from 2021.3.1

1. Stop Blue's Service Host in Billing|Administrative pages|Service Manager

2. open Microsoft SQL Manager Studio (make a backup copy of the database)

3. Run "New Query" and copy the following query, by changing the @startDate and @endDate according to the period you wish to delete. If you need to fully erase historical archive, please set @startDate to year 2000 and @endDate to year 2030.

Please keep in mind that dates are based on UTC Time Zone.

/* WARNING: BEFORE YOU RUN THIS SCRIPT TAKE A BACKUP OF THE DATABASE! */

-- The start date (included) of the deletion
DECLARE @startDate DATETIME = '2020-01-01 00:00:00.000'

-- The end date (excluded) of the deletion
DECLARE @endDate DATETIME = '2020-02-01 00:00:00.000'

-- Number of records that must be deleted in each chunk
DECLARE @recordsPerChunk INT = 1000

----- DO NOT EDIT BELOW THIS LINE -----
DECLARE @recordsNumber INT
DECLARE @startDateString VARCHAR(50) = CONVERT(VARCHAR(50), @startDate, 120)
DECLARE @endDateString VARCHAR(50) = CONVERT(VARCHAR(50), @endDate, 120)

SET ROWCOUNT @recordsPerChunk

RAISERROR('Deleting [BibCalls] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
    SELECT @recordsNumber = COUNT(*) FROM [BibCalls] WHERE [UtcStartDateTime] >= @startDate AND [UtcStartDateTime] < @endDate
    RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT

    BEGIN TRANSACTION
    DELETE FROM [BibCalls] WHERE [UtcStartDateTime] >= @startDate AND [UtcStartDateTime] < @endDate
    COMMIT TRANSACTION
END

RAISERROR('Deleting [Scarti] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
    SELECT @recordsNumber = COUNT(*) FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT

    BEGIN TRANSACTION
    DELETE FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    COMMIT TRANSACTION
END

SET ROWCOUNT 0

4. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro, from file StoricoSITE.txt, select all the lines you need to reimport and hit CTRL+C on the keyboard

5. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro open bluesrecsite.txt and use CTRL+V to paste in the previously copied strings

6. Start Blue's Service Host in Billing|Administrative pages|Service Manager and wait until miner has finished importing all calls

Solution: UC Suite starting from 2020.3.1 up to 2021.1.1

1. Stop Blue's Service Host in Billing|Administrative pages|Service Manager

2. open Microsoft SQL Manager Studio (make a backup copy of the database)

3. Run "New Query" and copy the following query, by changing the @startDate and @endDate according to the period you wish to delete. If you need to fully erase historical archive, please set @startDate to year 2000 and @endDate to year 2030.

/* WARNING: BEFORE YOU RUN THIS SCRIPT TAKE A BACKUP OF THE DATABASE! */

-- The start date (included) of the deletion
DECLARE @startDate DATETIME = '2020-01-01 00:00:00.000'

-- The end date (excluded) of the deletion
DECLARE @endDate DATETIME = '2020-02-01 00:00:00.000'

-- Number of records that must be deleted in each chunk
DECLARE @recordsPerChunk INT = 1000

----- DO NOT EDIT BELOW THIS LINE -----
DECLARE @recordsNumber INT
DECLARE @startDateString VARCHAR(50) = CONVERT(VARCHAR(50), @startDate, 120)
DECLARE @endDateString VARCHAR(50) = CONVERT(VARCHAR(50), @endDate, 120)

SET ROWCOUNT @recordsPerChunk

RAISERROR('Deleting [BibCalls] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
    SELECT @recordsNumber = COUNT(*) FROM [BibCalls] WHERE [StartDate] >= @startDate AND [StartDate] < @endDate
    RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT

    BEGIN TRANSACTION
    DELETE FROM [BibCalls] WHERE [StartDate] >= @startDate AND [StartDate] < @endDate
    COMMIT TRANSACTION
END

RAISERROR('Deleting [Scarti] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
    SELECT @recordsNumber = COUNT(*) FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT

    BEGIN TRANSACTION
    DELETE FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    COMMIT TRANSACTION
END

SET ROWCOUNT 0

4. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro, from file StoricoSITE.txt, select all the lines you need to reimport and hit CTRL+C on the keyboard

5. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro open bluesrecsite.txt and use CTRL+V to paste in the previously copied strings

6. Start Blue's Service Host in Billing|Administrative pages|Service Manager and wait until miner has finished importing all calls

Solution: UC Suite previous 2020.3.1 

1. Stop Blue's Service Host in Billing|Administrative pages|Service Manager

2. open Microsoft SQL Manager Studio (make a backup copy of the database)

3. Run "New Query" and copy the following query, by changing the @startDate and @endDate according to the period you wish to delete. If you need to fully erase historical archive, please set @startDate to year 2000 and @endDate to year 2030.

/* WARNING: BEFORE YOU RUN THIS SCRIPT TAKE A BACKUP OF THE DATABASE! */

-- Declare the date after which the calls will be canceled

-- The start date (included) of the deletion
DECLARE @startDate DATETIME = '2020-01-01 00:00:00.000'


-- The end date (excluded) of the deletion
DECLARE @endDate DATETIME = '2020-02-29 00:00:00.000'

-- Declare the site to be canceled 
DECLARE @sito VARCHAR(50)
set @sito = 'SITE'


-- Number of records that must be deleted in each chunk
DECLARE @recordsPerChunk INT = 1000

----- DO NOT EDIT BELOW THIS LINE -----
DECLARE @recordsNumber INT
DECLARE @startDateString VARCHAR(50) = CONVERT(VARCHAR(50), @startDate, 120)
DECLARE @endDateString VARCHAR(50) = CONVERT(VARCHAR(50), @endDate, 120)

SET ROWCOUNT @recordsPerChunk

RAISERROR('Deleting [ChiamateInterne] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
        SELECT @recordsNumber = COUNT(*) FROM [ChiamateInterne] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
        RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT
        
        BEGIN TRANSACTION
        DELETE FROM [ChiamateInterne] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
        COMMIT TRANSACTION
END


RAISERROR('Deleting [Chiamate] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
        SELECT @recordsNumber = COUNT(*) FROM [Chiamate] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
        RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT
        
        BEGIN TRANSACTION
        DELETE FROM [Chiamate] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
        COMMIT TRANSACTION
END

RAISERROR('Deleting [Scarti] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
    SELECT @recordsNumber = COUNT(*) FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT

    BEGIN TRANSACTION
    DELETE FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    COMMIT TRANSACTION
END

SET ROWCOUNT 0

( change the dates of the above query according to the period you wish to delete)

4. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro, from file StoricoSITE.txt, select all the lines you need to reimport and hit CTRL+C on the keyboard

5. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro open bluesrecsite.txt and use CTRL+V to paste in the previously copied strings

6. Start Blue's Service Host in Billing|Administrative pages|Service Manager and wait until miner has finished importing all calls