New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

The Community Blog is a personal opinion of community members and by no means the official standpoint of DNN Corp or DNN Platform. This is a place to express personal thoughts about DNNPlatform, the community and its ecosystem. Do you have useful information that you would like to share with the DNN Community in a featured article or blog? If so, please contact .

The use of the Community Blog is covered by our Community Blog Guidelines - please read before commenting or posting.

Preparing for an upgrade

Today I had to upgrade an old DNN 3.2.2 multiportal installation to DNN 4.8.1. There are 3 major areas that changed in the past DotNetNuke versions:

  1. New contstraints and unique indexes on Files table
  2. DNN Membership provider
  3. Consolidation of http handlers in one assembly

In order to smoothly pass the third change, I always do a 2-step upgrade. First to DNN 4.5.5, then to the last available version, 4.8.1 in this case. The consolidation of http handlers happened in DNN 4.6.0.

When upgrading from DNN versions prior to 3.3.x / 4.3.x, the upgrade process will upgrade the data in the DNN Database from almost pure membership, to the more loosely coupled DNN Membership. Major changes are: switch from unique ApplicationID / Name per portal to one ApplicationId / name per DNN installation, and DNNMembership no longer uses the aspnet roles and profile tables. However, even though all data is upgraded, no essential data is deleted. In order to finalize the upgrade, you can manually delete the old data using the following SQL:

DELETE FROM dbo.aspnet_UsersInRoles

DELETE FROM dbo.aspnet_Profile

DELETE FROM dbo.aspnet_Roles

DELETE FROM dbo.aspnet_Membership
WHERE ApplicationId <> (SELECT ApplicationId FROM dbo.aspnet_Applications WHERE ApplicationName='DotNetNuke')

DELETE FROM dbo.spnet_Users 
WHERE ApplicationId <> (SELECT ApplicationId FROM dbo.aspnet_Applications WHERE ApplicationName='DotNetNuke')

You can run this script from SQL Management Studio, or from Host > SQL (select run as script). NB: Always make a backup of your database prior to executing this SQL!


The reason upgrades fail mostly have to do with the first change: new constraints and unique indexes on the files table. Problems arise from duplicate files in the files table, and files in the files table without a corresponding folder in the folders table. Even though the DNN upgrade sql tries to handle a few cases, in my experience, there are often cases that are not handled. Today i created the following sql which fixed all upgrade issues for me:



UPDATE [dbo].Folders
SET    FolderPath = FolderPath + '/'
WHERE  FolderPath <> '' AND RIGHT(FolderPath, 1) <> '/'


UPDATE [dbo].Files
SET    FolderID = FO.FolderID
FROM   [dbo].Files F, [dbo].Folders FO
WHERE F.Folder = FO.FolderPath
    AND ( ( F.PortalID = FO.PortalID ) OR ( F.PortalID is NULL AND  FO.PortalID is NULL ) )

INSERT INTO [dbo].[Folders] (PortalId, FolderPath)
SELECT     TOP 100 PERCENT F.PortalId, F.Folder
FROM         [dbo].Files F LEFT OUTER JOIN
                      [dbo].Folders FO ON F.FolderID = FO.FolderID
GROUP BY F.Folder, F.PortalId
ORDER BY F.PortalId, F.Folder


-- delete duplicates from folders table
DELETE FROM [dbo].Folders
  WHERE FolderId in (
    SELECT FolderId  
    FROM [dbo].Folders FO 
      join (SELECT PortalId, FolderPath
            FROM [dbo].Folders 
            GROUP BY PortalId,FolderPath 
            HAVING COUNT(*) > 1) DUP 
      ON DUP.FolderPath = FO.FolderPath AND (DUP.PortalId = FO.PortalId OR ( FO.PortalID is NULL AND  DUP.PortalID is NULL ))
      WHERE FO.FolderId NOT IN 
        (SELECT FolderId=MIN(FolderId)
         FROM [dbo].Folders 
         GROUP BY PortalId,FolderPath 
         HAVING COUNT(*) > 1))


Delete from Files 
WHERE FileID in (
    FROM [dbo].Files F 
      join (SELECT FolderId, FileName
            FROM [dbo].Files 
            GROUP BY folder,FolderId,FileName 
            HAVING COUNT(*) > 1) DUP
      on DUP.FolderId = F.FolderID and DUP.FileName = F.FileName
     (SELECT FileID = MIN(FileId)
            FROM [dbo].Files 
            GROUP BY folder,FolderId,FileName 
            HAVING COUNT(*) > 1))

ALTER TABLE [dbo].Files



This script will do the following:

  • make sure that all foldernames in the files table exist in the folders table (add if neccesary)
  • delete duplicates from folders table
  • delete duplicates from files table.

Of course.. there is a risk. Because the final step is to delete duplicate files, links to that files will seize to work. It will be necessary to do a manual check of your site, to see if this happened on your site.

You can run this script from SQL Server management studio, or from Host >SQL (if you are using a non standard databaseowner and/or an object qualifier, you will need to edit this script to meet your needs) and again: Always make a backup of your database prior to executing this SQL!

[edit]I edited this post as for some reason the script ended up all garbled[/edit]


Comment Form

Only registered users may post comments.


Aderson Oliveira (22)
Alec Whittington (11)
Alessandra Davies (3)
Alex Shirley (10)
Andrew Hoefling (3)
Andrew Nurse (30)
Andy Tryba (1)
Anthony Glenwright (5)
Antonio Chagoury (28)
Ash Prasad (37)
Ben Schmidt (1)
Benjamin Hermann (25)
Benoit Sarton (9)
Beth Firebaugh (12)
Bill Walker (36)
Bob Kruger (5)
Bogdan Litescu (1)
Brian Dukes (2)
Brice Snow (1)
Bruce Chapman (20)
Bryan Andrews (1)
cathal connolly (55)
Charles Nurse (163)
Chris Hammond (213)
Chris Paterra (55)
Clint Patterson (108)
Cuong Dang (21)
Daniel Bartholomew (2)
Daniel Mettler (181)
Daniel Valadas (48)
Dave Buckner (2)
David Poindexter (12)
David Rodriguez (3)
Dennis Shiao (1)
Doug Howell (11)
Erik van Ballegoij (30)
Ernst Peter Tamminga (80)
Francisco Perez Andres (17)
Geoff Barlow (12)
George Alatrash (12)
Gifford Watkins (3)
Gilles Le Pigocher (3)
Ian Robinson (7)
Israel Martinez (17)
Jan Blomquist (2)
Jan Jonas (3)
Jaspreet Bhatia (1)
Jenni Merrifield (6)
Joe Brinkman (274)
John Mitchell (1)
Jon Henning (14)
Jonathan Sheely (4)
Jordan Coopersmith (1)
Joseph Craig (2)
Kan Ma (1)
Keivan Beigi (3)
Kelly Ford (4)
Ken Grierson (10)
Kevin Schreiner (6)
Leigh Pointer (31)
Lorraine Young (60)
Malik Khan (1)
Matt Rutledge (2)
Matthias Schlomann (16)
Mauricio Márquez (5)
Michael Doxsey (7)
Michael Tobisch (3)
Michael Washington (202)
Miguel Gatmaytan (3)
Mike Horton (19)
Mitchel Sellers (40)
Nathan Rover (3)
Navin V Nagiah (14)
Néstor Sánchez (31)
Nik Kalyani (14)
Oliver Hine (1)
Patricio F. Salinas (1)
Patrick Ryan (1)
Peter Donker (54)
Philip Beadle (135)
Philipp Becker (4)
Richard Dumas (22)
Robert J Collins (5)
Roger Selwyn (8)
Ruben Lopez (1)
Ryan Martinez (1)
Sacha Trauwaen (1)
Salar Golestanian (4)
Sanjay Mehrotra (9)
Scott McCulloch (1)
Scott Schlesier (11)
Scott Wilkinson (3)
Scott Willhite (97)
Sebastian Leupold (80)
Shaun Walker (237)
Shawn Mehaffie (17)
Stefan Cullmann (12)
Stefan Kamphuis (12)
Steve Fabian (31)
Steven Fisher (1)
Tony Henrich (3)
Torsten Weggen (3)
Tycho de Waard (4)
Vicenç Masanas (27)
Vincent Nguyen (3)
Vitaly Kozadayev (6)
Will Morgenweck (40)
Will Strohl (180)
William Severance (5)
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out