Home > IT/TLC > SQL Server Backup

SQL Server Backup

Recentemente ho iniziato ad approfondire la mia conoscenza di MS SQL Server ed ho voluto iniziare da un argomento a me caro: il backup.

Normalmente i database di SQL Server vengono salvati con software specifici (per esempio BackupExec) e spesso non ci si preoccupa di creare dei backup intermedi , prima o dopo elaborazioni particolari. Ho implementato una stored procedure per automatizzare questo genere di salvataggi che consente, anche a neofiti o a chi come me si dimentica i parametri per l’esecuzione del backup di SQL, di ottenere un risultato apprezzabile in modo rapido.

Il codice della SP accetta come possibili parametri il percorso di destinazione del backup, qualora non sia specificato verrà identificato il percorso predefinito del motore database, la possibilità di compattare il log file, se non viene specificato il valore di questo parametro l’operazione non viene eseguita, e la possibilità di eseguire la compressione del file di backup mediante appositi software (quali WinZIP o WinRAR).

Per consentire la tracciabilità dei files di backup ho preparato il nome dei files utilizzabili dalla SP in modo da ottenere univocità (posponendo al nome del database corrente la data/ora UTC).

La porzione di codice relativa alla compressione mediante un software esterno impatta sulla protezione del sistema, quindi prestate attenzione all’utilizzo del codice e soprattutto ai programmi che andrete ad eseguire mediante xp_cmdshell perché potreste compromettere l’integrità del sistema (in SQL 2005 e 2008 Microsoft si ha introdotto numerosi accorgimenti per aumentare il livello di sicurezza della piattaforma in modo da ridurre considerevolmente la superficie d’attacco da parte di malicius code o utenti "maldestri").

[USE model]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ============================================================================
-- Author:      Riccardo Verona
-- Create date: 20100101
-- Description: Backup database to destination
-- ============================================================================

CREATE PROCEDURE [BackupArchive]
    @cDestinationPath VARCHAR(MAX) = '',
    @bShrinkDatabase BIT = 0,
    @bPackBackup BIT = 0
AS
BEGIN
-- Dichiaro le variabili...
    DECLARE @CurrentDBName VARCHAR(MAX);
    DECLARE @TargetPath VARCHAR(MAX);
    DECLARE @CompactCommand sysname;
    DECLARE @RARFileName VARCHAR(MAX);
    DECLARE @ArchiveFiles VARCHAR(MAX);
    DECLARE @DataFileName VARCHAR(MAX);
    DECLARE @LogFileName VARCHAR(MAX);
    DECLARE @TimeStamp CHAR(25);
    DECLARE @AdvOptionsState INT;
    DECLARE @XpCmdShellState INT;

-- Identifico il database corrente...
    SELECT @CurrentDBName = DB_NAME();

-- Verifico l'imputazione del percorso di destinazione, se non specificato cerco il percorso predefinito
    IF RTRIM(@cDestinationPath) = ''
    BEGIN
         DECLARE @SQLInstances Table (Value CHAR(255), Data varchar(max))
         INSERT INTO @SQLInstances EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'
        SELECT @TargetPath = Data FROM @SQLInstances
    END
    ELSE
        SET @TargetPath = RTRIM(@cDestinationPath)

-- Preparo I nomi dei files che potrei utilizzare durante la procedura...
    SET @TimeStamp = REPLACE(REPLACE(REPLACE(CONVERT(CHAR(25), GETUTCDATE(), 127), '-', ''), ':', ''), '.', '');
    SET @RARFileName = RTRIM(RTRIM(@CurrentDBName) + '-' + RTRIM(@TimeStamp) + '.RAR');
    SET @ArchiveFiles = RTRIM(RTRIM(@CurrentDBName) + '-' + RTRIM(@TimeStamp) + '.*');
    SET @DataFileName = @TargetPath + RTRIM('\' + RTRIM(@CurrentDBName) + '-' + RTRIM(@TimeStamp) + '.BAK');
    SET @LogFileName = @TargetPath + RTRIM('\' + RTRIM(@CurrentDBName) + '-' + RTRIM(@TimeStamp) + '.TRN');

-- Eseguo il commando di salvataggio per il file dei dati...
    BACKUP DATABASE @CurrentDBName TO DISK = @DataFileName WITH INIT, NAME = @CurrentDBName 
-- ... e per il file di log...
    BACKUP LOG @CurrentDBName TO DISK = @LogFileName WITH INIT, NOUNLOAD, NAME = @CurrentDBName, NOSKIP, STATS = 10, NOFORMAT 
-- Verifico se è richiesta la compattazione dei files di log...
    IF @bShrinkDatabase = 1
    BEGIN
         DBCC SHRINKDATABASE(@CurrentDBName);
    END;
                
-- Verifico se è stata richiesta la compressione con un programma (p.e. WinZip o WinRAR)
    IF @bPackBackup = 1
    BEGIN
-- Per l'esecuzione di comandi esterni al motore database (quali I comandi shell) è necessario abilitarle
-- ... in prima istanza verifico la configurazione delle opzioni avanzate...
        DECLARE @SQLOptions Table (name varchar(max), minimum int, maximum int, config_value int, run_value int)
        INSERT INTO @SQLOptions EXEC [master]..sp_configure
        SELECT @AdvOptionsState = [config_value] FROM @SQLOptions WHERE [name] = 'show advanced options'

        IF @AdvOptionsState = 0
        BEGIN
            EXEC [master]..sp_configure 'show advanced options', 1;
            RECONFIGURE WITH OVERRIDE;
        END;

-- ... successivamente controllo lo stato dell'opzione di esecuzione di comandi shell...
        INSERT INTO @SQLOptions EXEC [master]..sp_configure
        SELECT @XpCmdShellState = [config_value] FROM @SQLOptions WHERE [name] = 'xp_cmdshell'
        IF @XpCmdShellState = 0 BEGIN
            EXEC [master]..sp_configure 'xp_cmdshell', 1;
            RECONFIGURE WITH OVERRIDE;
        END;
-- ... preparo ed eseguo il comando
        SET @CompactCommand = 'C:\CompactDB.bat ' + @TargetPath + ' ' + @RARFileName + ' ' + @ArchiveFiles
        PRINT @CompactCommand
        EXEC [master]..xp_cmdshell @CompactCommand
-- ... ripristino le opzioni allo stato precedente...
        EXEC [master]..sp_configure 'xp_cmdshell', @XpCmdShellState
        RECONFIGURE WITH OVERRIDE
        EXEC [master]..sp_configure 'show advanced options', @AdvOptionsState
        RECONFIGURE WITH OVERRIDE
    -- Lo script 'CompactDB.bat' va creato, per comodità nella root del disco(così che sia possibile ottimizzare la stringa del comando SQL):       
    -- @ECHO OFF
    -- @ECHO Compressione Archivi...
    -- @CD "C:\Program Files\WinRAR"
    -- @RAR.exe a -ac -df -m5 -y %1\%2 %1\%3
    -- @ECHO Compressione Archivi terminata.
    END;
END
GO
 

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Annunci
Categorie:IT/TLC Tag:,
  1. Non c'è ancora nessun commento.
  1. No trackbacks yet.

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger hanno fatto clic su Mi Piace per questo: