| Author |
 Topic  |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
 Posted - 01/02/2013 : 00:10:36
|
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
35007 Posts |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 01/02/2013 : 23:32:18
|
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 |
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 01/04/2013 : 03:46:49
|
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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 01/11/2013 : 01:52:26
|
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 |
|
|
| |
Topic |
|
Login Problem
