Solved

SQL Server 2008 Express Upgrade via command line

Posted on 2013-06-15
8
688 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
  • 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now