Un piano completo di manutenzione per SQL Server 2008

Piano di manutenzione di SQL Server in grado di eseguire il backup e il ripristino dei file di database su più server, riorganizzare o ricostruire indici dipendenti dal tempo

Ottimizzazione dei database

Questa sezione è dedicata al mantenimento dei database ottimizzati. In questo esempio, intendiamo impostare il codice per eseguire le seguenti operazioni (vedere uno schema ruvido di come i server sono connessi);

Database di backup dal server live a una cartella di backup condivisa
Database di ri-indice
Uccidere tutte le connessioni e ripristinare il database sul server di reporting.
I server collegati verranno presto in un articolo separato
La spedizione del giornale sarà presto in un articolo separato

Quando si esegue il server incrociato, è necessario consentire all'account che SQL Server è in esecuzione dall'accesso al file system dei server di destinazione.

Tutto il codice SQL viene eseguito da un database di utilità e lo schema è impostato su "maint"

Ci sono molti altri articoli che abbiamo scritto qui di seguito che potrebbero interessarti.

Filter

Rimuovere un utente senza lasciare dati orfani in SQL Server

Rimuovere completamente gli utenti da SQL Server senza lasciare dati orfani

Procedura memorizzata per ricostruire o riorganizzare indici di database frammentati

Una procedura memorizzata riutilizzabile che è possibile chiamare per ristabilire qualsiasi database inserendo il nome del database.

Ripristina il database da stored procedure

Spazio su disco e avvisi di posta elettronica di dimensione del database

Creare una stored procedure che invierà lo spazio su disco e gli avvisi di database tramite posta elettronica che è possibile allegare a un lavoro

Procedura memorizzata per eliminare tutte le connessioni al database selezionato

SQL stored procedure per uccidere tutte le connessioni al database selezionato tranne per la persona che lo chiama.

Piano di manutenzione completo

Ora possiamo gestire tutti questi segmenti di codice separati con un'altra procedura che li chiamerà nell'ordine corretto, questo verrà inserito nel server live. Il codice funziona come segue;

Scopri il tempo
Se è la sera reindice il database
Esegui un backup del database (aggiungi dove ti serve)
Se è la sera eseguire il backup delle altre banche dati (aggiungi dove vuoi)
Uccidi i collegamenti e ripristina il database (aggiungi dove vuoi)

Questo codice è stato provato e testato e funzionato per periodi di mesi senza alcun problema.

Si prega di notare che la riduzione dei file di registro e dei file di database deve essere mantenuta al minimo, eseguendo un backup si sta svuotando il registro (anche se conserverà lo spazio utilizzato). Se è necessario quando deve essere eseguito al di fuori delle normali ore di lavoro.

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

Database di backup

Per ottenere il codice per il backup del database, è più semplice scrivere il codice da SSMS.

Seguire il processo che si usa normalmente e quindi selezionare "Azione Script in Nuova finestra di query".

Copia questo codice nel piano di manutenzione.

Database di ri-indice

Quindi possiamo aggiungere un codice per ristabilire il nostro database, questa è una condivisione di stored procedure dove devi solo dire al sistema il nome.

Per evitare la duplicazione dei codici, è possibile leggere sul link riportato di seguito.

More: Procedura memorizzata per ricostruire o riorganizzare indici di database frammentati

Uccidere le connessioni

Quando si esegue un ripristino nel database, è possibile avere solo una connessione (il processo che esegue il ripristino), quindi è possibile creare una stored procedure per chiudere tutte le connessioni a parte il processo corrente. Abbiamo nuovamente creato un articolo separato per questo.

More: Procedura memorizzata per eliminare tutte le connessioni al database selezionato

Ripristina database

Questo codice può anche essere scritto da SQL Server Management Studio. Se si aggiunge questo codice a una stored procedure, è possibile chiamarlo facilmente da altri processi e anche da altri server. Lo abbiamo spostato in un articolo separato per coprire più opzioni.

More: Ripristina il database da stored procedure