MSSQL Last Resort Recovery Method

After attempting every type of restore / backup of a handful of MSSQL databases from a failed server I came upon a couple of commands that managed to bring the database back to life.

**WARNING**
This method can result in data loss especially if the database died while being written too.... obviously the more transactions you have going through the database the greater the chance of data loss. This method is the absolute last method you should try.

Here is the command to attempt data recovery and bring the database up in single user mode.

Use master

go

sp_configure 'allow updates', 1

reconfigure with override

go

sp_dboption 'DATABASENAME', 'single_user', 'true'

go

alter database DATABASENAME set EMERGENCY

go

DBCC CHECKDB('DATABASENAME', REPAIR_ALLOW_DATA_LOSS)

Go

 

 

Once this is complete you can view tables and data to check what is recovered. (NOTE - This can take a very long time to complete on large databases)

 

When you are happy to bring the database back online you just need to use this command

 

Use master

go

sp_dboption 'DATABASENAME', 'single_user', 'false'

go