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

Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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 thread is locked and should be used for reference only. Please use the Episerver CMS 7 and earlier versions forum to open new discussions.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.