Início / dba / Perguntas / 306210
Accepted
Ronaldo
Asked: 2022-01-19 01:42:40 +0800 CST

Por que o resultado de DATABASEPROPERTYEX não é consistente para AdventureWorks?

  • 772

Estou usando a função DATABASEPROPERTYEX para verificar a propriedade Recovery dos bancos de dados, mas o resultado está errado para o banco de dados AdventureWorks(posteriormente notei o mesmo comportamento estranho para bancos de dados ReportServere ReportServerTempDB).

Aqui está uma versão simplificada do que eu tentei:

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

Criei o procedimento masterusando o prefixo sp_ para poder executá-lo de qualquer banco de dados. Mas quando eu executo ele AdventureWorkso resultado difere do mesmo código rodando sem o procedimento:

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);

Funcionou bem para muitos outros bancos de dados, mas para o AdventureWorks a saída é:

AdventureWorks2016

--Banco de dados em OUTRO modelo de recuperação

AdventureWorks2016

--Banco de dados em modelo de recuperação SIMPLES

O que está causando esse comportamento?

Bancos de dados de exemplo AdventureWorks que usei:

  • AdventureWorks2014.bak
  • AdventureWorks2016.bak

Versões do SQL Server:

  • Microsoft SQL Server 2016 (SP2-GDR) (KB4532097) - 13.0.5102.14 (X64) 31 de dezembro de 2019
  • Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1 (X64) 31 de outubro de 2020
sql-server sql-server-2014

1 respostas

  1. Best Answer

    Eu reescrevi seu proc abaixo:

    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

    Observe que estou ignorando o modelo de recuperação bulk-logged. Este procedimento funciona para mim, no SQL Server 2019. Ou seja, se estou em um banco de dados que está em modo simples, ele retorna SIMPLE e se estou em um banco de dados que está em modo completo, ele retorna FULL.

    Se acima não funcionar para você em 2016, possivelmente houve uma mudança de comportamento entre 2016 e 2019.

    Em alguns casos não basta apenas nomear o procedimento sp_ e tê-lo no banco de dados mestre. Você também precisa marcá-lo como um procedimento do sistema usando o sp_MS_marksystemobject não documentado. Observe que isso não é documentado, então use por sua conta e risco!

    A diferença entre os dois é que, sem sp_MS_marksystemobject, o procedimento é encontrado se você estiver, por exemplo, no Adventureworks, mas é executado no contexto do banco de dados mestre. Se você, por exemplo, selecionar sys.tables, verá as tabelas no banco de dados mestre.

    Com sp_MS_marksystemobject, você informa ao SQL Server para estar no contexto do banco de dados atual ao executar o código em seu procedimento. Ou seja, tabelas do sistema, funções do sistema e afins agora serão resolvidos para usar o banco de dados do usuário em vez do banco de dados mestre.

    Então, por que a mudança de comportamento entre 2016 e 2019? Não podemos dizer, pois estamos em território indocumentado.

    O simples fato é que não há uma maneira com suporte de criar um procedimento armazenado do sistema "global" que resolva para o banco de dados em que você está.

    • 3

relate perguntas