Назад | Перейти на главную страницу

Автоматическое удаление резервных копий и журналов транзакций оставляет неработающие точки резервного копирования

TL; DR: задание доставки журналов по умолчанию и план обслуживания удаляют старые файлы с диска, но резервные копии по-прежнему отображаются в SSMS, поэтому окно «Восстановление» отображается очень медленно. Как правильно удалить старый .trn / .bak?


(Виртуальная машина Azure, Windows Server 2016 Datacenter (10.0), Microsoft SQL Server Web (уровень совместимости 2017), SMMS 17.3)

(даты отображаются в формате ГГГГ-ММ-ДД или ДД-ММ-ГГГГ с 24-часовым форматом)

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

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

  1. Создано полное резервное копирование вручную, так как это необходимо для доставки журналов - здесь ничего не подведет

  2. Сгенерирована задача расписания доставки журнала транзакций по умолчанию с удалением старых файлов. Работает нормально, каждую минуту спамит .trn и удаляет старые файлы с диска

  3. Настройте план обслуживания для создания и удаления старых полных резервных копий тестовых баз данных в полночь - здесь тоже нет проблем

  1. Пусть он работает с пятницы по понедельник, и .trn, и .bak были правильно созданы и удалены, поэтому у меня были резервные копии на два дня, чтобы поиграть:

  2. Пытался восстановить из резервных копий, что вызывало проблемы из-за отсутствия файлов транзакций. Открытие Tasks \ Restore \ Database происходит очень и очень медленно, SMMS очищает 100% диск в течение нескольких минут, чтобы получить ~ 10 МБ пустой базы данных .trn. Для базы данных без автоматического полного резервного копирования в нем перечислены все когда-либо созданные .trn (и в первую очередь ручное полное резервное копирование), очевидно, с ошибками «файл не найден». Для базы данных с ежедневным полным резервным копированием по умолчанию ограниченный результат возвращается к последнее полное резервное копирование и восстановление работали правильно, но по-прежнему очень медленно отображался только список резервных копий. Просмотр временной шкалы также очень медленный (несколько минут «не отвечает»).

Я нашел скрипт для получения истории скриптов в https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/ (слегка изменен, чтобы возвращать все резервные копии интересующей базы данных)

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE 
--(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
--and
 [database_name] = 'test'
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date 

и включает удаленные файлы .trn и .bak:

a и так далее до этой минуты, всего 29682 точки резервного копирования.

Я бы хотел, чтобы окно «Восстановить» снова работало, и у меня есть две тестовые базы данных, в которых я могу свободно тестировать. Могу ли я безопасно очистить список резервных копий? Как я могу сделать это автоматически, чтобы хорошо выполнять текущие задания по расписанию?

У меня есть полные административные привилегии как на db, так и на os, полный творческий контроль и много ресурсов, оставшихся на vm, так что практически никаких ограничений для возможных решений.

Можно использовать sp_delete_backuphistory хранимая процедура для очистки более старой истории. Обратите внимание, что он не знает, что вы удалили или нет, но он очистит более старую историю, чем указанная вами дата X.

Как только вы очистите огромное количество резервных записей, он должен начать реагировать. Это можно легко включить в задание агента, вызвав SP с текущей датой минус X дней в качестве последнего шага в задании.