sqlschool.gr logo

articles

Articles of SQLschool.gr Team

Purge old Database Backup files

Antonios Chatzipavlis
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

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

What's New in SQL Server 2022 - Episodes

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn


sqlschool.gr © 2010-2025 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.