1 Introduction

I have a client that handles sensitive form submissions via Umbraco Forms – they are submitted straight to SalesForce (which is incredibly simple to setup in Umbraco Forms) but have found when there are issues it’s been handy to have the backup of the submissions that are stored in Umbraco Forms.

2 Ah - but we don't have a Scheduler in SQL Server Express!

These submissions are sensitive though they only want to keep them as a backup for seven days. I wrote them a quick stored procedure to clean up the entries and thought it might be useful to others. The server they host on only uses SQL Express so there is no in-built SQL DB scheduler.

To get around the limitation of no scheduled SQL tasks you can use the Task Scheduler built into Windows server. I’ve created a simple batch file in a directory that this calls. This batch script then calls the SQL Server stored procedure below to run the clear down script. You can do anything you like in these scripts – we run DB backups and zip the live website folder and then run a subsequent script that stores these in an off line storage.

3 The Stored Procedure

First job is to create the sproc. I’ve guessed at the Umbraco Forms table structure and this seems to work but let me know in the comments below if you think this can be improved and obviously if you have mission critical data please backup your DB before testing / implementing.

CREATE PROCEDURE p_DeleteOldFormSubmissions 
    @Days int = 7 
AS
BEGIN
    DECLARE @DeleteDate AS DateTime
    SET @DeleteDate = DATEADD(DAY, -@Days, GETDATE())
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- SELECT @DeleteDate; 
    -- databit
    DELETE db
    FROM dbo.[UFRecords] AS r 
    INNER JOIN dbo.[UfRecordFields] AS rf 
        ON r.Id = rf.Record
    INNER JOIN dbo.[UFRecordDataBit] AS db
        ON rf.[Key]    = db.[Key]
        WHERE r.[Updated] < @DeleteDate;
        
    -- datetime
    DELETE dt
    FROM dbo.[UFRecords] AS r 
    INNER JOIN dbo.[UfRecordFields] AS rf 
        ON r.Id = rf.Record
    INNER JOIN dbo.[UFRecordDataDateTime] AS dt
        ON rf.[Key]    = dt.[Key]
        WHERE r.[Updated] < @DeleteDate;

    -- integer
    DELETE di
    FROM dbo.[UFRecords] AS r 
    INNER JOIN dbo.[UfRecordFields] AS rf 
        ON r.Id = rf.Record
    INNER JOIN dbo.[UFRecordDataInteger] AS di
        ON rf.[Key]    = di.[Key]
        WHERE r.[Updated] < @DeleteDate;

    -- longstring
    DELETE dls
    FROM dbo.[UFRecords] AS r 
    INNER JOIN dbo.[UfRecordFields] AS rf 
        ON r.Id = rf.Record
    INNER JOIN dbo.[UFRecordDataLongString] AS dls
        ON rf.[Key]    = dls.[Key]
        WHERE r.[Updated] < @DeleteDate;

    -- string
    DELETE ds
    FROM UfRecords AS r 
    INNER JOIN dbo.UfRecordFields AS rf 
        ON r.Id = rf.Record
    INNER JOIN dbo.UFRecordDataString AS ds
        ON rf.[Key]    = ds.[Key]
        WHERE r.[Updated] < @DeleteDate;
    
    -- finally the record
    DELETE r    
    FROM UfRecords AS r 
    WHERE r.[Updated] < @DeleteDate;
END
GO

Probably worth testing this manually first – call it like you would any sproc pass in how many days to keep (seven in the example below).

exec dbo.p_DeleteOldFormSubmissions 7

4 Batch File

Then you need to create a batch file. I called this formcleanup.bat. You can see we pass in the "days" parameter. Remember to change this script to match your DB name – this server uses windows auth, if yours doesn’t you may need to add db credentials here.

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC [INSERTDBNAMEHERE].dbo.[p_DeleteOldFormSubmissions] @Days=7;"

I usually test the batch file from a command line too.

5. Schedule that Task!

Now just set up a new task in the windows Task Scheduler. Get it to execute a program and select your batch. Choose a suitable schedule. Remember to check that the task is set to "Run whether the user is logged in or not" – you’ll need to provide a user password that has the relevant privileges to run this batch. And there you are!