I’m going to describe the SQL Script I used to migrate from Community Server 2007 to BlogEngine.NET. The complete script is available here, and as you’ll see there’s not very much to it. I walked through the script while writing up this post and successfully migrated a CS blog with it, but take it a step at a time and test each step until you’re comfortable with the results. Feel free to truncate the BE.NET tables and try again. Nothing to fear here.
The first thing to do is create an int column in be_posts to serve as a key for linking posts to post comments. I called the column cspostid.
alter table be_posts add cspostID int null
Now we’re ready to copy over the posts and comments for a single Community Server blog. I was migrating my main blog only. Its SectionID is “5”, so you’ll replace it with your blog’s SectionID. My CS database is dbvtcs2007, so you’ll need to change that as well. I’m not migrating ratings or other CS info like post aggviews. Just the basics.
insert into be_posts
(PostID, Title, PostContent, DateCreated, DateModified,
Author, IsPublished, cspostID)
select NEWID(), subject, body, postdate, postdate, ‘daveburke’,
1, postID from dbvtcs2007.dbo.cs_posts where sectionID = 5 and postlevel = 1
Now for the post comments. Note that I’m not migrating trackbacks. I didn’t care about them. If you do you’ll need to come up with something there. Sorry. You’ll need the FetchExtendedAttribute SQL function to extract the comment author and website info. I blogged about that here. I’m also plugging in an ‘firstname.lastname@example.org’ dummy email address. Registered CS users will have an email address you can retrieve and migrate if you want. Something else I didn’t concern myself with. You’ll start accumulating email addresses once you migrate to BE.NET, so not to worry.
insert into be_postcomment (postID, CommentDate, author, email, website,
select b.postid, c.postdate,
(dbvtcs2007.dbo.FetchExtendendAttributeValue(‘SubmittedUserName’, c.PropertyNames, c.PropertyValues)),
(dbvtcs2007.dbo.FetchExtendendAttributeValue(‘TitleUrl’, c.PropertyNames, c.PropertyValues)) ,
c.body, 1 from be_posts b inner join dbvtcs2007.dbo.cs_posts c
on b.cspostID = c.parentID where c.sectionid = 5 and c.postlevel = 2 and
c.posttype = 1 and c.applicationposttype <> 8
update be_postcomment set author = ‘YOUR NAME HERE’ where author is null
Categories are next and our last step. This is really easy. Again, we’re going to add an int field to key-up the categories during the migration. Change SectionID and “dbvtcs2007” to the name of your CS database and you should be good to go.
alter table be_categories add cscategoryID int null
insert into be_categories (categoryname, cscategoryID) select [name], categoryID
from dbvtcs2007.dbo.cs_post_categories c where c.isenabled = 1 and sectionID = 5
update be_categories set cscategoryID = c.categoryID from dbvtcs2007.dbo.cs_post_categories c, be_categories b
where b.categoryname = c.name
insert into be_postcategory (postid, categoryid) select b.postid, bc.categoryID from be_categories bc inner join
dbvtcs2007.dbo.cs_post_categories c on c.categoryID = bc.cscategoryID
inner join dbvtcs2007.dbo.cs_posts_incategories cic on c.categoryID = cic.categoryID
inner join dbvtcs2007.dbo.cs_posts p on cic.postid = p.postid
inner join be_posts b on b.cspostID = p.postID
I’ll see you on the other side…