• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

View Recovery State

I know in SQL 2008 I can do a right click on the database and navigate to where I can view the recovery state

Is there a script that I can run that lets me see that?  I can set with the script below...I want to see it with a script

alter database AdventureWorks
set recovery simple;
  • 2
  • 2
2 Solutions
Select state_desc, State FROM sys.databases

Select status
FROM sysdatabases

1 = autoclose; set with ALTER DATABASE.
4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.
8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.
16 = torn page detection, set with ALTER DATABASE.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with ALTER DATABASE.
1024 = read only; set with ALTER DATABASE.
2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.
4096 = single user; set with ALTER DATABASE.
32768 = emergency mode.
4194304 = autoshrink , set with ALTER DATABASE.
1073741824 = cleanly shutdown.

Anthony PerkinsCommented:
SELECT DATABASEPROPERTYEX('YourDatabaseNameGoesHere', 'Recovery')
Right click Database, Properties -> Option -> State
Anthony PerkinsCommented:
You can also do (just correct the obvious typo in xSELECT):
xSELECT recovery_model, recovery_model_desc
FROM sys.databases
WHERE name = 'YourDatabaseNameGoesHere'
lrbristerAuthor Commented:
Exactly what I wanted on both answers

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now