February 15th, 2015
  • DTAP
  • Database rollback
  • EPiServer 7.5

Production database rollback through DTAP

Since users would like to test on the acceptance environment with the same content and setup as production, and we would like to fix issues on our development machine using the same content and setup as production it is sometimes very handy to roll back the production database through the DTAP street.

When you only have one site, you can stop reading here, but if you have multiple sites, or like in our case way more than 30 sites, you got quiet a challenge!

The problem is with the main URL of Sitesettings every site. Since this is an production URL you cannot simply switch from site to site in the EPiServer backend. When you click on another homepage in EPiServer, you’ll be redirected to the production environment of that site and if the user isn’t aware of this he’ll start changing production… Changing these URLs is very time consuming and error prone. We have one convention for all URLs for every environment. Of course production is free to use other kind of urls, but acceptance starts with acceptance.*, testing with testing.* and local ends with *.localhost.local.

We’ve come up with a very good solution for this. On the production environment we’ve added every hostheader from every DTAP environment. Example:

In this example the production environment has multiple hostheaders: www.demosite.com, demosite.com, www.ds.com and ds.com
We’ve also added the other environment hostheaders; the cms url, the acceptance url, the testing url and the local url.
Since we always rollback to a ‘lower’ environment which has only one url (hostheader) it doesn’t matter that production has multiple.

When we backup the production database and restore it on lets say aceptance the url of the demosite site need to be changed to acceptance.demosite.com. To do this for every site in the EPiServer database you can run the following SQL script after the restore of the database, but before the site is started:

DECLARE @environment VARCHAR(250)

--SELECT @environment = '%.localhost.local'
--SELECT @environment = 'testing.%'
SELECT @environment = 'acceptance.%'
--SELECT @environment = '%.hiddencmsurl.com'


DECLARE @getid CURSOR
DECLARE @newUrl VARCHAR(250)
DECLARE @id INT

SET @getid = CURSOR
FOR SELECT Substring([SysTable].[string03], 0, Charindex('/',
           [SysTable].[string03]))
           + '//' + [Table].[string02] + '/',
           [Reference].[pkid]
    FROM   [dbo].[tblbigtable] AS [Table]
           JOIN [dbo].[tblbigtablereference] AS [Reference]
             ON ( [Table].[pkid] = [Reference].[refidvalue] )
           JOIN [dbo].[tblsystembigtable] AS [SysTable]
             ON ( [Reference].[pkid] = [SysTable].[pkid] )
    WHERE  [Table].[storename] = 'EPiServer.Web.HostDefinition'
           AND [Table].[string02] LIKE @environment

OPEN @getid

FETCH next FROM @getid INTO @newUrl, @id

WHILE @@FETCH_STATUS = 0
  BEGIN
      UPDATE [dbo].[tblsystembigtable]
      SET    [string03] = @newUrl
      WHERE  [pkid] = @id

      FETCH next FROM @getid INTO @newUrl, @id
  END

CLOSE @getid

DEALLOCATE @getid

SELECT [string01] AS [SiteName],
       [string03] AS [Url]
FROM   [dbo].[tblsystembigtable]
WHERE  [storename] = 'EPiServer.Web.SiteDefinition'
< Previous post Overview posts Next post >
Hi Johan, It is a good idea to use the views. With your current script you still have to specify the urls per site. The script we've created automatically migrates all site urls using the other hostheaders. We have 32 http sites, and 1 https site, and with this script I don't have to bother about any of them, just run the script after restore, and it automagically does its thing.
Laurens Huizer, February 23rd, 2015
Hi Instead of going directly against tblSystemBigTable you can work through the views [dbo].[VW_EPiServer.Web.SiteDefinition] and [dbo].[VW_EPiServer.Web.HostDefinition]. Then you could do something like: --Here I assume that there is only one site, otherwise Where statements can be used UPDATE [dbo].[VW_EPiServer.Web.SiteDefinition] SET [SiteUrl] = 'http://testenvironment/' UPDATE [dbo].[VW_EPiServer.Web.HostDefinition] SET [Name] = 'testenvironment' WHERE [Name] = 'prodhost'
Johan Björnfot, February 19th, 2015

Leave a comment

= Thanks for your comment =