Planes típicos de mantenimiento de SQL Server: copia de seguridad automatizada de la base de datos

Hay muchos mensajes que exigen una simple verdad: debe realizar copias de seguridad con regularidad. Las personas siempre se dividirán en dos categorías: las que hacen copias de seguridad y las que no. La primera categoría, que ignora este consejo, a menudo se puede encontrar en foros relevantes con aproximadamente las mismas preguntas:

- Mis discos han fallado / alguien borró mi base de datos… ¿Cómo puedo recuperar mis datos?
- ¿Tienes la última copia de seguridad?
- No.

Para evitar esta desagradable situación, es necesario hacer un mínimo esfuerzo. Lo primero que debe hacer es elegir una matriz de discos para realizar la copia de seguridad. Debido a que respaldar junto con los archivos de la base de datos no es nuestra elección, el segundo paso será crear un plan de mantenimiento de respaldo de la base de datos.

A continuación, analizaremos algunas de las complejidades asociadas con la copia de seguridad.

Vamos a crear una tabla para registrar los mensajes de error de copia de seguridad:

USE [master]
GO

IF OBJECT_ID('dbo.BackupError', 'U') IS NOT NULL
    DROP TABLE dbo.BackupError
GO

CREATE TABLE dbo.BackupError (
    db SYSNAME PRIMARY KEY,
    dt DATETIME NOT NULL DEFAULT GETDATE(),
    msg NVARCHAR(2048)
)
GO

Puede usar el siguiente script para hacer una copia de seguridad de su base de datos diariamente:

USE [master]
GO

SET NOCOUNT ON
TRUNCATE TABLE dbo.BackupError

DECLARE
      @db SYSNAME
    , @sql NVARCHAR(MAX)
    , @can_compress BIT
    , @path NVARCHAR(4000)
    , @name SYSNAME
    , @include_time BIT

--SET @path="\pubbackup"
IF @path IS NULL
    EXEC [master].dbo.xp_instance_regread
            N'HKEY_LOCAL_MACHINE',
            N'SoftwareMicrosoftMSSQLServerMSSQLServer',
            N'BackupDirectory', @path OUTPUT, 'no_output'

SET @can_compress = ISNULL(CAST((
    SELECT value
    FROM sys.configurations
    WHERE name="backup compression default") AS BIT), 0)

DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
    SELECT d.name
    FROM sys.databases d
    WHERE d.[state] = 0
        AND d.name NOT IN ('tempdb')

OPEN cur

FETCH NEXT FROM cur INTO @db

WHILE @@FETCH_STATUS = 0 BEGIN

    IF DB_ID(@db) IS NULL BEGIN
        INSERT INTO dbo.BackupError (db, msg) VALUES (@db, 'db is missing')
    END
    ELSE IF DATABASEPROPERTYEX(@db, 'Status') != 'ONLINE' BEGIN
        INSERT INTO dbo.BackupError (db, msg) VALUES (@db, 'db state != ONLINE')
    END
    ELSE BEGIN

        BEGIN TRY

            SET @name = @path + 'T' + CONVERT(CHAR(8), GETDATE(), 112) + '_' + @db + '.bak'
            SET @sql="
                BACKUP Base de datos " + QUOTENAME(@db) + '
                TO DISK = ''' + @name + ''' WITH NOFORMAT, INIT' + 
                CASE WHEN @can_compress = 1 THEN ', COMPRESSION' ELSE '' END

            --PRINT @sql
            EXEC sys.sp_executesql @sql

        END TRY
        BEGIN CATCH
            INSERT INTO dbo.BackupError (db, msg) VALUES (@db, ERROR_MESSAGE())
        END CATCH

    END

    FETCH NEXT FROM cur INTO @db
END

CLOSE cur
DEALLOCATE cur

Cuándo base de datos de correo está configurado en el servidor, puede agregar una notificación por correo electrónico a la secuencia de comandos sobre los problemas que pueden ocurrir:

IF EXISTS(SELECT 1 FROM dbo.BackupError) BEGIN

    DECLARE @report NVARCHAR(MAX)
    SELECT @report = STUFF((
    	SELECT '
        ' + db + ' (' + CAST(dt AS VARCHAR(20)) + '):' + msg
        FROM dbo.BackupError
    	FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

    EXEC msdb.dbo.sp_send_dbmail
        @recipients="[email protected]",
        @subject="Backup Problems",
        @body = @report,
        @body_format="HTML"

END

En este punto, el script de copia de seguridad en funcionamiento está listo para usarse. Solo queda crear una tarea que ejecute este script según lo programado.

Vale la pena señalar a los propietarios de las publicaciones Express, como Servidor SQL Express la edición no ofrece la posibilidad de uso Agente de servidor SQL. Por triste que parezca, en realidad se puede resolver fácilmente. La forma más fácil es crear un archivo bat con contenido similar:

sqlcmd -S  -i D:backup.sql

A continuación, abra el archivo Programador de tareas y crear una nueva tarea en él.

Programador de tareas

La segunda opción es utilizar un software de terceros que le permita realizar tareas programadas. Entre ellos están Programador de SQL - una práctica herramienta gratuita.

Ahora hablemos de las cosas útiles relacionadas con la copia de seguridad.

Compresión de copia de seguridad

Función de compresión de copia de seguridad ahora: Servidor SQL 2008 (Edición Enterprise/Desarrollador solamente), 2008R2 / 2012/2014 (Solo en las ediciones Enterprise, Business Intelligence, Standard y Developer). Hubo un tiempo en que trabajaba con Servidor SQL 2005Tuve que comprimir copias de seguridad con 7Zip. Ahora todo es mucho más fácil.

Debemos tener en cuenta que la compresión de respaldo solo se utilizará al ejecutar un archivo TAPÓN DE RESERVA equipo con COMPRESIÓN o habilite la compresión predeterminada con el siguiente comando:

USE [master]
GO

EXEC sp_configure 'backup compression default', 1
RECONFIGURE WITH OVERRIDE
GO

Por cierto, las copias de seguridad comprimidas tienen una serie de ventajas:
1) necesitas menos espacio para almacenarlos;
2) La recuperación de bases de datos a partir de copias de seguridad comprimidas suele llevar menos tiempo;
3) Las copias de seguridad comprimidas son más rápidas y requieren menos operaciones de E/S.

Hay varias desventajas: cuando se trabaja con copias de seguridad comprimidas, aumenta la carga en el procesador.

La siguiente consulta devuelve el tamaño de este último. COMPLETO copia de seguridad tanto comprimida como sin comprimir:

SELECT
      database_name
    , backup_size_mb = backup_size / 1048576.0
    , compressed_backup_size_mb = compressed_backup_size / 1048576.0
    , compress_ratio_percent = 100 - compressed_backup_size * 100. / backup_size
FROM (
   SELECT
          database_name
        , backup_size
        , compressed_backup_size = NULLIF(compressed_backup_size, backup_size)
        , RowNumber = ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date DESC)
    FROM msdb.dbo.backupset
    WHERE [type] = 'D'
) t
WHERE t.RowNumber = 1

Normalmente la compresión llega al 40-90%, dejando de lado los datos binarios:

database_name                   backup_size_mb   compressed_backup_size_mb  compress_ratio_percent
------------------------------- ---------------- -------------------------- ------------------------
AdventureWorks2012              190.077148437    44.652716636               76.50810894222767
DB_Dev                          1530.483398437   295.859273910              80.66890015190163
BinDocuments                    334.264648437    309.219978332              7.49246748707956
locateme                        37.268554687     17.247792243               53.72025454546944
master                          3.643554687      0.654214859                82.04459888434736
model                           2.707031250      0.450525283                83.35721895292208
msdb                            17.147460937     2.956551551                82.75807967958028
OnlineFormat                    125.078125000    23.639108657               81.10052524545207
Refactoring                     286.076171875    35.803841590               87.48450758543927
ReportServer$SQL_2012           4.045898437      0.696615219                82.78218719828627
ReportServer$SQL_2012TempDB     2.516601562      0.428588867                82.96953822273962

Si cambia la consulta anterior, puede realizar un seguimiento de las bases de datos que se respaldaron:

SELECT
      d.name
    , rec_model = d.recovery_model_desc
    , f.full_time
    , f.full_last_date
    , f.full_size
    , f.log_time
    , f.log_last_date
    , f.log_size
FROM sys.databases d
LEFT JOIN (
    SELECT
          database_name
        , full_time = MAX(CASE WHEN [type] = 'D'
            THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END)
        , full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
        , full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
        , log_time = MAX(CASE WHEN [type] = 'L'
            THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END)
        , log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
        , log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
    FROM (
        SELECT
              s.database_name
            , s.[type]
            , s.backup_start_date
            , s.backup_finish_date
            , backup_size =
                CASE WHEN s.backup_size = s.compressed_backup_size
                            THEN s.backup_size
                            ELSE s.compressed_backup_size
                END / 1048576.0
            , RowNum = ROW_NUMBER() OVER (
                PARTITION BY s.database_name, s.[type]
                ORDER BY s.backup_finish_date DESC
            )
        FROM msdb.dbo.backupset s
        WHERE s.[type] IN ('D', 'L')
    ) f
    WHERE f.RowNum = 1
    GROUP BY f.database_name
) f ON f.database_name = d.name

Si utiliza Servidor SQL 2005que esta línea:

backup_size = CASE WHEN s.backup_size = s.compressed_backup_size THEN ...

debe cambiarse a:

backup_size = s.backup_size / 1048576.0

Los resultados de esta consulta pueden ayudar a prevenir muchos problemas:

name                         rec_model full_time full_last_date      full_size         log_time  log_last_date       log_size
---------------------------- --------- --------- ------------------- ----------------- --------- ------------------- ------------
master                       SIMPLE    00:00:01  2015-11-06 15:08:12 0.654214859       NULL      NULL                NULL
tempdb                       SIMPLE    NULL      NULL                NULL              NULL      NULL                NULL
model                        FULL      00:00:00  2015-11-06 15:08:12 0.450525283       NULL      NULL                NULL
msdb                         SIMPLE    00:00:00  2015-11-06 15:08:12 2.956551551       NULL      NULL                NULL
ReportServer$SQL_2012        FULL      00:00:01  2015-11-06 15:08:13 0.696615219       NULL      NULL                NULL
ReportServer$SQL_2012TempDB  SIMPLE    00:00:00  2015-11-06 15:08:13 0.428588867       NULL      NULL                NULL
DB_Dev                       FULL      00:00:13  2015-11-06 15:08:26 295.859273910     00:00:04  2015-11-01 13:15:39 72.522538642
BinDocuments                 FULL      00:00:05  2015-11-06 15:08:31 309.219978332     00:00:01  2015-11-06 13:15:39 2.012338638
Refactoring                  SIMPLE    00:00:02  2015-11-06 15:08:33 35.803841590      NULL      NULL                NULL
locateme                     SIMPLE    00:00:01  2015-11-06 15:08:34 17.247792243      NULL      NULL                NULL
AdventureWorks2012           FULL      00:00:02  2015-11-06 15:08:36 44.652716636      NULL      NULL                NULL
OnlineFormat                 SIMPLE    00:00:01  2015-11-06 15:08:39 23.639108657      NULL      NULL                NULL

Puede ver inmediatamente si todas las bases de datos tienen copias de seguridad COMPLETAS a la fecha actual.

A continuación, puede consultar la hora de la copia de seguridad. Usted puede preguntar por qué? Suponga que la copia de seguridad de la base de datos DB_Dev tomó 5 segundos y luego duró 1 hora. Las razones de esto pueden ser diferentes: los discos no pueden soportar la carga, los datos en la base de datos han crecido enormemente, el disco falló en RAID y disminuyó la velocidad de escritura.

Si la base de datos contiene un archivo COMPLETO o BULK_LOGGED modelos de recuperación, es recomendable hacer una copia de seguridad del registro de vez en cuando para evitar que el servidor crezca constantemente LDF Archivo. Puede ver qué tan completos están el archivo de datos y el registro para la base de datos con esta consulta:

IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
    DROP TABLE #space

CREATE TABLE #space (
    database_id INT PRIMARY KEY,
    data_used_size DECIMAL(18,6),
    log_used_size DECIMAL(18,6)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #space (database_id, data_used_size, log_used_size)
    SELECT
          DB_ID()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

SELECT 
      database_name = DB_NAME(t.database_id)
    , t.data_size
    , s.data_used_size
    , t.log_size
    , s.log_used_size
    , t.total_size
FROM (
    SELECT
          database_id
        , log_size = SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024
        , data_size = SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024
        , total_size = SUM(size) * 8. / 1024
    FROM sys.master_files
    GROUP BY database_id
) t
LEFT JOIN #space s ON t.database_id = s.database_id

Resultados de la consulta en mi local Una instancia de SQL Server:

database_name                  data_size     data_used_size  log_size      log_used_size  total_size
------------------------------ ------------- --------------- ------------- -------------- --------------
master                         4.875000      3.562500        1.750000      0.781250       6.625000
tempdb                         8.000000      4.500000        0.500000      0.632812       8.500000
model                          4.062500      2.562500        1.250000      0.609375       5.312500
msdb                           16.687500     16.062500       26.187500     2.804687       42.875000
ReportServer$SQL_2012          4.062500      3.937500        10.125000     1.570312       14.187500
ReportServer$SQL_2012TempDB    4.062500      2.437500        1.312500      0.500000       5.375000
DB_Dev                         1782.812500   1529.562500     7286.125000   42.570312      9068.937500
BinDocuments                   334.000000    333.500000      459.125000    12.031250      793.125000
Refactoring                    333.125000    285.625000      127.882812    0.851562       461.007812
locateme                       591.000000    36.500000       459.125000    8.585937       1050.125000
AdventureWorks2012             205.000000    189.125000      0.750000      0.453125       205.750000
OnlineFormat                   125.375000    124.437500      1.015625      0.414062       126.390625

También hay un par de trucos divertidos que pueden hacerte la vida más fácil. Especifique varias rutas durante el comando BACKUP y el archivo de copia de seguridad resultante se cortará en partes de aproximadamente el mismo tamaño:

BACKUP Base de datos AdventureWorks2012 
TO
    DISK = 'D:AdventureWorks2012_1.bak',
    DISK = 'D:AdventureWorks2012_2.bak',
    DISK = 'D:AdventureWorks2012_3.bak'
GO

Esto puede ser útil si está realizando una copia de seguridad en una unidad flash USB con un sistema de archivos FAT32que tiene un límite en el tamaño máximo de archivo.

Otra característica interesante es la copia de seguridad (solo Enterprise / Developer). En lugar de hacer una copia de seguridad en la carpeta predeterminada y copiar manualmente el script en una carpeta compartida, simplemente puede usar el siguiente comando:

BACKUP Base de datos AdventureWorks2012 
    TO DISK = 'D:AdventureWorks2012.bak'
    MIRROR TO DISK = 'E:AdventureWorks2012_copy.bak'
    WITH FORMAT
GO

Artículos de interés

Subir