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

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?


  • 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.

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

  • 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/.

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

  • 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.

     

     

  • 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?

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

  • 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.

  • 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

  • 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

     

First   1   Last