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