DEV Community

Cover image for DNN CMS: Client Website Restoration Script
Will Strohl
Will Strohl

Posted on

DNN CMS: Client Website Restoration Script

#dnncms #dotnetnuke #aspnet #sqlserver

SQL Script to Prepare a Newly Restored DNN Site for Local Development

When restoring a DNN site locally — whether for debugging, development, or testing — there are a few common pain points:

  • The restored database still has old portal aliases, so the site might not load with your local URL.
  • The DefaultPortalAlias setting points to the production domain.
  • The SMTP settings are still pointing to production mail servers (and may accidentally send emails).
  • The Schedule table may still be tied to specific production servers.

This script solves those problems in one go.

What This Script Does

  • Sets your new local PortalAlias and makes it primary.
  • Updates the DefaultPortalAlias in PortalSettings.
  • Optionally changes SMTP settings to use localhost so no emails go out during development.
  • Clears Schedule.Servers so scheduled jobs run locally.
  • Outputs relevant rows from PortalAlias, PortalSettings, HostSettings, and Schedule so you can verify changes.

How to Use

  1. Back up your database first — this script makes direct updates.
  2. Edit the variables at the top:
    • @PortalID – The ID of the portal/site you’re updating.
    • @PortalAlias – Your new local domain (e.g., mysite.local).
    • @IsForLocalDev – Set to 1 for local installs (changes SMTP to localhost), 0 for cloning between production servers.
  3. Run the script against your restored DNN database.

The Script 

 sql /* Prepares a newly restored local client site for running locally. */ DECLARE @PortalID INT = 0; -- the PortalID for the above domain name (IMPORTANT for DNN instances that have multiple sites hosted) DECLARE @PortalAlias NVARCHAR(255) = N''; -- new domain name DECLARE @IsForLocalDev BIT = 1; -- 1 for installing locally, 0 for production-to-production, cloning, etc. BEGIN TRAN UPDATE [dbo].[PortalAlias] SET [IsPrimary] = 0 WHERE [PortalID] = @PortalID; IF NOT EXISTS(SELECT 1 FROM [dbo].[PortalAlias] WHERE [PortalID] = @PortalID AND [HttpAlias] = @PortalAlias) BEGIN INSERT INTO [dbo].[PortalAlias] ([PortalID],[HTTPAlias],[CreatedByUserID],[CreatedOnDate],[LastModifiedByUserID],[LastModifiedOnDate],[BrowserType],[IsPrimary]) VALUES (@PortalID,@PortalAlias,-1,GETDATE(),-1,GETDATE(),N'Normal',1); END ELSE BEGIN UPDATE [dbo].[PortalAlias] SET [IsPrimary] = 1 WHERE [PortalID] = @PortalID AND [HTTPAlias] = @PortalAlias; END UPDATE [dbo].[PortalSettings] SET [SettingValue] = @PortalAlias, [LastModifiedByUserID] = -1, [LastModifiedOnDate] = GETDATE() WHERE [PortalID] = @PortalID AND [SettingName] = N'DefaultPortalAlias'; IF NOT EXISTS (SELECT 1 FROM [dbo].[HostSettings] WHERE [SettingValue] = N'localhost') AND @IsForLocalDev = 1 BEGIN UPDATE [dbo].[HostSettings] SET [SettingValue] = 0, [LastModifiedByUserID] = -1, [LastModifiedOnDate] = GETDATE() WHERE [SettingName] = N'SMTPAuthentication'; UPDATE [dbo].[HostSettings] SET [SettingValue] = N'localhost', [LastModifiedByUserID] = -1, [LastModifiedOnDate] = GETDATE() WHERE [SettingName] = N'SMTPServer'; UPDATE [dbo].[HostSettings] SET [SettingValue] = N'N', [LastModifiedByUserID] = -1, [LastModifiedOnDate] = GETDATE() WHERE [SettingName] = N'SMTPEnableSSL'; END UPDATE [dbo].[Schedule] SET [Servers] = NULL WHERE NOT [Servers] IS NULL; COMMIT TRAN SELECT pa.* FROM [dbo].[PortalAlias] pa WHERE pa.[PortalID] = @PortalID ORDER BY pa.[CreatedOnDate]; SELECT ps.* FROM [dbo].[PortalSettings] ps WHERE [PortalID] = @PortalID AND [SettingName] = N'DefaultPortalAlias'; SELECT hs.* FROM [dbo].[HostSettings] hs WHERE hs.[SettingName] LIKE N'%SMTP%' ORDER BY hs.[SettingName]; SELECT s.* FROM [dbo].[Schedule] s WHERE NOT s.[Servers] IS NULL; /* END OF SCRIPT */
Enter fullscreen mode Exit fullscreen mode

Top comments (0)