Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2008 Express Upgrade via command line

Posted on 2013-06-15
8
Medium Priority
?
726 Views
Last Modified: 2013-06-24
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
Comment
Question by:spazjr01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39250607
Hi,

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

Regards
  David
0
 

Author Comment

by:spazjr01
ID: 39250656
I'm logged in as a local admin.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39250716
What groups have sysadmin rights in sql express?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:spazjr01
ID: 39250762
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39250770
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
 

Author Comment

by:spazjr01
ID: 39250816
ok.  how do I do this programmatically.  This is part of a install package is that distributed to users of the app.
0
 

Accepted Solution

by:
spazjr01 earned 0 total points
ID: 39259362
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
 

Author Closing Comment

by:spazjr01
ID: 39270673
No one else provided a feasible solution.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question