Published on

April 12, 2021

Automatizando a Verificação de Consistência do Banco de Dados SQL no SQL Server Express Edition

Realizar verificações regulares de integridade do banco de dados é uma tarefa crítica para os administradores de banco de dados. Isso garante a integridade estrutural e a alocação de todos os objetos e índices do banco de dados. No SQL Server, o comando DBCC CheckDB é comumente usado para identificar qualquer corrupção no banco de dados.

No entanto, revisar os logs gerados pelo comando DBCC CheckDB pode ser tedioso, especialmente ao trabalhar com vários bancos de dados. Para simplificar esse processo, criei uma stored procedure SQL que é executada em todos os bancos de dados e envia os detalhes de quaisquer erros de consistência para a equipe de DBA.

Aqui está um exemplo de como você pode automatizar a verificação de consistência de um banco de dados SQL criado no SQL Server Express Edition:

Passo 1: Restaurar o Banco de Dados

Comece restaurando o banco de dados usando o comando RESTORE DATABASE. Por exemplo:

USE [master]
GO
RESTORE DATABASE [DemoCorruptMetadata] FROM DISK = N'C:\SQL Backup\DemoCorruptMetadata2008R2.bak' WITH FILE = 1, MOVE N'DemoCorruptMetadata' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\DemoCorruptMetadata.mdf', MOVE N'DemoCorruptMetadata_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\DemoCorruptMetadata_log.ldf', NOUNLOAD, STATS = 5

Passo 2: Executar o Comando DBCC CheckDB

Após restaurar o banco de dados, execute o comando DBCC CheckDB para visualizar quaisquer erros. Por exemplo:

SET NOCOUNT ON
GO
USE master
GO
DBCC CHECKDB (DemoCorruptMetadata) WITH ALL_ERRMSGS, NO_INFOMSGS

Se o banco de dados estiver corrompido, o comando de verificação de consistência reportará erros.

Passo 3: Criar a Stored Procedure

Crie uma stored procedure que execute a verificação de consistência em todos os bancos de dados SQL e insira quaisquer erros em uma tabela temporária. Aqui está um exemplo da stored procedure:

USE master
GO
CREATE PROCEDURE Sp_db_integrity_check
AS
BEGIN
    DECLARE @DBName VARCHAR(150)
    DECLARE @dbcccommand NVARCHAR(max)
    DECLARE @DBcount INT
    DECLARE @i INT = 0

    CREATE TABLE #errormsgs (
        error INT NULL,
        level INT NULL,
        state INT NULL,
        messagetext VARCHAR(7000) NULL,
        repairlevel INT NULL,
        status INT NULL,
        dbid INT NULL,
        dbfragid INT NULL,
        objectid INT NULL,
        indexid INT NULL,
        partitionid INT NULL,
        allocunitid INT NULL,
        riddbid INT NULL,
        ridpruid INT NULL,
        [file] INT NULL,
        page INT NULL,
        slot INT NULL,
        refdbid INT NULL,
        refpruid INT NULL,
        reffile INT NULL,
        refpage INT NULL,
        refslot INT NULL,
        allocation INT NULL
    )

    CREATE TABLE #userdatabases (NAME VARCHAR(500))

    INSERT INTO #userdatabases
    SELECT NAME FROM sys.databases WHERE database_id > 4

    SET @DBcount = (SELECT COUNT(1) FROM #userdatabases)

    WHILE (@DBcount > @i)
    BEGIN
        SET @DBName = (SELECT TOP 1 NAME FROM #userdatabases)
        SET @dbcccommand = 'dbcc checkdb (' + @DBName + ') with no_infomsgs, all_errormsgs, tableresults'

        INSERT INTO #errormsgs
        EXEC ('dbcc checkdb (' + @DBName + ') with no_infomsgs, all_errormsgs, tableresults')

        DELETE FROM #userdatabases WHERE NAME = @DBName
        SET @i = @i + 1
    END

    --select MessageText from #errormsgs

    DECLARE @subject NVARCHAR(max)
    DECLARE @tableHTML NVARCHAR(max)

    SET @subject = 'Relatório de Verificação de Consistência do Banco de Dados para o Servidor: ' + @@servername

    SET @tableHTML = '
    
    

Resumo da Verificação de Consistência do Banco de Dados no Servidor ' + @@servername + '

' + CAST(( SELECT ISNULL(messagetext, '') AS 'TD', '', ISNULL(DB_NAME(dbid), '') AS 'TD', '', ISNULL(repairlevel, '') AS 'TD', '' FROM #errormsgs FOR XML PATH('tr'), ROOT ) AS NVARCHAR(max)) + '
MessageText Banco de Dados Corrompido
' EXEC msdb..sp_send_dbmail @profile_name = 'DBMailProfile', @recipients = 'ni*********87@outlook.com', @subject = @subject, @importance = 'High', @body = @tableHTML, @body_format = 'HTML' DROP TABLE #userdatabases END

Passo 4: Configurar o Database Mail e o Agendador de Tarefas do Windows

Como a edição SQL Server Express não suporta o Database Mail e o SQL Server Agent, precisamos configurar o Database Mail usando consultas T-SQL e usar o Agendador de Tarefas do Windows para automatizar a verificação de consistência.

Para obter instruções detalhadas sobre como configurar o Database Mail na edição SQL Server Express, você pode consultar o artigo “Configuração do Database Mail na edição SQL Server Express”.

Para automatizar a verificação de consistência, crie um arquivo em lote que execute a stored procedure usando o comando SQLCMD. Aqui está um exemplo do arquivo em lote:

sqlcmd -S Nisarg-PC\SQLExpress -Q "EXEC sp_DB_Integrity_check" -d DBATools

Salve o arquivo em lote com a extensão .bat.

Em seguida, crie uma tarefa no Agendador de Tarefas do Windows para executar o arquivo em lote em um intervalo especificado. Aqui estão os passos:

  1. Abra o Painel de Controle
  2. Abra as Ferramentas Administrativas
  3. Abra o Agendador de Tarefas
  4. Clique em “Criar uma tarefa básica”
  5. Especifique o nome e a descrição da tarefa
  6. Escolha a frequência (por exemplo, semanalmente)
  7. Especifique o horário de início e a recorrência
  8. Escolha a opção “Iniciar um programa”
  9. Especifique o caminho completo do arquivo em lote
  10. Revise os detalhes da tarefa e clique em “Concluir”

Agora, a verificação de consistência será realizada automaticamente de acordo com o cronograma especificado.

Seguindo essas etapas, você pode automatizar a verificação de consistência de bancos de dados SQL criados ou restaurados na edição SQL Server Express. Isso pode ser especialmente útil para administradores de banco de dados iniciantes que desejam otimizar suas tarefas de manutenção de banco de dados.

No próximo artigo, exploraremos como automatizar a manutenção de índices em bancos de dados SQL criados na edição SQL Server Express usando o Agendador de Tarefas do Windows. Fique ligado!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.