Hi Jouni,

HostDefinition is stored in tblBigTable, and SiteDefinition is stored in tblSystemBigTable

Nice question, I am just about to do the same thing!

Jouni, can you please chare the code when you are done?

Thanks! I'll see if I can make some sense of this :)

Henrik, certainly! I'll share the script if I get it working.  

I forgot to send the queries:

SELECT * FROM tblBigTable where StoreName = 'EPiServer.Web.HostDefinition'

SELECT * FROM tblSystemBigTable WHERE StoreName = 'EPiServer.Web.SiteDefinition'

As with other types in DDS there is also database views generated for each store. So an alternative is to do the data access through the views instead of going directly towards the tables.

Thanks Johan! The views make the sql scripting super-easy!

use MySite

update dbo.[VW_EPiServer.Web.SiteDefinition]
   set SiteUrl = 'http://test.site.com/'
 where Name = 'MySite';
   
update dbo.[VW_EPiServer.Web.HostDefinition]
   set Name = 'test.site.com'
 where Name = 'www.site.com';

Remember that you can run scripts from command line, e.g. "call sqlcmd -i update_test_conf.sql". So you can automate the whole db restore + configuration, for example, with a batch file like:

set physicalPath=%~dp0
set dumpfolder=D:\temp\datadump
set database=MySite
set dbuser=MySite
 
:: restore db and set correct owner
call sqlcmd -E -Q "Alter Database %database% SET SINGLE_USER With ROLLBACK IMMEDIATE; RESTORE DATABASE %database% FROM DISK='%dumpfolder%\%database%.bak' WITH REPLACE; ALTER AUTHORIZATION ON DATABASE::%database% TO %dbuser%"
:: update correct configuration
call sqlcmd -i %physicalPath%update_test_conf.sql

Btw. you can easily automate production data dumps too with command line, for example, run this batch file in production db (just add 7za.exe to the same folder)

:: Set your site specs here
set database=MySite
set vppfolder=D:\Webshare\MySite\appdata
:: Datadump output folder
set dumpfolder=D:\temp\datadump

mkdir %dumpfolder%

:: backup database. replace existing file
call sqlcmd -E -Q "BACKUP DATABASE %database% TO DISK='%dumpfolder%\%database%.bak' WITH FORMAT"

:: zip vpp folder, no compression
call 7za.exe a -mx0 %dumpfolder%\%database%.vpp.zip %vppfolder%\*
:: compress database backup ("fast" compression)
call 7za.exe a -mx3 %dumpfolder%\%database%.bak.zip %dumpfolder%\%database%.bak
:: combine zips to one archive
call 7za.exe a -mx0 %dumpfolder%\%database%.data.zip %dumpfolder%\%database%.bak.zip %dumpfolder%\%database%.vpp.zip

:: cleanup
call del %dumpfolder%\%database%.vpp.zip
call del %dumpfolder%\%database%.bak
call del %dumpfolder%\%database%.bak.zip

So, now you should have one zip archive named MySite.data.zip that contains both the database and vpp files. Let's transfer the file to the test environment db and execute following batch file (add 7za.exe and the previosly mentioned update_test_conf.sql to the same folder)

@echo off
:: better safe than sorry
IF NOT %COMPUTERNAME%==TEST_ENVIRONMENT_NAME (
    echo WRONG ENVIRONMENT!
    pause
    exit
)
@echo on

:: Set your site specs here 
set database=MySite
set dbuser=MySite
set vppfolder=D:\Webshare\MySite\appdata
:: Datadump input folder
set dumpfolder=D:\temp\datadump
set physicalPath=%~dp0

:: unzip packages
call 7za.exe x -o%dumpfolder% %dumpfolder%\%database%.data.zip 
call 7za.exe x -o%dumpfolder% %dumpfolder%\%database%.bak.zip 

:: restore db and set correct owner
call sqlcmd -E -Q "Alter Database %database% SET SINGLE_USER With ROLLBACK IMMEDIATE; RESTORE DATABASE %database% FROM DISK='%dumpfolder%\%database%.bak' WITH REPLACE; ALTER AUTHORIZATION ON DATABASE::%database% TO %dbuser%"
:: update correct configuration
call sqlcmd -i %physicalPath%update_test_conf.sql


:: delete old vpp data
FOR /D %%p IN ("%vppfolder%\*.*") DO rmdir "%%p" /s /q
:: sometimes the first call fails to remove all subdirs 
FOR /D %%p IN ("%vppfolder%\*.*") DO rmdir "%%p" /s /q
:: restore vpp from zip
call 7za.exe x -o%vppfolder% %dumpfolder%\%database%.vpp.zip

:: cleanup
call del %dumpfolder%\%database%.vpp.zip
call del %dumpfolder%\%database%.bak
call del %dumpfolder%\%database%.bak.zip

If all goes well, you should now have freshly updated data dump in your test environment :)

Site and host definitions are no longer kept in DDS but instead in own tables. So the previous script no longer works. This one does:

use MySite
 
update dbo.[tblSiteDefinition]
   set SiteUrl = 'http://test.site.com/'
 where Name = 'MySite';
    
update dbo.[tblHostDefinition]
   set Name = 'test.site.com'
 where Name = 'www.site.com';

Thanks Jouni, I have missed that schema change and I use it a lot so, thanks for saving me some time bugfixing my restore-script! :)

Thanks! I also missed the switch to separate tables. Strange thing tho in our DB the old views are still there, pointing to big table and returing data that is no longer correct. Shouldn't this data has been migrated/cleaned up during upgrade to some version?

It might be that they are not used any more, but in that case they should have been removed.

Another question.
I noticed that changing this works great, but if you are using a clould-license the old url is still in the licence definition, are there any way to also change this by SQL-script?

Right now I update the dev/test environment with the sql script but then I need to go manually into the site to update to licence.