0

So, my db can be in three differrent statuses.

  • RECOVERY
  • NORECOVERY
  • STANDBY

And I want to know current db's status by query. Can you help me ? The single what I've found is

SELECT is_in_standby
     FROM sys.databases
WHERE [name] = 'MyDb1'

And If MyDb1 is actually in StandBy mode I'll give following:

is_in_standby
-------------
1

But Is there way to determine that MyDb1 in NoRecovery mode ?

0

So, finally I've found out a solution. Actually it's a sort of merge two queries

SELECT 
    case
        when is_in_standby = 1 then N'YES'
        else 'NO'
    END as STANDBY
    , DATABASEPROPERTYEX ('MyDb1', 'Status') STATUS
FROM sys.databases
WHERE [name] = 'MyDb1'

Result

STANDBY STATUS
------- ------
YES     ONLINE
0

You can try this -

SELECT rhistory.destination_database_name,rhistory.restore_date,
rhistory.destination_database_name,rhistory.user_name,
CASE 
WHEN rhistory.[recovery] = 0 THEN 'NoRecovery' 
WHEN sysdb.[is_in_standby] = 1 THEN 'StandBy'
ELSE 'Recovery' END AS Recovery_State
FROM msdb..restorehistory rhistory
INNER JOIN sys.databases sysdb ON sysdb.name=rhistory.destination_database_name
ORDER BY rhistory.restore_date DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.