There are many scenarios SQL Server database becomes inaccessible because it is marked as Suspected. When the database is in suspected mode, user cannot access and cannot perform transaction on that data until it comes back to online mode. In addition, if the backup is not generated before database corruption, user cannot perform any action.
In order to resolve this problem user can go for Emergency mode that gives Read Only permission to the user. Emergency Mode facilitates the use of suspected database effectively.
In this article, we will discuss the different aspects of using SQL Server Database Emergency Mode.
Some Reasons That Turns SQL Server Database ‘Suspected’
- If transaction log files are corrupted or missing
- Database .mdf and .ndf file corruption
- Server unexpected shutdown
- Hardware issue and Power failure
- Virus attack on database
Using SQL Server Emergency Mode for Suspected Database
At times when user is not equipped with a backup and their log files are also damaged, in such case the only one way to access the data in Emergency mode. It only allows Read Only access and restricts the access to members.
The method is fully documented and supports SQL Server 2005 and all new versions. It uses DBCC CHECKDB to get the database back online.
- The operations performed during Emergency Mode are a one-way operation and user cannot roll it back. Therefore, the advice is to create a copy of damaged database before performing any operation on it.
- Suspect state is not prerequisite for using Emergency Mode. Although, putting Emergency Mode is most useful when there suspect data is present in database.
How Does SQL Server Database Emergency Mode Works?
Checking the Suspected State of Database
Firstly, user needs to check database is in suspected state by accessing data from database. By using command:
SELECT *FROM database_name..table_name
If the database is in suspect mode, it will show the error message to the user.
Putting the SQL Server Data in Emergency Mode
To put suspected data in emergency mode, user needs to execute the following command:
ALTER DATABASE database_name SET EMERGENCY
Use DBCC CHECKDB Tool to Check the Integrity
User can use DBCC statements with REPAIR_ALLOW_DATA_LOSS option that will remove all damaged and inconsistent data and results physically consistent database. While removing the inconsistency from database, user may lose some data.
User can run below mentioned command to run DBCC CHECKDB.
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Switch the Database Back to Multi-user
While using emergency mode, it restricts the database access to limited user, after repairing the data, one can switch it back to multi user access.
ALTER DATABASE database_name SET MULTI_USER WITH ROLLBACK IMMEDIATE
Put the Database in Online State
In the final step user needs to put the database in online state in order to leave SQL Server database emergency mode.
ALTER DATABASE database_name SET ONLINE
Limitations – This approach is not suitable for large databases, user needs an alternative solution for handling such errors. To tackle the limitations of system defined methods, user can go for a third party tool namely SysTools SQL Recovery.