Um plano de manutenção completo para o SQL Server 2008

Plano de manutenção do SQL Server que pode fazer backup e restaurar arquivos de banco de dados em vários servidores, reorganizar ou reconstruir índices dependentes do tempo

Otimizando bases de dados

Esta seção é dedicada a manter os bancos de dados otimizados. Neste exemplo, vamos configurar o código para fazer o seguinte (veja um diagrama aproximado de como os servidores estão conectados);

Backup do banco de dados do servidor ao vivo para uma pasta de backup compartilhada
Re-Index Database
Mate todas as conexões e restaure o banco de dados no servidor de relatórios.
Servidores vinculados estarão em um artigo separado em breve
Log Shipping será em um artigo separado em breve

Ao fazê-lo cruzar o servidor, você precisará permitir a conta que o SQL Server está executando do acesso ao sistema de arquivos dos servidores de destino.

Todo o código SQL é executado a partir de um banco de dados de utilitários e o conjunto de esquema é "maint"

Há uma série de outros artigos que escrevemos abaixo que podem ser do seu interesse.

Filter

Remova um usuário sem deixar dados orphan no SQL Server

Remova completamente os usuários do SQL Server sem deixar dados orfãos

Procedimento armazenado para matar todas as conexões ao banco de dados selecionado

Procedimento armazenado SQL para matar todas as conexões ao banco de dados selecionado, exceto para a pessoa que o chama.

Procedimento armazenado para reconstruir ou reorganizar índices de banco de dados fragmentados

Um procedimento armazenado reutilizável que você pode chamar para re-indexar qualquer banco de dados, inserindo o nome do banco de dados.

Restaurar banco de dados de procedimento armazenado

Espaço em disco e alertas de email de tamanho de banco de dados

Crie um procedimento armazenado que enviará espaço em disco e alertas de banco de dados por e-mail que você pode anexar a um trabalho

Plano completo de manutenção

Agora, podemos gerenciar todos esses segmentos de código separados com outro procedimento que os chamará na ordem correta, isso será colocado no servidor ao vivo. O código é executado da seguinte maneira;

Estime o tempo
Se é tarde re-indexar o banco de dados
Execute um backup do banco de dados (adicione onde você precisa)
Se for de volta as outras bases de dados (adicione onde precisa)
Mate conexões e restaure o banco de dados (adicione onde você precisa)

Este código foi testado e testado e funcionou por períodos de meses sem problemas.

Observe que o encolhimento de arquivos de log e arquivos de banco de dados deve ser reduzido, executando um backup que está esvaziando o log (embora ele conserve o espaço usado). Se for necessário quando deve ser executado fora do horário normal de trabalho.

SQL Code

Use [utilities]
GO
CREATE PROC [maint].MaintenancePlan AS BEGIN
DECLARE @BackupType VARCHAR(1)='E'
IF DATEPART(HOUR,GETDATE()) BETWEEN 5 AND 21 BEGIN
SET @BackupType='D'
END
--EXEC ('USE TempDb; DBCC SHRINKFILE(templog, 0)');--This is only needed when space is at a premium!
--Re-index Live
IF @BackupType='E' EXEC [maint].DatabaseReIndex 'dbname'
--Create Backup
BACKUP DATABASE TO DISK=N'{backuplocation}{dbname}.bak'
WITH NOFORMAT, INIT, NAME =N'{dbname}', SKIP, NOREWIND, NOUNLOAD, STATS= 10;
--EXEC ('USE ; DBCC SHRINKFILE(_log, 0)');--This is only needed when space is at a premium!
--Backup Other Files at Night
IF @BackupType='E' BEGIN
EXEC [maint].DatabaseReIndex 'dbname'
--Backup Others
BACKUP DATABASE [databasename] TO DISK=N'{backuplocation}{dbname2}.bak'
WITH FORMAT,INIT, NAME =N'{dbname2}',SKIP, NOREWIND, NOUNLOAD, STATS= 10
END

--Restore Backups on other server
EXEC [server].[utilities].[maint].KillConnections 'dbname';

EXEC [server].[utilities].[maint].RestoreDatabase_{dbname};

--Restore Backups on other server for db_2 etc
IF @BackupType='E' BEGIN
EXEC [server].[utilities].[maint].KillConnections 'dbname2';

EXEC [server].[utilities].[maint].RestoreDatabase_{dbname2};

END

END
GO

Banco de dados de backup

Para obter o código para fazer backup de seu banco de dados, é mais fácil script do código do SSMS.

Siga o processo que você usaria normalmente e, em seguida, selecione "Ação do Script para Nova Janela de Consulta".

Copie este código para o plano de manutenção.

Re-Index Database

Em seguida, podemos adicionar algum código para re-indexar nosso banco de dados, isso novamente é um procedimento armazenado compartilhado onde você só precisa dizer ao sistema o nome.

Para evitar a duplicação de código, você pode ler sobre isso no link abaixo.

More: Procedimento armazenado para reconstruir ou reorganizar índices de banco de dados fragmentados

Kill Connections

Ao executar uma restauração no banco de dados, você só pode ter uma conexão com ele (o processo que executa a restauração), portanto, podemos criar um procedimento armazenado para fechar todas as conexões além do processo atual. Nós criamos novamente um artigo separado para isso.

More: Procedimento armazenado para matar todas as conexões ao banco de dados selecionado

Banco de dados de restauração

Este código também pode ser roteado a partir do SQL Server Management Studio. Se você adicionar este código a um procedimento armazenado, você pode chamá-lo de outros processos e até mesmo de outros servidores com muita facilidade. Nós mudamos isso para um artigo separado para cobrir mais opções.

More: Restaurar banco de dados de procedimento armazenado