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)