0

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
4
  • That is weird. I set recovery for Adventureworks to FULL and I get a consistent result from the proc and executing the SQL directly (ANOTHER for both). I wonder if this was changed between 2016 and 2019 (which I am using). I thought this required that you do the unsupported "mark as system proc" (sp_MS_marksystemobject) but it isn't required for me... Commented Jan 18, 2022 at 10:42
  • @TiborKaraszi, when you ran your tests, did you change the IF comparison to check if it was FULL? Otherwise I don't think it really proves the function works fine when the database is in FULL Recovery model since it was already showing ANOTHER for a database in SIMPLE Recovery model. I believe changing the recovery model to FULL only manipulates the result that was already working to look like the broken one.
    – Ronaldo
    Commented Jan 18, 2022 at 11:33
  • @TiborKaraszi, the sp_MS_marksystemobject seems to have done the trick. Can you guess any reason why it was happening? And what exactly changes in a procedure when SQL Server understand it is a system procedure that would fix this situation?
    – Ronaldo
    Commented Jan 18, 2022 at 12:05
  • See my answer (coming soon)... Commented Jan 18, 2022 at 16:16

1 Answer 1

3

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.

2
  • Thanks for explaining in more detail. Green tick for the solution with sp_MS_marksystemobject, but I experienced a different behavior for the "If you for instance select from sys.tables, you will see the tables in the master database." I see the tables for AdventureWorks as expected not for master. Also, DB_NAME() shows AdventureWorks, meaning it's on the right context. I'm more inclined to believe it was some bug related to DATABASEPROPERTYEX that has already been fixed.
    – Ronaldo
    Commented Jan 18, 2022 at 17:01
  • My explanation is from a more general point of view. Since we are in unsupported territory, things can vhanhe between version without MS communicating the change. That is one if the risks we take when using unsupported functionality Commented Jan 19, 2022 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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