Logotype

Join our community!

Get everything you need to start developing EPiServer solutions.

How can I run the database upgrade with a SQL file, not Deployment Center?

Deane Barker EPiServer Most Valuable Professional
Deane Barker
January 25 2012, 18:33

What's the best practice for extracting the database upgrade part of the R2 upgrade into a simple SQL file?

For one client, any database changes need to be run by the database group.  So, I have to send them a SQL file, which they run.  Therefore, I can't use Deployment Center to do the upgrade.

I have upgraded my local instance, and will push all the files (including all the new assemblies) in through Subversion.  But the database upgrade as to be done by a DBA, using a SQL script.

How easy is it to pull out this portion?


  • Johan Petersson EPiServer Certified Developer
    Johan Petersson
    January 25 2012, 19:57
    Content is edited

    Hi,

    All upgrade scripts are located here C:\Program Files (x86)\EPiServer\CMS\6.1.379.0\Upgrade\Database\sql.

    But I don't know if that's all you need. Maybe something is handled through code.

  • Deane Barker EPiServer Most Valuable Professional
    Deane Barker
    January 25 2012, 20:02

    Yeah, I found the scripts, but there are at least a dozen.  Do they just run them in order?

  • Johan Petersson EPiServer Certified Developer
    Johan Petersson
    January 25 2012, 20:12

    No!

    By examine the scripts (in Notepad or SQL Manager) you can see between which versions they apply.

    Don't forget to download an updated script here http://world.episerver.com/Blogs/Fredrik-Tjarnberg/Dates/2011/4/Important-Potential-issues-when-upgrading-to-CMS6-R2/.

  • Johan Petersson EPiServer Certified Developer
    Johan Petersson
    January 25 2012, 20:18

    There is also an XForms upgrade called "Upgrade-EPiXFormNames". Don't know if you have to care about that one though...

  • Deane Barker EPiServer Most Valuable Professional
    Deane Barker
    January 25 2012, 20:21

    Oh, I see.  I thought they were cumulative.

    sp_DatabaseVersion returns a number, which represents the database version.  At the top of each SQL file is a construct that looks like this:

    if (@ver >= 5204)
---				select 0, 'Already correct database version'
---			else if (@ver = 5203)
---				select 1, 'Upgrading database'
---			else
---				select -1, 'Invalid database version detected'

        

    So, this would be the script to use when upgrading database version 5203.

     

     

  • Deane Barker EPiServer Most Valuable Professional
    Deane Barker
    January 25 2012, 20:22

    Do you have to be sa or a dbo when doing this script?  Or could I run it under the user which the app connects to the database as?

  • Johan Petersson EPiServer Certified Developer
    Johan Petersson
    January 25 2012, 20:26

    Isn't the user which the application runs under dbo? I think you can run the scripts with that user.

  • Deane Barker EPiServer Most Valuable Professional
    Deane Barker
    January 25 2012, 20:48

    It's looking like it.  (I ran a few as the app user and didn't get errors.  My only fear is that buried in there somewhere is some command that tries to madify the master database or something...)

    I did find out, also, that the files ARE sequential.  The database version just tells you where to start -- you will have to run multiple SQL files.

    I found an old install that was v6 R1.  It was database version 6003.  I found the right file for that and ran it and then checked the sp_DatabaseVersion again...6004.  So it went up a version.

    I then tracked down the right file to upgrade version 6004.  I ran that, and it upgraded to 6100.  The next one took it to 6102, then to 6103, which is current.

    So, it looks like they're sequential -- each file moves the database version up a level, but then you have to track down the next one to keep it moving forward.

  • bodahl EPiServer Certified Developer
    bodahl
    February 15 2012, 7:01

    If you do an upgrade from the deployment centre after doing a manual db-upgrade, the upgrade wizard will simply state that the sql-script has been run, and move on to the next task

  • Daniel Badro
    Daniel Badro
    February 21 2012, 11:54

    Hi I am also interested in one standalone db update method; the installer won’t work in the production environment.

    One thing that I noticed was that if the site is upgraded but not the database then the upgrade won’t do anything when choosing upgrade site and SQL it simply goes 100% and nothing happens, this has been noticed in two separate environments.

    Bug in the installer?

    One more thing the deployment center is installed under the current user why can’t you choose to install for everyone on this machine?

    EPiServer staff shape up the installer or give us the tools to install it even on more complex environments that are running more rigorous security restrictions