Существует великое количество постов, в которых настойчиво призывают к одной простой истине – нужно делать бекапы на постоянной основе. Но люди всегда будут делиться на две категории: кто еще не делает бэкапы, и кто их уже делает. Первая категория, которая пренебрегает такими советами, часто можно встретить на профильных форумах с примерно одинаковыми вопросами:

– у меня полетели диски/кто-то удалил мою базу… как мне восстановить мои данные?
– у вас есть свежий бекап?
– нет

Чтобы не стать героем такой ситуации, нужно потратить минимум усилий. Во-первых, выделить дисковый массив, на который складывать резервные копии. Поскольку, хранить бекапы вместе с файлами БД – явно не наш выбор. Второе… это создать план обслуживания по резервному копированию баз данных.

Что мы и сделаем далее, а после обсудим некоторые тонкости связанные с бекапами.

Создадим таблицу, в которой будут записываться сообщения об ошибках при создании резервных копий:

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

Скрипт для резервного копирования баз данных на каждый день я использую такой:

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 = '\\pub\backup' -- можно задать свой путь для бекапа
IF @path IS NULL -- либо писать в папку для бекапов указанную по умолчанию
    EXEC [master].dbo.xp_instance_regread
            N'HKEY_LOCAL_MACHINE',
            N'Software\Microsoft\MSSQLServer\MSSQLServer',
            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 DATABASE ' + 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

Если на сервере настроен компонент Database Mail, то в скрипт можно добавить уведомление по почте о возникших проблемах:

IF EXISTS(SELECT 1 FROM dbo.BackupError) BEGIN

    DECLARE @report NVARCHAR(MAX)
    SET @report =
        '<table border="1"><tr><th>database</th><th>date</th><th>message</th></tr>' +
        CAST(( 
            SELECT td = db, '', td = dt, '', td = msg
            FROM dbo.BackupError
            FOR XML PATH('tr'), TYPE
        ) AS NVARCHAR(MAX)) +
        '</table>'

    EXEC msdb.dbo.sp_send_dbmail
        @recipients = 'your_account@mail.ru',
        @subject = 'Backup Problems',
        @body = @report,
        @body_format = 'HTML'

END

Собственно, на этом этапе, рабочий скрипт для автоматического создания резервных копий готов. Остается его создать job, который бы по расписанию запускал этот скрипт.

Владельцев Express редакций нужно отдельно упомянуть, поскольку в SQL Server Express edition нет возможности использовать SQL Server Agent. Какая бы печалька не пришла после этих слов, на самом деле, все решаемо. Проще всего создать bat файл с примерно похожим содержанием:

sqlcmd -S <ComputerName>\<InstanceName> -i D:\backup.sql

Далее открыть Task Scheduler и создать в нем новую задачу.



Вторая альтернатива – использовать сторонние разработки, которые позволяют запускать задачи по расписанию. Среди можно выделить SQL Scheduler – удобный и бесплатный тул. Инсталлятор у меня потерялся, поэтому буду благодарен, если кто-то поделиться рабочей ссылкой для читателей.

Теперь поговорим о полезных мелочах связанных с бекапами.

Сжатие…

Возможность сжатия бекапов появилась впервые в SQL Server 2008. Вспоминаю с ностальгией время, когда работая на 2005 версии мне приходилось 7Zip-ом сжимать бекапы. Теперь же все стало намного проще.

Но нужно помнить, что сжатие бекапов будет использоваться только если выполнять команду BACKUP с параметром COMPRESSION или включить сжатие по умолчанию следующей командой:

USE [master]
GO

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

К слову будет сказано, что сжатые бекапы имеет некоторые преимущества: нужно меньше места для их хранения, восстановление БД из сжатых бекапов обычно выполняется чуточку быстрее, также они быстрее создаются, поскольку требуют меньшего количества I/O операций. Минусы, кстати, тоже есть – при работе со сжатыми бекапами нагрузка на процессор увеличивается.

Этим запросом можно вернуть размер последнего FULL бекапа со сжатием и без:

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

Обычно сжатие достигает 40-90%, если не брать во внимание бинарные данные:

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

Если модифицировать предыдущий запрос, то можно мониторить для каких баз делались резервные копии:

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

Если у Вас SQL Server 2005, то эту строку:

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

нужно поменять на:

backup_size = s.backup_size / 1048576.0

Результаты этого запроса могут помочь предотвратить многие проблемы:

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

Можно сразу увидеть, что для всех ли БД есть FULL бекапы за актуальную дату.

Далее можно посмотреть на время создания бэкапа. Зачем спрашивается? Предположим, что раньше бекап базы DB_Dev занимал 5 секунд, а потом стал занимать 1 час. Причин этого может быть много: диски не справляются с нагрузкой, данные в базе выросли до неприличных объемов, полетел диск в RAID и скорость записи снизилась.

Если у базы стоит модель восстановления FULL или BULK_LOGGED, то желательно время от времени делать бекап лога, чтобы не обрекать сервер на муки постоянного роста LDF файла. Степень заполнения файла данных и лога для баз данных можно посмотреть этим запросом:

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

Результаты запроса на моем локальном инстансе:

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

Еще хотел показать пару интересных трюков, которые могут облегчить жизнь. Если при выполнении команды BACKUP указать несколько путей, то конечный файл с бекапом будет разрезан на куски примерно одинакового размера.

BACKUP DATABASE AdventureWorks2012 
TO
	DISK = 'D:\AdventureWorks2012_1.bak',
	DISK = 'D:\AdventureWorks2012_2.bak',
	DISK = 'D:\AdventureWorks2012_3.bak'
GO

Однажды мне это пригодилось, когда пришлось копировать бекап на флешку с файловой системой FAT32, в которой есть ограничение на максимальный размер файла.

Еще одна интересная возможность – создавать копию бекапа. Из личного опыта скажу, что доводилось встречать людей, которые вначале создавали бекап в дефолтной папке, а потом руками или скриптом копировали на дисковую шару. А нужно было просто использовать такую команду:

BACKUP DATABASE AdventureWorks2012 
	TO DISK = 'D:\AdventureWorks2012.bak'
	MIRROR TO DISK = 'E:\AdventureWorks2012_copy.bak'
	WITH FORMAT
GO

Предыдущие посты для автоматизации задач «на каждый день»:

Часть 1: Автоматическая дефрагментация индексов
Часть 2: Автоматическое обновление статистики