Database Recovery

• SriSun

  Say Hey Kid

  Points: 684

  More actions

  May 19, 2008 at 9:37 am

  #185796

  Is there any query in SQL Server 2005 which gives the status of database, if the database is showing recovery (after we start SQL Server Service).

  ex: select [name], [is_read_only] from sys.databases

  resultset:

  [name] [is_read_only]

  DB1 0

  DB2 0

  DB3 1

  DB4 0

  Just like this.

  Thanks,

  Sri.

• Matija Lah

  SSCertifiable

  Points: 6429

  More actions

  May 21, 2008 at 2:19 am

  #818405

  Have you read the article on sys.databases in Books Online? You really should. 🙂

  Anyway, here's the query:

  select[name]

  ,state_desc

  fromsys.databases

  ...or if you want a 1 for every database in recovery, and a 0 for every other state:

  select[name]

  ,is_inrecovery

  = case[state]

  when2

  then1

  else0

  end

  fromsys.databases

  ML

  ---
  Matija Lah, SQL Server MVP
  http://milambda.blogspot.com