Restoring DEV db with a PRODUCTION db backup with environmental updates

alan gerling asked on February 12, 2016 02:59

After a production database backup/restore to a different environment (test/uat/dev) we need to update some of the data with environmental settings such as domain URLs, email addresses, continuous integration etc..

Is there a best practice method on how to do this? Ideally we would do a straight backup/restore opposed to a staging method.

Recent Answers

Roman Hutnyk answered on February 12, 2016 04:43

You may add setting for all your environments to the database, let's say in production:

  • domain aliases for PRD, DEV,QA
  • licenses for all env
  • staging servers, but keep enabled just those, you want to be enabled
  • even web farm servers, in case you're adding them manually

In this case, whenever you restore PRD database to DEV or QA, you do not need add all this data, as domains, licenses already sit there, just enable/disable appropriate servers for staging and web farm. This does not eliminates all your efforts, but reduces them a bit.

0 votesVote for this answer Mark as a Correct answer

David te Kloese answered on February 12, 2016 14:04 (last edited on February 12, 2016 14:05)


We tend to create our own sql script which updates some environment specific settings. This script is pretty straight forward for most uses and easy to create.

Settings (dbo.CMS_SettingsKey), site main domain url (dbo.CMS_Site), Users (dbo.CMS_User)... etc.

And as Roman suggested above we keep all licenses and domains as aliases.



0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on February 12, 2016 14:34

I agree with Roman, somewhat. If you have items which can be enabled or disabled, then by all means leave those things in there. If they cannot be disabled, remove them simply because you don't want leave yourself open to the chance of having something improperly configured and someone accessing your other sites which are improperly configured.

That being said, it should be easy to go in and make changes to your site domain name, aliases and license keys. Yes, I'm sure there are other settings but they too can easily be added removed. One thing you could do is immediately before you perform your backup, is to add a site alias in for your domain so you can access it without going directly to the server and accessing it via localhost.

Lastly, there are scripts you could create or run which would update some tables but I'd highly recommend against this unless you know what affect it will have on your system and specifically what tables or views you need to query.

For the number of times this is performed in a year, it really shouldn't be an issue to go into the UI and make the changes. If you're doing it multiple times per year, you might question what the purpose of having DEV, QA, PROD instances. If you're simply looking to update content, then use content staging and post your content backwards.

1 votesVote for this answer Mark as a Correct answer

alan gerling answered on February 12, 2016 18:18

SQL Scripts seems like the preferred way but as pointed out it might get difficult to determine exactly what needs updated.

Has anyone written a .aspx page that uses the Kentico API to update values? We can have environmental config files to read from.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on February 12, 2016 21:28

The API is very easy to use and depending on what you need it can be very easy or very complicated to write. I'd create a webpart vs. an aspx page, this way you can use it wherever you'd like.

1 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.