It will happen to us all (at least once :))
Now you know how to fix it and not having to reinstall SQL Server!
A big thank you for my colleague Wim Kok who saved me!
So in my case, I detached an important database that was used to store logon statistics by using a server trigger.
The thing is that detaching it was easy, but it kicked me out of SQL Server and (ofcourse) it didn’t let me re-connect to the SQL Server Instance. If you’re lucky, you still have an open connection (as Administrator) and be able to attach the database again. Again, I my case I wasn’t that lucky 😦
More info: Microsoft Support
Steps to take:
Step #1: Stop SQL Server and related services.
Step #2: Start SQL Server from the command prompt using the startup parameters: “–c -m –f“. Or via Services -> SQL Server Properties as shown below (doing this disables the login trigger):
Still having trouble?
“Server is in single user mode. Only administrator can connect at this time.”
Make sure that all services are stopped and create a batch-file (*.bat) that contains:
NET STOP MSSQLSERVER
NET START MSSQLSERVER /c /m /T3604 /f
Execute the batch-file via Command (run as Administrator) -> Shift + Right-click
Now, start SSMS (run as Administrator) and try to connect (a suggestions would be to fill in an IP-address, 127.0.0.1 for local host).
This should work, enjoy! 🙂