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

Member since: 2008

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?

#56541 Jan 25, 2012 18:33
  • Member since: 2007

    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.

    #56542 Edited, Jan 25, 2012 19:57
  • Member since: 2008

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

    #56543 Jan 25, 2012 20:02
  • Member since: 2007

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

    #56544 Jan 25, 2012 20:12
  • Member since: 2007

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

    #56545 Jan 25, 2012 20:18
  • Member since: 2008

    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.

     

     

    #56546 Jan 25, 2012 20:21
  • Member since: 2008

    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?

    #56547 Jan 25, 2012 20:22
  • Member since: 2007

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

    #56548 Jan 25, 2012 20:26
  • Member since: 2008

    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.

    #56549 Jan 25, 2012 20:48
  • Member since: 2002

    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

    #56900 Feb 15, 2012 7:01
  • Member since: 2006

    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

     

    #56999 Feb 21, 2012 11:54
This topic is locked because the last reply was posted more than 6 months ago. Please contact epw@episerver.com to unlock it.