Создание и хранение резервных копий баз данных в MS SQL. Практические советы

По роду деятельности я немного администратор баз данных. Так или иначе мне приходится обслуживать несколько десятков БД.
Наткнувшись на статью, опубликованную недавно, посчитал нужным дополнить ее некоторыми практическими рекомендациями. То что затрагивалось в прошлой статье, в этой опускалось.

1. Размер резервной копии

Оценить размер резервной копии можно с помощью хранимой процедуры sp_spaceused

К примеру вот такой запрос:

USE your_database;
GO
EXEC sp_spaceused @updateusage = N'TRUE';
GO

выполняется намного быстрей резервного копирования, и позволяет более-менее точно оценить будущий размер бэкапа без сжатия.

Если вы используете MS SQL 2008 или выше, рекомендую вам использовать сжатие резервных копий средствами SQL. Следующий код включит сжатие по умолчанию:

USE master;
GO
EXEC sp_configure ‘backup compression default’, '1';
RECONFIGURE WITH OVERRIDE;

Это позволит уменьшить время создания бэкапов. К примеру, на одной из наших БД размером около 290 ГБ, создание полной резервной копии с включенным сжатием происходит ровно в 2 раза быстрей. А размер конечного файла выходит в 2 раза больше, чем размер архива 7z (максимальное сжатие) резервной копии без изначального сжатия. Ох уж эта магическая цифра 2. Если учесть что процесс архивации занимал 10 часов — выгода огромна.

2. Удаляем старые резервные копии

Еще один плюс использования сжатия SQL, заключается в том, что SQL помнит историю создания резервных копий. А следовательно можно задавать время жизни резервной копии. А старые резервные копии можно удалять, написав *.bat скрипт и вызывать его непосредственно в плане обслуживания:

EXEC master..xp_cmdshell 'path for bat script

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

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

3. Оповещать администратора по почте

Очень удобно получать электронное письмо после создания бэкапа. Для добавления электронной почты оператора делаем так:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'Operator name', 
            @enabled=1, 
            @pager_days=0, 
            @email_address=N'Operator e-mail', 
            @pager_address=N'', 
            @netsend_address=N''
GO

Помимо этого необходимо настроить компонент Database Mail. Сделать это проще всего через среду Management Studio и одноимённого компонента Database Mail



Там всё достаточно просто. Далее в плане обслуживания добавляем соответствующий компонент.

4. Если не усекается журнал транзакций после контрольной точки

При больших операциях вставки в БД, есть смысл переключить режим восстановления на простую, а после вставки данных снова в полную.
У меня, иногда, такие финты проявляются тем, что журнал транзакций перестаёт усекаться. В большинстве случает достаточно сделать 2 раза бэкап журнала транзакций. Но для того что бы не гадать на кофейной гуще, рекомендую выполнить такой запрос:

select log_reuse_wait_desc
from sys.databases
where name = 'your_database'

В ответ получим ответ, о том каких действий SQL ожидает. Если в ответ получим LOG_backup, значит делаем бэкап журнала еще раз.

5. Что можно делать одновременно?

Если БД большая, тяжело так составить расписание, что бы соседние операции не пересекались. А если вдруг пересекаются, чем это чревато?

Вот такая шпаргалка вам поможет. Жирная точка указывает на операции которые нельзя выполнять одновременно.



А еще не забывайте делать бэкапы системных БД. В частности master и msdb.

Литература

• msdn.microsoft.com/ru-ru/library/ms130214%28v=sql.105%29.aspx
• dba.stackexchange.com
• www.sql.ru

В комментариях предлагаю делится своими практическими наработками.

^{P. S. Я новенький, если не в тот хаб запостил — поправьте.}