I finally set aside some time to finish my conversion from Community Server 2007 over to BlogEngine.NET 1.4. The main problem I was having was getting my old posts/comments/categories/etc out of CS2007 and into BE.NET. The built-in export tool wasn't working and would fail with several hard to decipher errors. I even tried using the BlogML tool to export all of my data and that wasn't working either (thanks Keyvan Nayyeri for trying to help!). Just by chance, I was reading Dave Burke's blog and noticed that he just finished his conversion from CS2007 to BE.NET. I sent him an email asking how he was able to get all of his data out of CS2007 and he told he did it manually via SQL. He was even kind enough to send me some of the queries he used and it worked!
The first thing you have to do is add a new column to the be_Posts table that allows you to link posts to post comments:
ALTER TABLE be_Posts ADD csPostID int null
Next, we copy over the posts:
INSERT INTO be_Posts
(PostID, Title, PostContent, DateCreated, DateModified, Author, IsPublished, cspostID)
SELECT
NEWID(),
subject,
body,
postdate,
postdate,
'drohm',
1,
postID
FROM
SQL2005_347492_dougrohm.dbo.cs_Posts
WHERE
sectionID = 3 AND
postlevel = 1
You'll need to change 'drohm' to the name of your blog in CS2007. The section id for my blog in CS2007 is '3'. This most likely is different for you and you'll need to get that value and enter it here. Also, change 'SQL2005_347492_dougrohm' to whatever the name of your CS2007 database is named.
Next, we can migrate over post comments:
INSERT INTO be_PostComment
(postID, CommentDate, author, email, website, comment, isapproved)
SELECT
b.postid,
c.postdate,
(SQL2005_347492_dougrohm.dbo.FetchExtendendAttributeValue('SubmittedUserName', c.PropertyNames, c.PropertyValues)),
'doug@dougrohm.com',
(SQL2005_347492_dougrohm.dbo.FetchExtendendAttributeValue('TitleUrl', c.PropertyNames, c.PropertyValues)),
c.body,
1
FROM
be_posts b INNER JOIN SQL2005_347492_dougrohm.dbo.cs_posts c
ON
b.csPostID = c.parentID
WHERE
c.sectionid = 3 AND
c.postlevel = 2 AND
c.posttype = 1 AND
c.applicationposttype <> 8
Trackbacks are not being migrated here so if you want to capture that information you'll need to update this query. Also, you'll need the FetchExtendedAttribute SQL function to extract the comment author and website info. You can find that great tool here. One other note, I'm using my email address for the email field. On my CS2007 blog I didn't allow users to register. If you want to migrate this information, you'll need to modify that here as well.
I then update the author field for any comments that don't have a value for the name:
UPDATE
be_PostComment
SET
author = 'Douglas Rohm'
WHERE
author is null
The last step is to migrate over categories:
ALTER TABLE be_Categories ADD csCategoryID int null
INSERT INTO be_Categories
(categoryname, csCategoryID) select [name], categoryID
FROM
SQL2005_347492_dougrohm.dbo.cs_post_categories c
WHERE
c.isenabled = 1 AND
sectionID = 3
UPDATE
be_Categories
SET
csCategoryID = c.categoryID
FROM
SQL2005_347492_dougrohm.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 SQL2005_347492_dougrohm.dbo.cs_post_categories c ON c.categoryID = bc.csCategoryID
INNER JOIN SQL2005_347492_dougrohm.dbo.cs_posts_incategories cic ON c.categoryID = cic.categoryID
INNER JOIN SQL2005_347492_dougrohm.dbo.cs_posts p ON cic.postid = p.postid
INNER JOIN be_posts b ON b.cspostID = p.postID
Like earlier, I'm adding a column to the be_Categories table so we can link the two. Be sure to change the section id and 'SQL2005_347492_dougrohm' to the name of your CS2007 database and you should be all set.
Now that I have BlogEngine.NET 1.4 running, I have to say it's very fast and super easy to configure. Kudos to the BE.NET team.
Subscribe