go backarticles

Articles of SQLschool.gr Team

Purge old Database Backup files

Antonios Chatzipavlis

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 is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.



Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in

Newsletters   About us   Contact us   Terms of Use   Privacy   Register
sql school greece logo
© 2010-2022 All rights reserved

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