Небольшой скрипт для подчистки резервных копий. Может кому пригодится.
[more=Читать дальше..]
Код:
--*****************************************************************************
--Обход всех не нужных копий с удалением файлов и данных из истории копирования
--*****************************************************************************
--Разрешаем команды ОС
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
USE msdb
--Переменные определяющие горизонт хранения для полных копий
DECLARE
@DailyCopies int = 30, -- сколько дней хранить ежедневные полные копии
@MonthCopies int = 365, --сколько дней хранить копии на первые числа месяцев которые старше @DailyCopies
@QuarterCopies int = 365*5, --сколько дней хранить квартальные копии которые старше @MonthCopies
@physical_device_name nvarchar(260),
@BackupSize2Save numeric(20,0),
@BackupSize2Del numeric(20,0),
@FilesSize2Save numeric(20,0),
@FilesSize2Del numeric(20,0),
@TotalBackupSize numeric(20,0),
@TotalFilesSize numeric(20,0)
--Переменные-таблицы для хранения данных истории резервного копирования
DECLARE @saved_backup_set_id TABLE (backup_set_id INT)
DECLARE @backup_set_id TABLE (backup_set_id INT)
DECLARE @media_set_id TABLE (media_set_id INT)
DECLARE @restore_history_id TABLE (restore_history_id INT)
--Получаем список сохраняемых полных копий (для 30-ти дневных сохраняем разностные и журналы)
--копии не старше @DailyCopies
INSERT INTO @saved_backup_set_id (backup_set_id)
SELECT backup_set_id
FROM backupset
WHERE [type] = 'D' AND DATEDIFF(DAY, backup_finish_date, GETDATE()) <= @DailyCopies
--разностные копии и журналы транзакций не старше @DailyCopies
INSERT INTO @saved_backup_set_id (backup_set_id)
SELECT backup_set_id
FROM backupset
WHERE database_backup_lsn IN
(
SELECT checkpoint_lsn
FROM backupset
WHERE backup_set_id IN (SELECT backup_set_id FROM @saved_backup_set_id)
) AND [type] <> 'D' --нужно чтобы дважды не выбрать запись о полной копии
--копии старше @DailyCopies и не старше @MonthCopies на 1-е числа месяцев
INSERT INTO @saved_backup_set_id (backup_set_id)
SELECT MIN(backup_set_id) AS backup_set_id
FROM backupset
WHERE [type] = 'D' AND DATEDIFF(DAY, backup_finish_date, GETDATE()) > @DailyCopies
AND DATEDIFF(DAY, backup_finish_date, GETDATE()) <= @MonthCopies
GROUP BY DATEPART(MONTH, backup_finish_date), DATEPART(YEAR, backup_finish_date), database_name
--копии старше @MonthCopies и не старше @QuarterCopies на первые числа кварталов
INSERT INTO @saved_backup_set_id (backup_set_id)
SELECT MIN(backup_set_id) AS backup_set_id
FROM backupset
WHERE [type] = 'D' AND DATEPART(MONTH, backup_finish_date) IN (1,4,7,10)
AND DATEDIFF(DAY, backup_finish_date, GETDATE()) > @MonthCopies
AND DATEDIFF(DAY, backup_finish_date, GETDATE()) <= @QuarterCopies
GROUP BY DATEPART(MONTH, backup_finish_date), DATEPART(YEAR, backup_finish_date), database_name
--Получаем список удаляемых резервных копий
INSERT INTO @backup_set_id (backup_set_id)
SELECT backup_set_id
FROM backupset bs
WHERE backup_set_id NOT IN (SELECT backup_set_id FROM @saved_backup_set_id)
--Получаем усписок удаляемых устройств
INSERT INTO @media_set_id (media_set_id)
SELECT DISTINCT media_set_id FROM msdb.dbo.backupset WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
--Получаем удаляемую историю восстановления
INSERT INTO @restore_history_id (restore_history_id)
SELECT DISTINCT restore_history_id FROM msdb.dbo.restorehistory WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
--Подсчитываем размеры копий
SELECT @BackupSize2save = SUM(backup_size), @FilesSize2Save = SUM(compressed_backup_size) FROM backupset WHERE backup_set_id IN (SELECT backup_set_id FROM @saved_backup_set_id)
SELECT @BackupSize2Del = SUM(backup_size), @FilesSize2Del = SUM(compressed_backup_size) FROM backupset WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
SELECT @TotalBackupSize = SUM(backup_size), @TotalFilesSize = SUM(compressed_backup_size) FROM backupset
PRINT 'Total ' + CAST(@TotalBackupSize As nvarchar(20)) + ' ('+ CAST(@TotalFilesSize As nvarchar(20)) + ' compressed)'
PRINT 'Save ' + CAST(@BackupSize2Save As nvarchar(20)) + ' ('+ CAST(@FilesSize2Save As nvarchar(20)) + ' compressed)'
PRINT 'Delete ' + CAST(@BackupSize2Del As nvarchar(20)) + ' ('+ CAST(@FilesSize2Del As nvarchar(20)) + ' compressed)'
/*
--Запрос для посмотреть, что будет сохранено
SELECT backup_set_id, name, [type], backup_finish_date FROM backupset WHERE backup_set_id IN
(SELECT backup_set_id FROM @saved_backup_set_id) AND database_name = '...'
*/
--Удаляем устройства
DECLARE #backups CURSOR FORWARD_ONLY
FOR
SELECT physical_device_name FROM backupmediafamily WHERE media_set_id IN (SELECT media_set_id FROM @media_set_id)
OPEN #backups
FETCH NEXT FROM #backups
INTO @physical_device_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @physical_device_name = N'del /F /Q ' + @physical_device_name
EXEC xp_cmdshell @physical_device_name, no_output
FETCH NEXT FROM #backups
INTO @physical_device_name
END
CLOSE #backups
DEALLOCATE #backups
--теперь чистим таблицы
BEGIN TRANSACTION
DELETE FROM backupfile WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
DELETE FROM backupfilegroup WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
DELETE FROM restorefile WHERE restore_history_id IN (SELECT restore_history_id FROM @restore_history_id)
DELETE FROM restorefilegroup WHERE restore_history_id IN (SELECT restore_history_id FROM @restore_history_id)
DELETE FROM restorehistory WHERE restore_history_id IN (SELECT restore_history_id FROM @restore_history_id)
DELETE FROM backupset WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
DELETE backupmediafamily FROM backupmediafamily bmf WHERE bmf.media_set_id IN (SELECT media_set_id FROM @media_set_id)
AND ((SELECT COUNT(*) FROM backupset WHERE media_set_id = bmf.media_set_id) = 0)
DELETE backupmediaset FROM backupmediaset bms WHERE bms.media_set_id IN (SELECT media_set_id FROM @media_set_id)
AND ((SELECT COUNT(*) FROM backupset WHERE media_set_id = bms.media_set_id) = 0)
COMMIT TRANSACTION
--Отключаем команды ОС
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
[more=Читать дальше..]
Код:
--*****************************************************************************
--Обход всех не нужных копий с удалением файлов и данных из истории копирования
--*****************************************************************************
--Разрешаем команды ОС
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
USE msdb
--Переменные определяющие горизонт хранения для полных копий
DECLARE
@DailyCopies int = 30, -- сколько дней хранить ежедневные полные копии
@MonthCopies int = 365, --сколько дней хранить копии на первые числа месяцев которые старше @DailyCopies
@QuarterCopies int = 365*5, --сколько дней хранить квартальные копии которые старше @MonthCopies
@physical_device_name nvarchar(260),
@BackupSize2Save numeric(20,0),
@BackupSize2Del numeric(20,0),
@FilesSize2Save numeric(20,0),
@FilesSize2Del numeric(20,0),
@TotalBackupSize numeric(20,0),
@TotalFilesSize numeric(20,0)
--Переменные-таблицы для хранения данных истории резервного копирования
DECLARE @saved_backup_set_id TABLE (backup_set_id INT)
DECLARE @backup_set_id TABLE (backup_set_id INT)
DECLARE @media_set_id TABLE (media_set_id INT)
DECLARE @restore_history_id TABLE (restore_history_id INT)
--Получаем список сохраняемых полных копий (для 30-ти дневных сохраняем разностные и журналы)
--копии не старше @DailyCopies
INSERT INTO @saved_backup_set_id (backup_set_id)
SELECT backup_set_id
FROM backupset
WHERE [type] = 'D' AND DATEDIFF(DAY, backup_finish_date, GETDATE()) <= @DailyCopies
--разностные копии и журналы транзакций не старше @DailyCopies
INSERT INTO @saved_backup_set_id (backup_set_id)
SELECT backup_set_id
FROM backupset
WHERE database_backup_lsn IN
(
SELECT checkpoint_lsn
FROM backupset
WHERE backup_set_id IN (SELECT backup_set_id FROM @saved_backup_set_id)
) AND [type] <> 'D' --нужно чтобы дважды не выбрать запись о полной копии
--копии старше @DailyCopies и не старше @MonthCopies на 1-е числа месяцев
INSERT INTO @saved_backup_set_id (backup_set_id)
SELECT MIN(backup_set_id) AS backup_set_id
FROM backupset
WHERE [type] = 'D' AND DATEDIFF(DAY, backup_finish_date, GETDATE()) > @DailyCopies
AND DATEDIFF(DAY, backup_finish_date, GETDATE()) <= @MonthCopies
GROUP BY DATEPART(MONTH, backup_finish_date), DATEPART(YEAR, backup_finish_date), database_name
--копии старше @MonthCopies и не старше @QuarterCopies на первые числа кварталов
INSERT INTO @saved_backup_set_id (backup_set_id)
SELECT MIN(backup_set_id) AS backup_set_id
FROM backupset
WHERE [type] = 'D' AND DATEPART(MONTH, backup_finish_date) IN (1,4,7,10)
AND DATEDIFF(DAY, backup_finish_date, GETDATE()) > @MonthCopies
AND DATEDIFF(DAY, backup_finish_date, GETDATE()) <= @QuarterCopies
GROUP BY DATEPART(MONTH, backup_finish_date), DATEPART(YEAR, backup_finish_date), database_name
--Получаем список удаляемых резервных копий
INSERT INTO @backup_set_id (backup_set_id)
SELECT backup_set_id
FROM backupset bs
WHERE backup_set_id NOT IN (SELECT backup_set_id FROM @saved_backup_set_id)
--Получаем усписок удаляемых устройств
INSERT INTO @media_set_id (media_set_id)
SELECT DISTINCT media_set_id FROM msdb.dbo.backupset WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
--Получаем удаляемую историю восстановления
INSERT INTO @restore_history_id (restore_history_id)
SELECT DISTINCT restore_history_id FROM msdb.dbo.restorehistory WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
--Подсчитываем размеры копий
SELECT @BackupSize2save = SUM(backup_size), @FilesSize2Save = SUM(compressed_backup_size) FROM backupset WHERE backup_set_id IN (SELECT backup_set_id FROM @saved_backup_set_id)
SELECT @BackupSize2Del = SUM(backup_size), @FilesSize2Del = SUM(compressed_backup_size) FROM backupset WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
SELECT @TotalBackupSize = SUM(backup_size), @TotalFilesSize = SUM(compressed_backup_size) FROM backupset
PRINT 'Total ' + CAST(@TotalBackupSize As nvarchar(20)) + ' ('+ CAST(@TotalFilesSize As nvarchar(20)) + ' compressed)'
PRINT 'Save ' + CAST(@BackupSize2Save As nvarchar(20)) + ' ('+ CAST(@FilesSize2Save As nvarchar(20)) + ' compressed)'
PRINT 'Delete ' + CAST(@BackupSize2Del As nvarchar(20)) + ' ('+ CAST(@FilesSize2Del As nvarchar(20)) + ' compressed)'
/*
--Запрос для посмотреть, что будет сохранено
SELECT backup_set_id, name, [type], backup_finish_date FROM backupset WHERE backup_set_id IN
(SELECT backup_set_id FROM @saved_backup_set_id) AND database_name = '...'
*/
--Удаляем устройства
DECLARE #backups CURSOR FORWARD_ONLY
FOR
SELECT physical_device_name FROM backupmediafamily WHERE media_set_id IN (SELECT media_set_id FROM @media_set_id)
OPEN #backups
FETCH NEXT FROM #backups
INTO @physical_device_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @physical_device_name = N'del /F /Q ' + @physical_device_name
EXEC xp_cmdshell @physical_device_name, no_output
FETCH NEXT FROM #backups
INTO @physical_device_name
END
CLOSE #backups
DEALLOCATE #backups
--теперь чистим таблицы
BEGIN TRANSACTION
DELETE FROM backupfile WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
DELETE FROM backupfilegroup WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
DELETE FROM restorefile WHERE restore_history_id IN (SELECT restore_history_id FROM @restore_history_id)
DELETE FROM restorefilegroup WHERE restore_history_id IN (SELECT restore_history_id FROM @restore_history_id)
DELETE FROM restorehistory WHERE restore_history_id IN (SELECT restore_history_id FROM @restore_history_id)
DELETE FROM backupset WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id)
DELETE backupmediafamily FROM backupmediafamily bmf WHERE bmf.media_set_id IN (SELECT media_set_id FROM @media_set_id)
AND ((SELECT COUNT(*) FROM backupset WHERE media_set_id = bmf.media_set_id) = 0)
DELETE backupmediaset FROM backupmediaset bms WHERE bms.media_set_id IN (SELECT media_set_id FROM @media_set_id)
AND ((SELECT COUNT(*) FROM backupset WHERE media_set_id = bms.media_set_id) = 0)
COMMIT TRANSACTION
--Отключаем команды ОС
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO