SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Login Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 01/02/2013 :  00:10:36  Show Profile  Reply with Quote
Hi,

am getting the following error while connecting to my mssql instance..
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)"

How to resolve this error?

I had look into log file. The error was 'Master Database Corrupted'
Then i tried to rebuild master database by using the following command:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [/SAPWD= StrongPassword]

Again i got error as follows:
Error result: -2068643838
Result facility code: 1203
Result error code: 2

What to do next? Please help me.............

--
Chandu

tkizer
Almighty SQL Goddess

USA
36565 Posts

Posted - 01/02/2013 :  14:01:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
Do you have backups that you can restore?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 01/02/2013 :  23:32:18  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Do you have backups that you can restore?

I have only .mdf and .ldf files of all databases... i tried lot of methods to start instance..
Main thing is "my ipaddress is changed". Can i replace ipaddress in SQL server configuration manager?

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 01/03/2013 :  05:18:51  Show Profile  Reply with Quote
i too got same error as in this link...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=176587

I am using Developer edition. how can i re-install my instance? And am i need to have License key details? Please provide possible solutions for me....

Am unable to findout Rebuildm.exe file to recover master database (http://www.techrepublic.com/article/recover-the-master-database-in-sql-server/5025441)
--
Chandu

Edited by - bandi on 01/03/2013 05:22:10
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 01/04/2013 :  03:46:49  Show Profile  Reply with Quote
Hi,
SQL Server service is stopped and unable to start again..
My system was suddenly strucked and then master database is corrupted. I don't have backup and also tried to rebuild system databases, got error.
Error result: -2068643838
Result facility code: 1203
Result error code: 2

And one more thing is that my ipaddress is changed.. Thats why i changed old ipaddress with new one in TCP/IP connection in Configuration Manager.

Please help me on this..

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 01/11/2013 :  01:52:26  Show Profile  Reply with Quote
For the above problem, i did the following
1) First of all i started MSSQL as console (-c) in single user mode(-m)
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn>sqlservr.exe -c -m -sMSSQLSERVER -T3608

NOTE:
3608 --> Starts SQL and recovers only the master database.

Now master database is recovered..

2) Secondly i got this error: Unable to open the physical file
"e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\model.mdf". Operating system e
rror 3: "3(The system cannot find the path specified.)".
Error: 17204, FCB::Open failed: Could not open file e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\model.mdf for file number 1. OS error: 3(The system cannot find the path specified.).

i figured out that e:\ is not at all available in my system.

Fix for above error is as follows:
************************

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn>NET START MSSQLSERVER /f /T3608
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn>SQLCMD -sMSSQLSERVER
1> SELECT name, physical_name, state_desc FROM sys.master_files ORDER BY database_id;
2> go

Now notice those wrong file names; and run following commands ...

Note: you need to change the file name location (where the original .mdf, .ldf files are available in ur system. In my point of view 'c:\model.mdf' and soon) ..

1>ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'c:\model.mdf');
2>ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = 'c:\modellog.ldf');
3> go

1>ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData, FILENAME = 'c:\MSDBData.mdf');
2>ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog, FILENAME = 'c:\MSDBLog.ldf');
3>go

1>ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'c:\temp.mdf');
2>ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = 'c:\temp.ldf');
3>go

1> SELECT name, physical_name, state_desc FROM sys.master_files ORDER BY database_id;
2> go

Now you will get modified file paths where you have respective file paths. Changing the paths as described above worked, but still I was not able to log in.
--To list the users
1> select loginname from master..syslogins;
2>go
My windows account was mising, so I have added it:>

1> CREATE LOGIN [COMPUTERNAME\USERNAME] FROM WINDOWS;
Then I was able to login with Management Studio with Windows Authentication.

--Give sysadmin rights to ur login acccount
1> EXEC sp_addsrvrolemember 'domainName\WindowsUserName', 'sysadmin';
2> go
1> SELECT loginname, sysadmin FROM master..syslogins;
2> go
You can see the login users list
1> exit

C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\Binn>NET STOP MSSQLSERVER
The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service was stopped successfully.

Then i enabled service broker for msdb database.

Finally i attached all user databases to my instance...

The supportive URLs are:
http://blogs.msdn.com/b/sqlserverfaq/archive/2011/05/11/inf-hey-my-sql-server-service-is-not-starting-what-do-i-do.aspx

http://social.msdn.microsoft.com/forums/en-US/sqlexpress/thread/54bbcac3-41c5-4a5d-a4f6-2669e538dc82/

--How to create Sql Server login for a domain account?
http://dba.stackexchange.com/questions/15737/how-to-create-sql-server-login-for-a-domain-account

-- Error 18456 causes and solution
http://www.mssqltips.com/sqlservertip/2581/sql-server-error-18456-finding-the-missing-databases/

******************* SOLVED ***********************

--
Chandu

Edited by - bandi on 01/11/2013 01:56:24
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000