Why is the result of DATABASEPROPERTYEX not consistent for AdventureWorks?

Question

I'm using the function DATABASEPROPERTYEX to check the Recovery property of databases, but the result is wrong for the database AdventureWorks (later I noticed the same strange behavior for ReportServer and ReportServerTempDB databases).

Here's a simplified version of what I tried:

USE master;
GO

CREATE PROCEDURE [dbo].[sp_isSimpleRecovery] 
AS
BEGIN
    PRINT DB_NAME() + CHAR(13);

    IF DATABASEPROPERTYEX(DB_NAME(), 'Recovery') = 'SIMPLE'
        PRINT '--Database in SIMPLE recovery model' + CHAR(13);
    ELSE
        PRINT '--Database in ANOTHER recovery model' + CHAR(13);
END

I created the procedure on master using the prefix sp_ so that I could run it from any database. But when I run it from AdventureWorks the result differs from the same code running without the procedure:

USE AdventureWorks2016;
GO

EXEC sp_isSimpleRecovery;

PRINT DB_NAME() + CHAR(13);

IF DATABASEPROPERTYEX(DB_NAME(), 'Recovery') = 'SIMPLE'
    PRINT '--Database in SIMPLE recovery model' + CHAR(13);
ELSE
    PRINT '--Database in ANOTHER recovery model' + CHAR(13);

It worked fine for many other databases, but for the AdventureWorks the output is:

AdventureWorks2016

--Database in ANOTHER recovery model

AdventureWorks2016

--Database in SIMPLE recovery model

What is causing this behavior?

---

AdventureWorks sample databases I used:

• AdventureWorks2014.bak
• AdventureWorks2016.bak

SQL Server versions:

• Microsoft SQL Server 2016 (SP2-GDR) (KB4532097) - 13.0.5102.14 (X64) Dec 31 2019
• Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1 (X64) Oct 31 2020

Answer 1

I re-wrote your proc to below:

CREATE OR ALTER PROCEDURE [dbo].[sp_isSimpleRecovery] 
AS
BEGIN
    PRINT DB_NAME() + CHAR(13);
    PRINT CAST(DATABASEPROPERTYEX(DB_NAME(), 'Recovery') AS varchar(20))

    IF CAST(DATABASEPROPERTYEX(DB_NAME(), 'Recovery') AS varchar(20)) = 'SIMPLE'
        PRINT '--Database in SIMPLE recovery model' + CHAR(13);
    IF CAST(DATABASEPROPERTYEX(DB_NAME(), 'Recovery') AS varchar(20)) = 'FULL'
        PRINT '--Database in FULL recovery model' + CHAR(13);
END
GO

Note that I'm ignoring bulk-logged recovery model. This procedure does work for me, on SQL Server 2019. I.e., if I'm in a database that is in simple mode, it returns SIMPLE and if I'm in a database which is in full mode it returns FULL.

If above doesn't work for you on 2016, then possibly there has been a behavioral change between 2016 and 2019.

In some cases it isn't enough to just name the procedure sp_ and have it in the master database. You also have to mark it as a system procedure using the undocumented sp_MS_marksystemobject. Note that this is undocumented, so use at own risk!

The difference between the two is that without sp_MS_marksystemobject, the procedure is found if you are in for example Adventureworks, but is executed in the master database context. If you for instance select from sys.tables, you will see the tables in the master database.

With sp_MS_marksystemobject, you tell SQL Server to be in the context of the current database when it executes the code in your procedure. I.e., system tables, system functions and such will now be resolved to use user database instead of the master database.

So why the behavioural change between 2016 and 2019? We can't say, since we are in undocumented territory.

The simple fact is that there is no supported way to create a "global" system stored procedure that resolves to the database that you are in.