Stop the SQL Server instance. And start it in single user mode. Open a command prompt window.
Change into you SQL Server Binn directory for the instance you are repairing (the default is c:\Program Files\Microsoft SQL Server\MSSQL\Binn) and run:

sqlservr.exe -c -m

The -c option tells SQL Server to run independantly, and not as a service.
The -m option tells SQL Server to run in single-user mode.

Your sql server instance will then start up and you can connect to it with Query Analzyer.

If you get the error message:

Server: Msg 18461, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failer for user ‘x’.
Reason: Server is in single user mode. Only one administrator can connect at this time.

then you need to make sure that no other administrators are accessing the server. If you have
Enterprise Manager or the SQL Server Service Manager open, close them, and try again. Make sure
that any other services (like SQL Agent) that access SQL Server are also stopped.

Once you have successfully connected to your SQL Server instance in Query Analyzer, restoring
the master database simply requires running the RESTORE DATABASE command:

RESTORE DATABASE master FROM DISK=’N:\Backups\master.bak’

SQL Server should return:

The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

Once your master database is restored, SQL Server will reboot and attempt
recovery on the other databases listed in your master database. Check the
error log again to see what databases have SQL Server is unable to recover.

2005-12-28 16:16:00.66 spid11    Starting up database 'HHH'.
2005-12-28 16:16:27.85 spid11    Error: 9003, Severity: 20, State: 1
2005-12-28 16:16:27.85 spid11    The LSN (72984:107911:2) passed to log scan in database 'HHH' is invalid..
2005-12-28 16:16:28.07 spid11    Error: 3414, Severity: 21, State: 1
2005-12-28 16:16:28.07 spid11    Database 'HHH' (database ID 7) could not recover. Contact Technical Support..

During the recovery of this server I found four databases were unrecoverable. Again, restoring these databases
from their latest backup resolved the problem.

To ensure that all the databases were infact ok, I ran DBCC CHECKDB. A quick way to run it on
all databases is with this script:

EXEC sp_MSforeachdb 'PRINT REPLICATE(CHAR(13),3) + REPLICATE(''='',80) + CHAR(13) + ''Checking: ?''
		     DBCC CHECKDB (''?'') WITH NO_INFOMSGS'
Tags: