Smart Differential Backup

[kmckelve]

Currently the Smart Differential Backup option is based wholly on ModificationLevel. It would be useful to also check if the last non COPY_ONLY full backup was placed under the default backup directory. This would help spot non COPY_ONLY backups made by VSS and users on machines over which one does not have complete control. i.e. It would help ensure that differential backups related to known full backups on disk. Thanks, Ken

[leepfrog-ger]

Adding to this it could also make sense to include the LSN in the filename of the full backups, so that it can be verified that there is an unbroken chain.

[kmckelve]

Nice idea. In fact putting the LSN in the filename might make this easier to implement. ie Non-DBA users or VSS are unlikely to put the LSN in a full backup filename.

Putting the LSN in the full backup filename would have to be optional as other processing might depend on the current format.

[kmckelve]

For the moment, I am currently running the following before doing a daily differential backup. The code would need to be more involved if I ever did file, or filegroup, backups.

DECLARE @DefaultBackupDir nvarchar(4000) = dbo.fn_SQLServerBackupDir() + '%'
    ,@Databases nvarchar(4000);

WITH FullBackupLSNs
AS
(
    SELECT D.[database_id], S.[database_name], S.checkpoint_lsn
            ,ROW_NUMBER() OVER (PARTITION BY S.[database_name] ORDER BY S.backup_start_date DESC) AS rn
    FROM msdb.dbo.backupset S
            JOIN msdb.dbo.backupmediafamily M
                    ON S.media_set_id = M.media_set_id
            JOIN sys.databases D
                    ON S.[database_name] = D.[name]
    WHERE S.[type] = 'D'
        AND S.backup_finish_date IS NOT NULL
        AND S.[server_name] = @@SERVERNAME
        AND S.is_copy_only = 0
        AND D.[state] = 0 -- ONLINE
        AND M.physical_device_name LIKE @DefaultBackupDir
)
,CurrentBaseLSNs ([database_name], differential_base_lsn)
AS
(
    SELECT DB_NAME(database_id), MAX(differential_base_lsn)
    FROM sys.master_files
    WHERE DB_NAME(database_id) <> 'tempdb'
        AND [type_desc] = 'ROWS'
    group by DB_NAME(database_id)
)
SELECT C.[database_name]
INTO #t
FROM CurrentBaseLSNs C
    JOIN sys.databases D
            ON C.[database_name] = D.[name]
    LEFT JOIN FullBackupLSNs F
        ON C.[database_name] = f.[database_name]
            AND F.rn =1
WHERE D.[state] = 0 -- ONLINE
    AND
    (
        C.differential_base_lsn <> COALESCE(F.checkpoint_lsn, 0)
        OR
        C.differential_base_lsn IS NULL
    );

IF NOT EXISTS (SELECT 1 FROM #t)
BEGIN;
    PRINT 'No Full Backups Required.';
END;
ELSE
BEGIN;
    SELECT @Databases =
        STUFF
        (
            (SELECT ',' + [database_name] FROM #t FOR XML PATH(''))
            , 1, 1, ''
        );

    PRINT 'Full Backups being done for ' + @Databases;

    EXECUTE [dbo].[DatabaseBackup]
        @Databases = @Databases
        ,@Directory = NULL
        ,@BackupType = 'FULL'
        ,@Updateability = 'READ_WRITE'
        ,@CleanupTime = NULL
        ,@CheckSum = 'Y'
        ,@LogToTable = 'Y'
END;