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'