Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 746
  • Last Modified:

SQL Server 2008 Express Upgrade via command line

I am having a problem upgrade an existing SQL Server 2005 Express instance to SQL Server 2008.  I believe there is a problem with the installing (upgrading) user account that sql server 2008 is trying to use.  There is no existing sa account nor does the current Windows user have an account in the SQL Server 2005 instance.  First, I'm not sure which account the SQL Server 2008 needs, plus I don't know how to reestablish this.  Historically, I have a C# project that manages the install and upgrade of the SQL Server Engine for the app.  Previously, for upgrades, I have code that renames the app sa account to sa and resets the password to a known value.  Then, when I upgraded to SQL Server 2005 (because it used the sa account for the upgrade), I could pass the /SAPWD="passwordValue" option for the upgrade.  After the upgrade was finished.  I'd rename the sa account back to the app account userid and change the password back.  However, SQL Server 2008 doesn't appear to support this technique during the upgrade.  the /SAPWD option is only available on the initial installation, but not the upgrade.  Keep in mind that I am doing this via the command line.  Everything is working except the access to the db during the install.  I believe this is because the installing account does not have access.  I need to what account to use and how to set this up via sql commands and/or command line options.  I have included the error that is generated when I try to upgrade via the UI.  Below is my current command line script.  Thanks for any help.

My current command line upgrade script:
SQLEXPR_x86_ENU.exe /INSTANCEID=MYDB /INDICATEPROGRESS=False /QS /ERRORREPORTING=False /SQMREPORTING=False /INSTANCENAME=MYDB /ISSVCSTARTUPTYPE=Automatic /ISSVCACCOUNT="NT AUTHORITY\NetworkService" /BROWSERSVCSTARTUPTYPE=Disabled
/SKIPRULES=Engine_SqlEngineHealthCheck /IAcceptSQLServerLicenseTerms=True /ACTION=UPGRADE

Below in the error that I get in the log when I try to install manually.  Something to know:  I am logged in with a Windows account called Administrator, when I attempt to install.  I see two account mentioned in the log: sa (which doesn't exist in MYDB), and Administrator (which doesn't exist in MYDB).  I don't know which I need, if any, and I don't know how to set this up programmatically as part of the install via SQL Commands and/or option on the command line.

One other thing to note.  If I have SQL Server 2008 Developer Edition installed on the computer, the upgrade from the SQL Server 2005 Express MyDB instance upgrades just fine to SQL Server 2008 Express MyDB using my command line script above.   I don't know what the difference is, but the user community will not have the Developer Edition.  But, maybe that's a clue that will help someone give me the right answer.

Error log from a manual upgrade failure:
2013-06-15 10:05:28.51 Server      Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)
 Dec 10 2010 10:56:29
 Copyright (c) 1988-2005 Microsoft Corporation
 Express Edition on Windows NT 6.1 (Build 7601: Service Pack 1)

2013-06-15 10:05:28.51 Server      (c) 2005 Microsoft Corporation.
2013-06-15 10:05:28.51 Server      All rights reserved.
2013-06-15 10:05:28.51 Server      Server process ID is 2472.
2013-06-15 10:05:28.51 Server      Authentication mode is MIXED.
2013-06-15 10:05:28.51 Server      Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2013-06-15 10:05:28.51 Server      This instance of SQL Server last reported using a process ID of 1344 at 6/15/2013 10:05:26 AM (local) 6/15/2013 3:05:26 PM (UTC). This is an informational message only; no user action is required.
2013-06-15 10:05:28.51 Server      Registry startup parameters:
2013-06-15 10:05:28.51 Server        -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2013-06-15 10:05:28.51 Server        -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2013-06-15 10:05:28.51 Server        -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2013-06-15 10:05:28.53 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2013-06-15 10:05:28.53 Server      Detected 1 CPUs. This is an informational message; no user action is required.
2013-06-15 10:05:28.70 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2013-06-15 10:05:28.73 Server      Database mirroring has been enabled on this instance of SQL Server.
2013-06-15 10:05:28.73 spid5s      Starting up database 'master'.
2013-06-15 10:05:28.84 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2013-06-15 10:05:28.96 spid5s      SQL Trace ID 1 was started by login "sa".
2013-06-15 10:05:29.01 spid5s      Starting up database 'mssqlsystemresource'.
2013-06-15 10:05:29.06 spid5s      The resource database build version is 9.00.5000. This is an informational message only. No user action is required.
2013-06-15 10:05:29.79 spid8s      Starting up database 'model'.
2013-06-15 10:05:29.82 spid5s      Server name is 'WIN7_32BITTEST\TPSDB'. This is an informational message only. No user action is required.
2013-06-15 10:05:29.82 spid5s      Starting up database 'msdb'.
2013-06-15 10:05:30.09 Server      A self-generated certificate was successfully loaded for encryption.
2013-06-15 10:05:30.10 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\TPSDB ].
2013-06-15 10:05:30.10 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$TPSDB\sql\query ].
2013-06-15 10:05:30.10 Server      Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2013-06-15 10:05:30.12 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2013-06-15 10:05:30.93 spid8s      Clearing tempdb database.
2013-06-15 10:05:31.32 Logon       Error: 18456, Severity: 14, State: 11.
2013-06-15 10:05:31.32 Logon       Login failed for user 'WIN7_32BITTEST\Administrator'. [CLIENT: <local machine>]

 

















");
SQL-Server-2008-Upgrade-problem.docx
0
spazjr01
Asked:
spazjr01
  • 5
  • 3
1 Solution
 
David ToddSenior DBACommented:
Hi,

When you are logged in as administrator, is it domain administrator, or the local administrator on win7....

Regards
  David
0
 
spazjr01Author Commented:
I'm logged in as a local admin.
0
 
David ToddSenior DBACommented:
What groups have sysadmin rights in sql express?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
spazjr01Author Commented:
There is one account that has sa rights and is used by the app that uses the instance.  It is the account that used to be the built in sa account.  But, as part of (actually after) the installation of sql server 2005, the sa account was altered (renamed) to this account, and the password was changed to a password that the app uses.There are no other sa accounts.
0
 
David ToddSenior DBACommented:
Hi,

Then of course the windows account can't connect!

Set up the windows account that will be performing the upgrade and give it sysadmin rights!

Regards
  David
0
 
spazjr01Author Commented:
ok.  how do I do this programmatically.  This is part of a install package is that distributed to users of the app.
0
 
spazjr01Author Commented:
I finally discovered that the problem is:  "Access is denied error installing SQL Server 2008 on Windows 7", which is addressed at the following link.  Once the steps at the below link are executed, the upgrade (and first time installs) works just fine.

http://serverfault.com/questions/212135/access-is-denied-error-installing-sql-server-2008-on-windows-7
0
 
spazjr01Author Commented:
No one else provided a feasible solution.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now