The database states are
Offline
Online
Recovering
Recovery Pending
Restoring
Suspect
Emergency
Offline :
When db is offline state then the database is not accessible for user connections. If you don't want to connect to the database then you can make it as offline .
Ex: You have migrated the db to new server and don't want to users connect to old db accidentally then you can make the db offline .
Online :
When the db is online state then the db is available to the users for access .The primary file group is in online even though the undo phase of recovery may not have been completed .
Recovering :
The db state would be in transient during recovery . The db will become to online automatically once recovery succeeds . If the recovery fails then the db will goes to suspect mode and db is unavailable.
Recovery Pending :
When the db is in recovery pending state means the db encountered a resource related error during recovery .The db is missing files and DBA interventions is required in such case .
Ex: we faced this situation when the developer moved database files either mdf or ldf files to different location without DBA awareness.
Suspect :
When the db in suspect state then db is unavailable for the user connections .
Emergency :
User will set the db status to emergency to repair or restore the database during db goes to suspect. Database would be in single user mode and read only operations possible . Sysadmin permission is required to make the db into emergency mode .
Offline
Online
Recovering
Recovery Pending
Restoring
Suspect
Emergency
Offline :
When db is offline state then the database is not accessible for user connections. If you don't want to connect to the database then you can make it as offline .
Ex: You have migrated the db to new server and don't want to users connect to old db accidentally then you can make the db offline .
Online :
When the db is online state then the db is available to the users for access .The primary file group is in online even though the undo phase of recovery may not have been completed .
Recovering :
The db state would be in transient during recovery . The db will become to online automatically once recovery succeeds . If the recovery fails then the db will goes to suspect mode and db is unavailable.
Recovery Pending :
When the db is in recovery pending state means the db encountered a resource related error during recovery .The db is missing files and DBA interventions is required in such case .
Ex: we faced this situation when the developer moved database files either mdf or ldf files to different location without DBA awareness.
Suspect :
When the db in suspect state then db is unavailable for the user connections .
Emergency :
User will set the db status to emergency to repair or restore the database during db goes to suspect. Database would be in single user mode and read only operations possible . Sysadmin permission is required to make the db into emergency mode .
Great explanation . It would be great if you can include Syntaxes also .
ReplyDelete