Purge old Database Backup files
Saturday 27 November 2021
Overview
Όσοι κάνουν σωστά την δουλειά τους έχουν και την σωστή backup στρατηγική. Αυτό σημαίνει ότι καθημερινά δημιουργούν ένα αριθμό από backup files και φυσικά μειώνεται και ο ελεύθερος χώρος του δίσκου.
Αυτό σημαίνει ότι πρέπει να δημιουργηθεί μια διαδικασία που να σβήνει τα παλιά και μη "χρήσιμα" backups (αν και καλό θα είναι και αυτά να τα κρατάμε σε εξωτερικού αποθηκευτικούς χώρους).
Έχω δει αρκετούς τρόπους να γίνεται αυτό αλλά θεωρώ ότι υπάρχει ευκολότερος και καλύτερος τρόπος με την χρήση κάποιων stored procedures (δείτε το SQL Server in Greek – Episode #26 - File system accessing from a SQL Server 2019 instance).
My Solution
Αυτό που έχω δημιουργήσει είναι μια stored procedure η οποία έχει μια παράμετρο στην οποία ορίζουμε το πόσα full backups θέλουμε να κρατήσουμε σε κάθε database.
Αυτό που κάνω σε αυτή είναι να βρω ΤΟΡ(Ν) για κάθε database και να σβήνω από εκεί και πίσω.
SQL Script
USE msdb;
GO
CREATE OR ALTER PROC dbo.sp_PurgeDatabaseBackupFiles
@keepOffset int = 5 -- how many full backup (and the depended other backups) you want to keep
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('#dboffsets') IS NOT NULL
DROP TABLE #dboffsets;
WITH setoffset
AS
(
SELECT
row_number() OVER (PARTITION BY bs.database_name ORDER BY bs.backup_finish_date DESC) AS RN
, bs.[database_name] AS [database_name]
, bs.[type] AS backup_type
, bs.[backup_finish_date] AS backup_finish_date
, mf.[physical_device_name] AS physical_device_name
, bs.[position] AS position_in_device
FROM
msdb.dbo.backupset AS bs
INNER JOIN
msdb.dbo.backupmediafamily AS mf ON bs.[media_set_id] = mf.[media_set_id]
WHERE
bs.[type] = 'D'
AND
mf.mirror = 0
)
SELECT DISTINCT
[database_name]
, backup_finish_date
, physical_device_name
INTO
#dboffsets
FROM
setoffset
WHERE
RN = @keepOffset;
-- DELETE FILES FROM DISK
DECLARE delete_cursor CURSOR READ_ONLY
FOR
SELECT DISTINCT
mf.[physical_device_name] AS physical_device_name
FROM
msdb.dbo.backupset AS bs
INNER JOIN
msdb.dbo.backupmediafamily AS mf ON bs.[media_set_id] = mf.[media_set_id]
INNER JOIN
#dboffsets AS do ON do.database_name = bs.database_name
WHERE
bs.backup_finish_date < do.backup_finish_date
AND
mf.physical_device_name != do.physical_device_name
AND
mf.physical_device_name != 'NUL'
ORDER BY
physical_device_name;
DECLARE @filename nvarchar(4000);
OPEN delete_cursor;
FETCH NEXT FROM delete_cursor INTO @filename;
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
EXEC sys.xp_delete_files @filename;
--print @filename
END
FETCH NEXT FROM delete_cursor INTO @filename;
END
CLOSE delete_cursor;
DEALLOCATE delete_cursor;
END
GO