Unable to start sql2008R2 after issuing startup command

Question

Hi There,

I have an issue starting SQL2008 R2 instance after rebuild. i intentionally removed the system databases and trying to rebuild again.(disaster recovery)

*************************************************************************

I tried this first on SQL2008. Worked fine. no issues. my databases is up and able to restore everything.

these are the steps:

1) C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="<name>\user" /SAPWD="value"

my instance is started when i open configuration manager. then i restored everything.

easy. no troubles.

*********************************************************

I am trying the same on SQL2008R2

this time i change the location.(ofcourse i tried the above location in the first place. and then reinstall again as i could not get through)

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="<name>\user" /SAPWD="value"

This real disaster to me. i can not start my instance. by default it is not running. i tried manually with no luck. some ppl on forums talking about user permissions on the folder. no luck. some one mentioned when they change the login as local from servicess worked. did not work way. i am not sure what is causing this issue. this is my 5th time installing SQL2008R2 from the begining as i could not get it work. well i tried to repair from cd, it did not work.

i am giving all the error information below.

message when instance fail to start

The request failed or the service did not respond in a timely fashon.
consult the eventlog or other applicable error logs for details.

------------------------------------------------------------------------------------------------ 

errorloginfo:

Error 2(The system cannot find the file specified.) occurred while opening
file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf'
to obtain configuration information at startup. An invalid startup option might have caused the error.
Verify your startup options, and correct or remove them if necessary.

-------------------------------------------------------------------------------------------------------------

Below is the error log information:

2012-07-16 03:57:07.25 Server      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
 Apr  2 2010 15:48:46
 Copyright (c) Microsoft Corporation
 Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

2012-07-16 03:57:07.25 Server      (c) Microsoft Corporation.
2012-07-16 03:57:07.25 Server      All rights reserved.
2012-07-16 03:57:07.25 Server      Server process ID is 1448.
2012-07-16 03:57:07.25 Server      System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
2012-07-16 03:57:07.25 Server      Authentication mode is MIXED.
2012-07-16 03:57:07.25 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2012-07-16 03:57:07.25 Server      This instance of SQL Server last reported using a process ID of 160 at 7/16/2012 3:54:20 AM (local) 7/16/2012 10:54:20 AM (UTC). This is an informational message only; no user action is required.
2012-07-16 03:57:07.25 Server      Registry startup parameters:
  -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
  -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2012-07-16 03:57:07.26 Server      Error: 17113, Severity: 16, State: 1.
2012-07-16 03:57:07.26 Server      Error 2(The system cannot find the file specified.) occurred while opening
file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf' to obtain
configuration information at startup. An invalid startup option might have caused the error.
Verify your startup options, and correct or remove them if necessary.

Answer 1

Have you checked if the file mentioned does exist? The error is pretty specific... My guess would be that your rebuild either failed or generated the DB at a wrong location.

Answer 2

Hi Harri,

Could you please check will your service account has proper privileges on the data folder (C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA) if not please provide the full permission...

Configure Windows Service Accounts and Permissions(best practice for the permission to set)-

http://msdn.microsoft.com/en-us/library/ms143504.aspx

then  are you sure that you have the sysadmin granted to to the service account and also verify your permission as well incase if you are trying to start the SQL server services from your account.Verify the MDF and ldf file resides...

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

Answer 3

HI there,

I did not find the master.mdf in my mssqlserver folder except the template. so my rebuild failed. but i haven't get any error. it worked in 2008. is there any difference in setup.exe command for 2008 R2.

cheers

Answer 4

i checked the permissions. it is not permissions issue. it is something else. looks like rebuild did not worked. i am not sure how it is different from SQL2008.

Answer 5

When i look at the forums i figured out it is a known issue. we can not rebuilddatabase in SQL2008.

some ppl suggested workaround. but it did not work.

http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/4b8ad8c6-b26f-4477-90ec-33257e8585b3/

If any one have faced the same issue and found the solution please let me know. i am moving forward to SQL2012.

thank you.

Answer 6

Hi Harri,

Please check this one

http://sqlbuzz.wordpress.com/2011/08/20/how-to-rebuild-master-database-aka-rebuilding-sql-server-2008r2/

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

Answer 7

Hi Harri,

I had the same issue recently when was doing some practice with disaster recovery.

I have no trouble using startup command in SQL 2008. it works fine. where as SQL 2008R2 folders has changed you must have known by now. in SQL 2008R2 start up command have some known issues with microsoft. It won't support.

instead of using startup you must use your CD-ROM source to kick off the recovery. i have done this way and i am able to start up and recover my servers...

good luck.

kumar