As the title already says… I accidentally deleted a board within Focalboard.
I use Focalboard on Windows 7, on-premise.
I still have access to focalboard.db - and when opening the file with an SQLite Program, I can still see the previous steps (all comments, ticks, pictures, etc.) within the database. Focalboard.db File is also 1,21mb big - so the files are still here.
Is there any chance I can reverse the last step of deleting the board?
Any help would be very much appreciated! Thank you!
First of all, please make an offline copy of your Focalboard.db and store it in a safe place, just in case we ruin something by trying to recreate your data.
As you could already see, the data is still there and it’s only soft-deleted, but I’m not aware of an easy way to just undo the last step, but if you remember when you deleted the board, it should be easy to restore the data.
When you open the Focalboard.db file with an sqlite editor (I’m using sqlite3 here on Linux), you can toggle the output mode to see also the column headings and when doing that, you will see that there is an attribute called delete_at. This indicates a timestamp of deletion in the unix timestamp format (microseconds since 1970). So let’s say you deleted the board 24hrs ago, then you would find out the unix timestamp of this very minute and subtract 86400 seconds from it and this would be the starting time of your delete action (add three zeros at the end, because we’re in microseconds here).
Example:At the time of this writing, the unix timestamp is 1659556805, so yesterday at the same time would be 1659556805 - 86400 = 1659470405 * 1000 (because we need microseconds) = 1659470405000.
In the sqlite browser, you can then search for all the data deleted after this specific point in time, by running queries like:
SELECT * FROM blocks where delete_at > 1659470405000;
You will then see lots of data being displayed and if this is the data you’re interested in, you can proceed with bringing it back to life by just setting the delete_at attribute to 0 (which means, it never has been deleted).
Do do that, run the following query:
UPDATE blocks set delete_at=0 WHERE delete_at > 1659470405000;
The following tables in the Focalboard.db database have a delete_at column, you’d have to do that for all of them (not just the table blocks as in my example above):
I did reproduce that on my system and it seems that the deleted board is being removed from the blocks table and being written to the blocks_history table, so on your system, you should see the following entry with the name of the board you deleted:
sqlite> select id,insert_at,type,title from blocks_history where type='board' and delete_at > 0;
+-----------------------------+-------------------------+-------+------------+
| id | insert_at | type | title |
+-----------------------------+-------------------------+-------+------------+
| buws7fmw9xiy55fg8t747kooe4c | 2022-08-04 03:46:25.460 | board | mein board |
+-----------------------------+-------------------------+-------+------------+
What I did to get it working was to copy over this record to the blocks table (to make it active again) and to reset the delete_at column afterwards:
insert into blocks select * from blocks_history where id='buws7fmw9xiy55fg8t747kooe4c';
update blocks set delete_at=0 where id='buws7fmw9xiy55fg8t747kooe4c';
If that worked and restored your board, you should also clean the entry in the blocks_history table then, not sure if it’s OK that the same ID exists in both tables:
delete from blocks_history where id='buws7fmw9xiy55fg8t747kooe4c';
This got my previously deleted board restored, I hope this also works for you.
Awesome, that’s good to hear
Make sure you start making backups of your Focalboard.db file so when that happens again, you can restore out of the backup and do not need to go through the hassle of sqlite command lines
