Существует великое количество постов, в которых настойчиво призывают к одной простой истине – нужно делать бекапы на постоянной основе. Но люди всегда будут делиться на две категории: кто еще не делает бэкапы, и кто их уже делает. Первая категория, которая пренебрегает такими советами, часто можно встретить на профильных форумах с примерно одинаковыми вопросами:
– у меня полетели диски/кто-то удалил мою базу… как мне восстановить мои данные?
– у вас есть свежий бекап?
– нет
– тогда никак…
Чтобы не стать героем такой ситуации, нужно потратить минимум усилий. Во-первых, выделить дисковый массив, на который складывать резервные копии. Поскольку, хранить бекапы вместе с файлами БД – явно не наш выбор. Второе… это создать план обслуживания по резервному копированию баз данных.
Создадим таблицу, в которой будут записываться сообщения об ошибках при создании резервных копий:
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 ссылка на оригинал статьи http://habrahabr.ru/post/270401/
Добавить комментарий