Emergency Access to SQL Server

There are times when you need to gain access to SQL Server in an emergency. It doesn't happen very often, but it does happen. Actually, part of the problem is that it happens so rarely! We all get to grips with the various back-door commands required of us DBAs when we're training or taking a certification course. But after six months, even two or more years, it all gets a bit rusty.

Therefore, I decided to put together an emergency cheat-sheet. All of these processes are very well documented around the web. My purpose here is purely to list the necessary steps to get access to your server quickly. So I won't be explaining how things work. I will list different options of getting the various steps done.

If you'd like to get more detailed info on how all this works and what to do in some worst case scenarios see the following articles:
Troubleshooting a SQL Server instance that will not start - Gail Shaw
The Dedicated Admin Connection: Why You Want It, When You Need It, and How To Tell Who’s Using It - Kendra Little
Rebuilding System Databases - MSDN
Interesting behaviour with trace flag 3608 - Amit R S Bansal
Using the emergency state for a corrupt sql server database - MSSQL Tips

DAC (Dedicated Administrator Connection)

The first scenario involves a SQL Server instance that is blocking new connections or is so maxed out it just won't accept any additional connections. It's time to use DAC.

As general preparation for these scenarios, be sure to have remote DAC connections enabled on all SQL Server instances:

• Access using DAC (Dedicated Administrator Connection)
• Make sure SQL Browser is running
• Local connection
• cmd prompt: NET Start sqlbrowser
• Powershell:
  
• Remote connection
• cmd prompt:
  
• Powershell:
  
• Start sqlcmd via CMD prompt or sqlps (powershell)
• Default instance:
  
• Named instance:
  
• DAC via SSMS
• Open a new Database Query Connection
• Prefix the sql server instance name with 'admin:' in the server name field

Start SQL Server in master-only recovery mode

Once, in attempting to move tempdb files we ran into an issue where Windows denied access to the new location after we moved the files and attempted to restart SQL Server. Despite the entire Operations team's best efforts we couldn't get SQL Server access to the new directory. The only way round it was to fail back to the original configuration. And, the only way to do that would be to start SQL Server in master-only recovery mode, then run an alter database move file script.

• Start SQL Server from a CMD prompt:
• Default instance:
  
• Named instance:
  
• Start SQL Server from sqlps (powershell):
• Default Instance
  
• Named Instance
  

Fixing a corrupt or suspect database (only if no usable DB backups are available)

There may be occasions where a database is marked as Suspect or Corrupt. And in a worst case scenario, there will be no backups that can be used. For example, they may be too old or in an odd and evil alignment of the planets, the disks/tapes with the relevant backups are also damaged or inaccessible (no, this has never happened to me!). In such a case, you will need to perform the following steps and hope the database can be fixed and not too much data is lost.