SQL Server бэкап базы данных

#sql #база_данных #sql_server

Я работаю с базой данных на SQL Server, ее размер около 100 Gb. Чтобы уменьшить риск
потери данных выполняю бэкапы по расписанию при помощи сторонней программы. 

Как вы думаете бэкапы - это хороший способ чтобы защитить данные или я что -то упускаю
и нужно это добавить? 
    

Ответы

Ответ 1

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

Что такое "полный бэкап"? Это полная копия базы в конкретный момент времени. Если
вы делаете только полные бэкапы - то в случае чего вы потеряете все данные с момента
полного бэкапа. А делать полные бэкапы часто вам не даст размер вашей базы.

К счастью, SQL Server работает с данным не только на уровне "записать в базу". В
нем есть еще и фишка под названием "лог транзакций" и "full recovery mode".

Лог транзакций - это .ldf файл, по умолчанию лежащий рядом с .mdf.

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


приходит запрос от клиента INSERT что-то там
SQL Server меняет данные в памяти и по-быстрому записывает сам факт вставки в лог
транзакций (ldf), просто дописывая его в конц лога.
SQL Server отчитывается клиенту "все ок, запись вставлена".
Раз в минуту SQL Server делает checkpoint - записывает на диск сами измененные данные
(mdf).
SQL Server дописывает в лог "изменения по таким-то транзакциям сохранены на диск".


В Full Recovery Mode файл лога хранит в себе все изменения, внесенные с момента создания
базы. В том порядке, в котором их вносили.

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

(Кстати, тот факт, что вы не бэкапите лог, скорее всего означает что ваша база и
ваш полный бэкап сейчас весит в 2 раза больше, чем положено).

Суть всего этого - имея полный бэкап базы недельной давности + бэкап логов, снятый
5 минут назад вы можете восстановить состояние базы на любой момент в течении этой недели. 

Ваша схема бэкапов должна выглядеть так:


Полный бэкап базы раз в неделю.
Бэкап логов раз в 5-15 минут/час, в зависимости от допустимых потерь.


Эта схема легко настраивается через стандартный механизм Maintenance Plan (если у
вас не Express). Или вашей сторонней утилитой - она наверняка умеет поддерживать бэкап
логов.



Кроме того, если у вас SQL Server Standard или выше, то в нем есть встроенный Managed
Backup, который автоматичеcки настраивает эту схему, подбирая интервалы исходя из объемов
данных + включет сжатие бэкапов + льет бэкапы в Azure Storage. Т.е. решает проблему
поддержания бэкапов с минимальными расходами на их организацию :)


Ответ 2

Это зависит от того, от чего Вы защищаетесь. Если вы боитесь потерять данные, то
да, бекапов достаточно. Если же хотите защититься от кражи данных, то надо думать о
других средствах безопасности. Классический пример: хранение паролей в хеше.

UPD. Только бекапы лучше всего хранить на другой машине!


Ответ 3

Добрый день.

Крайне рекомендую в качестве решения для сохранения и проверки бэкапов в SQL Server
использовать легендарный набор скрпиптов https://ola.hallengren.com/ (потратьте пару
часов на их изучение, столько же на настройку и о проблеме бэкапов в SQL Server можно
беспокоиться значительно меньше).

Теперь о самой методологии бэкапа. Выделите 3 часа и прочитайте обязательно следующие
4 статьи (осторожно английский): Help, my database is corrupt. Now what?, What to Do
When DBCC CHECKDB Reports Corruption, Questions You Should Ask About the Databases
You Manage и самая важная в контексе вашего вопроса The 9 Letters That Get DBAs Fired.

От себя добавлю: обязательно в письменном виде согласуйте с вашим начальником (или
начальником начальника) самые главные метрики:


RPO (Recovery Point Objective) - это минимальное время (в минутах или часах) в течении
которого допустима потеря данных в вашей базе данных (т.е. если случилась беда в 12:59
дня, а вы делаете бэкапы каждые пол часа, то вы потеряете все данные, которые были
изменены с 12:30)
RTO (Recovery Time Objective) - сколько времени необходимо на восстановление бэкапа


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

P.S. Не совсем по теме бэкапов, но по моему мнению крайне полезные скрпипты для мониторинга
проблем с SQL Server абсолютно бесплатно можно найти здесь: Github Brent, а дополнительные
материалы по углублению в мир SQL Server здесь: Github sqlserver-kit


Ответ 4

Бэкап не спасёт вас от потери уже совершенных транзакций.
Пусть у вас бэкап раз в сутки, в течении суток у вас накупили товара на одил биллион
рублей.
В ваш сервер ударила молния:)
Чем в этом случае вам бэкап поможет? Лучше чем ничего, конечно.
Нужно думать о рисках и рассматривать варианты их минимизации..
Помимо бэкапов есть точки сохранения БД(по стуи моменты окончания транзакций), которые
весят намного меньше, чем полный бекап.
итого бэкап + некоторое количество точек сохранения смогут вам вернуть согласованное
состояние БД перед ударом молнии.