How I can get db's current restore mdoe status by query?

Question

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 ?

Answer 1

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

Answer 2

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