☰

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

tag-icon • tag-icon sql-server backup-restoration ssms incremental-backup
Автоматическое удаление резервных копий и журналов транзакций оставляет неисправные точки резервного копирования

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

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

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

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

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

  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: введите описание изображения здесь

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

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

У меня есть полные права администратора как в БД, так и в ОС, полный контроль над творчеством и достаточно ресурсов на виртуальной машине, так что ограничений на возможные решения практически нет.

решение1

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

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

Связанный контент