Criar backup agendado e Shrink através do SQL Server Agent

Introdução

As vezes um banco de dados SQL Server ocupa muito mais espaço do que realmente é usado pelos dados ou jamais seria usado. Em tais situações, seria ideal reduzir o tamanho do banco de dados e devolver o espaço de armazenamento ao sistema operacional. Recomendamos que você faça backups de log com frequência para minimizar exposição à perda de trabalho e para truncar o log de transações.

Backup agendado usando um Job do SQL Server Agent

Antes de iniciar o procedimento no banco de dados, crie uma pasta Backup no diretório  de sua preferência; 

Para automatizar e agendar um backup com o SQL Server Agent siga as instruções abaixo:

1 - No painel Pesquisador de Objetos, no nó SQL Server Agent, clique com o botão direito do mouse em "Jobs" (Trabalhos) e selecione "New Job" (Novo trabalho) menu de contexto:

2 - Na caixa de diálogo "New Job" (Novo trabalho), insira o nome e a descrição, os detalhes do proprietário e da categoria são inseridos por padrão.

3 - Na guia "Steps" (Etapas), clique no botão "New" (Novo) e crie um "Steps" (Etapa) de backup inserindo uma instrução T-SQL. Nesse caso, a cláusula CHECKSUM deve ser incluída no código T-SQL conforme exemplo:

USE [nome_banco];

BACKUP DATABASE [nome_banco]

  TO  DISK = N'C:\Backup\Data\nome_banco_bkp.bak'

WITH CHECKSUM;

BACKUP LOG [nome_banco]

   TO  DISK = N'C:\Backup\LOG\nome_banco_bkp.log';

GO

Às vezes, o nome do banco de dados e do arquivo de log podem variar, portanto, primeiro é necessário confirmar os nomes do banco e do arquivo de log. Abaixo lhe daremos instruções de como consultar estes dados.

Expanda o banco de dados, clique com o botão direito do mouse e vá para Propriedades. Em seguida, clique em “Files" (Arquivos) na janela Propriedades do banco de dados e confirme o nome do banco de dados e o nome do arquivo de log na coluna "Logical Name" (Nome lógico) conforme exemplo abaixo:

Após ter consultado o nome "logical name" (nome lógico), siga com as instruções abaixo para agendar o backup.

 Na etapa abaixo, insira o nome do "step name" (nome da etapa), o "type" (tipo) é inserido por padrão, selecione a "database" (base de dados), ressaltando que  o comando a ser executado deverá constar o mesmo nome da database (base de dados) escolhida.

Após ter criado o passo 1, será exibido desta forma na janela abaixo:

Veja na imagem acima que foi criado o passo 1 do backup, sendo necessário criar o passo 2 para execução do comando shrink.

Para reduzir o log do banco de dados, precisamos escrever a consulta SQL. A sintaxe e a consulta SQL para reduzir o log do banco de dados são fornecidas abaixo.

ALTER DATABASE nome_banco SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE([nome_banco_log], 0, TRUNCATEONLY)

ALTER DATABASE nome_banco SET RECOVERY FULL WITH NO_WAIT

GO

 

4 - Para agendar um "Job" (Trabalho), na caixa de diálogo "New Job" (Novo Trabalho), na guia "Schedules" (Cronograma), para criar novo cronograma clique em "New" (Novo) ou "Pick" (escolher) para selecionar uma agenda já existente no banco de dados.

5 - No "Schedules" (cronograma) de "job" (trabalho), selecione uma frequência de ocorrência e uma data de início e clique em OK:

Para verificar um job (trabalho) criado no painel Pesquisador de Objetos e no nó SQL Server Agent ➜ Jobs, siga as instruções abaixo:

Clique em "View Job History" (Ver histórico de trabalhos), abrirá a visualização dos arquivo de log permitindo verificar a execução com êxito.

Após a execução, recebemos uma mensagem de sucesso e se tivermos algum erro em nosso script, receberemos uma mensagem de erro.

É possível reduzir os arquivos com extensão .mdf, porém a redução cria uma fragmentação de índice severa, além de ser uma operação que consome muitos recursos e pode afetar significativamente o desempenho do banco de dados gerando impactos negativos simultâneos no sistema.

Vale lembrar que não é recomendada a redução regularmente do banco de dados sem uma razão justificada. Apenas em circunstâncias especiais e de maneira direcionada, nunca como parte de algum processo automatizado.

Se for necessário reduzir o arquivo de dados,  execute o comando abaixo:

USE [DatabaseName];

DBCC SHRINKDATABASE (DatabaseName,TRUNCATEONLY)

Para mais informações, clique aqui.

• Related Articles

• Plano de Manutenção do SQL Server

  Introdução Os Planos de Manutenção fornecem uma GUI fácil de usar e não exigem scripts escritos manualmente. A desvantagem é que as tarefas fornecidas são básicas e não deixam espaço para personalização. Um Plano de Manutenção também é atômico e, ...

• 5.3 - Instalação Agent Fleet Server

  Visão geral O Elastic Agent é uma maneira unificada de adicionar monitoramento de logs, métricas e outros tipos de dados a um host. Ele também pode proteger hosts contra ameaças de segurança, consultar dados de sistemas operacionais e muito mais. Um ...

• 5.4 - Criar/Configurar APM Server no Fleet

  Para ativar o APM, vamos criar uma nova Policy do Fleet Server, para isso clique na aba "Agent policies". Clique em "Add integration" e procure por "Elastic APM". Seleciona a aba "Elastic APM in Fleet" e depois clique em "APM integration". Adicione ...

• 1.2 - Configuração SQL Server

  Comunicação Externa  Para que a comunicação tenha êxito, siga as instruções de configurações abaixo: Sobre o firewall, libere a porta padrão do SQL 1433; se necessário crie regra de acesso a porta especifica. libere no antivirus a porta padrao do SQL ...

• 1.1 - Instalação do SQL Server e Management Studio

  Neste artigo, vamos mostrar como baixar e instalar o sistema de gerenciamento de bancos de dados relacionais SQL Server 2019 no Windows, além do pacote de gerenciamento SQL Server Management Studio. Primeiro passo é obter os pacotes de instalação, ...