Copying forums between databases in CommunityServer

I’ve used Telligent‘s CommunityServer product for about a year and a half now, and it’s proven to be a good (but not great) forums product. (Note: Version 2.0, recently released, seems to correct that issue.)

While doing some maintenance on the forums as installed for my baseball game, CSFBL, I deleted one forum that was still needed. To correct the problem, I restored a copy of a recent backup as a separate database and ran the following SQL scripts to copy the database from the backup to the production copy.

In the following source code, productionforum is the production database name, and productionforum_old is the old (backup) database name. Note that I haven’t moved every column over (such a read flags, attachment links, edit notes, etc.), but the process for those would be pretty much the same.

On to the SQL…

declare @sectionID int
set @sectionID = 0 --replace with the sectionID to move

--1. Move section
set identity_insert productionforum..cs_sections on

INSERT INTO productionforum..cs_Sections(SectionID, SettingsID, IsActive, ParentID, GroupID, Name, NewsgroupName, Description, DateCreated, Url, IsModerated, DaysToView, SortOrder, TotalPosts, TotalThreads, DisplayMask, EnablePostStatistics, EnableAutoDelete, EnableAnonymousPosting, AutoDeleteThreshold, MostRecentPostID, MostRecentThreadID, MostRecentThreadReplies, MostRecentPostSubject, MostRecentPostAuthor, MostRecentPostAuthorID, MostRecentPostDate, PostsToModerate, ForumType, IsSearchable, ApplicationType, ApplicationKey, PropertyNames, PropertyValues, Path)
select *
from productionforum_old..cs_sections
where sectionid = @sectionID

set identity_insert productionforum..cs_sections off

--2. Move permissions
INSERT INTO productionforum..cs_SectionPermissions(SettingsID, SectionID, RoleID, AllowMask, DenyMask)
select *
from productionforum_old..cs_sectionpermissions
where sectionid = @sectionID

--3. Move moderators
INSERT INTO productionforum..cs_Moderators(UserID, SectionID, DateCreated, EmailNotification, PostsModerated, SettingsID)
select *
from productionforum_old..cs_Moderators
where sectionid = @sectionID

--4. Move threads
set identity_insert productionforum..cs_threads on

INSERT INTO productionforum..cs_Threads(threadid, SectionID, UserID, PostAuthor, PostDate, ThreadDate, LastViewedDate, StickyDate, TotalViews, TotalReplies, MostRecentPostAuthorID, MostRecentPostAuthor, MostRecentPostID, IsLocked, IsSticky, IsApproved, RatingSum, TotalRatings, ThreadEmoticonID, ThreadStatus, SettingsID)
select threadid, SectionID, UserID, PostAuthor, PostDate, ThreadDate, LastViewedDate, StickyDate, TotalViews, TotalReplies, MostRecentPostAuthorID, MostRecentPostAuthor, MostRecentPostID, IsLocked, IsSticky, IsApproved, RatingSum, TotalRatings, ThreadEmoticonID, ThreadStatus, SettingsID
from productionforum_old..cs_threads
where sectionid = @sectionID

set identity_insert productionforum..cs_threads off

--5. Move posts
set identity_insert productionforum..cs_Posts on

INSERT INTO productionforum..cs_Posts(postid, ThreadID, ParentID, PostAuthor, UserID, SectionID, PostLevel, SortOrder, Subject, PostDate, IsApproved, IsLocked, IsIndexed, TotalViews, Body, FormattedBody, IPAddress, PostType, EmoticonID, PropertyNames, PropertyValues, SettingsID, AggViews, PostConfiguration)
select postid, ThreadID, ParentID, PostAuthor, UserID, SectionID, PostLevel, SortOrder, Subject, PostDate, IsApproved, IsLocked, IsIndexed, TotalViews, Body, FormattedBody, IPAddress, PostType, EmoticonID, PropertyNames, PropertyValues, SettingsID, AggViews, PostConfiguration
from productionforum_old..cs_Posts
where sectionid = @sectionID

set identity_insert productionforum..cs_Posts off