How to mass unarchive topics with SQL

[Parisian]

First step is to move posts from the archive database back into the regular one:

insert into ipb.posts
select archive_id,archive_show_edited_by,archive_edit_time,
archive_author_id,archive_author_name,archive_show_signature,archive_show_emoticons,
archive_ip_address,archive_content_date,NULL,archive_content,archive_queued,
archive_topic_id,NULL,archive_is_first,archive_edit_name,MD5(CONCAT(archive_content_date,',',archive_id)),
archive_html_mode,archive_edit_reason,archive_bwoptions,0,0,NULL,NULL
from ipb_archive.forums_archive_posts

Replace ipb and ipb_archive with the correct database names for your installation.

You can restrict this to specific forums only by adding where archive_forum_id=X to the query (including the ones below).

The topics table need to be updated to reflect the change:

update ipb.topics set topic_archive_status=0

Then finally the archive table can be purged to save database space:

truncate table ipb_archive.forums_archive_posts

If you used a forum based restriction then adapt the query like so:

delete from ipb_archive.forums_archive_posts where archive_forum_id=X

Your topics should now be unarchived. If you're using any cache mechanism give those a refresh.

I've tested this on my very large boards where the combined posts table is over 9GB. Remember to check your MySQL query time limits so it doesn't get killed during the process.

[tAPir]

Thanks. Bookmarked in case I need it :)