Restore Typo3 pages after they have been deleted (recursively)

Use your MySQL console or better PhpMyAdmin for this, but make a backup in advance with mysqldump or the export function. Ideally, back up the entire database in a backup.sql file!

We'll show you how to restore pages and their content (pages, tt_content) easily, quickly and reliably via SQL

    •  » 
    •  » Restore recursively deleted pages and content in Typo3 (all versions)
// navigation

Blog


// content

Feb, 10. 2018

We first need tstamp to determine what period of deleted items to recover

For this we put the following request to the database:

SELECT tstamp FROM pages WHERE deleted = 1 ORDER BY tstamp DESC;

This command lists the last changes made to all pages in the database. Since we noticed the deletion immediately, we should get here the relatively current timestamp. To go back one hour, we subtract from the 3600 value stored in seconds format. Our last timestamp is currently 1435760161, so we get 1435756561 as the last timestamp of recovery.

Now we execute these commands one after the other in the database:

UPDATE pages SET deleted = 0 WHERE deleted = 1 and tstamp > 1435756561;
UPDATE tt_content SET deleted = 0 WHERE deleted = 1 and tstamp > 1435756561;

We only need to check for deleted = 1 for performance reasons. Otherwise you would update all the pages that were edited in the last hour, which could take a few seconds in large systems. We also want to hedge against errors if the column has been expanded (for example, to a 2 for inactive pages).

The commands are safe to use, but we still recommend a full database backup in advance. Now check in the Typo3 backend, if everything is back and look in the frontend, if the sides are also displayed correctly. Be sure to review the two Typo3 tables and the history to see if any entries that were accidentally deleted have been recovered.

To convert your current time or any other to a timestamp, you can use this Use Timestamp Calculator. Keep in mind that the tstamp value is the same for all time zones, but of course the time and time zone makes a difference if you convert it to the timestamp (after all, 9am New York is not the same second as 9 am in Berlin).

Of course you can also select an arbitrary timeline:

UPDATE pages SET deleted = 0 WHERE deleted = 1 AND tstamp > 1435736561 AND tstamp < 1435746561;
UPDATE tt_content SET deleted = 0 WHERE deleted = 1 AND tstamp > 1435736561 AND tstamp < 1435746561;

In this query, all entries are set to "not deleted" between the two time values. Thank God Typo3 does not delete the entries directly, but simply sets the "deleted" column to 1, so sets a value from 0 to 1 and thus transmits the deleted status, so hide these pages and content completely (unlike hidden where these entries are still grayed out, at least in the backend).

MySQL commands with editing function and error detection for restoring pages and content in Typo3

mysql


# First we get the tstamp values for the last edits (deletions):
SELECT tstamp FROM pages WHERE deleted = 1 ORDER BY tstamp DESC;

# Alternatively, you can only read the values for the content from tt_content:
SELECT tstamp FROM tt_content WHERE deleted = 1 ORDER BY tstamp DESC;

# Now we update all deleted entries and set deleted to 0, the entries are immediately restored in backend and frontend
UPDATE pages SET deleted = 0 WHERE deleted = 1 AND tstamp> 1435736561 AND tstamp <1435746561; #Edit the value!
UPDATE tt_content SET deleted = 0 WHERE deleted = 1 AND tstamp> 1435736561 AND tstamp <1435746561; #Edit the value!
Rating: 4.5. 2 vote(s).
2 anonymous votes
No rating done at all.
Your vote was '' (0 of 5) You are an anonymous user.
You may log on to do personalized votings
Click the rating bar to rate this item Please log on to do ratings

Comments

comments powered by Disqus