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
localhostso no emails go out during development. - Clears
Schedule.Serversso scheduled jobs run locally. - Outputs relevant rows from
PortalAlias,PortalSettings,HostSettings, andScheduleso you can verify changes.
How to Use
- Back up your database first — this script makes direct updates.
- 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 to1for local installs (changes SMTP to localhost),0for cloning between production servers.
-
- 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
*/
