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.
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.
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
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.
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!
If you spot any typos or issues with this article please leave a comment below or email me steve@SiempreSolutions.co.uk
Siempre Solutions Limited is a company registered in England and Wales with company number 09019307