Создание и хранение резервных копий баз данных в 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.

Ссылки по теме

• Купить Microsoft Windows Server Datacenter 2012 R2
• Купить Microsoft Windows Server Standard 2012
• Купить Windows Server 2012 R2
• Купить Microsoft: Серверные программы (SQL Server)