Can I create differential backups tied to a specifc Full backup instead of the most recent?

1

We have a great backup plan. Full backups on Saturday at 3AM, Differential backups at 3:30 AM daily, and Transaction logs every 15 minutes. I test the backups monthly by restoring them to our Dev database. Yesterday I found that the Differential backups are no longer tied to the Saturday Full backup. It seems that our admin is doing a full backup via the Hypervisor every morning at 5 AM. Since Differential backups are tied to the last Full backup, the differential backups are no longer viable because the chain has been broken with the Saturday morning Full backup.
My question is, is there a way to make differential backups that are tied to a specific Full backup? Maybe specify the DatabaseBackupLSN some how?

2

Your admins need to do their backups with the COPY_ONLY flag for SQL Server set to True as this will not reset the differential LSN.

If they cannot, or will not, do that then you can disable the SQL Server VSS Writer. This will stop the admins' backup program using VSS so it will not interfere with your backups. The downside of this is if the VM ever needs to be restored the DBs are likely to be in an inconsistent state so you would need to restore your backups as well.

2 Likes
3

Thank You, That is what I was looking for !

(P.S. Admin washes his hands of it, says it our problem. Can you believe being in a position to not restore and the admin doesn't care...)

4

Can you believe being in a position to not restore and the admin doesn't care...

Yes! You should escalate this with the person you report to as disabling the VSS Writer service is not the ideal solution.

1 Like
5

For what it is worth, this is my, somewhat old, SP that does a FULL backup if the differential LSNs do not tie up.

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER FUNCTION [dbo].[fn_SQLServerBackupDir]()
RETURNS NVARCHAR(4000)
AS
BEGIN

   DECLARE @path NVARCHAR(4000)

   EXEC master.dbo.xp_instance_regread
            N'HKEY_LOCAL_MACHINE',
            N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
            @path OUTPUT, 
            'no_output'
   RETURN @path

END;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE dbo.Diff2FullBackup
AS
SET NOCOUNT, XACT_ABORT ON;

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

IF LEFT(REVERSE(@DefaultBackupDir) , 1) = '\'
	SET @DefaultBackupDir = LEFT(@DefaultBackupDir, LEN(@DefaultBackupDir) -1);
SET @DefaultBackupDir = @DefaultBackupDir + '\' + @@SERVERNAME + '%';

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
	)
	AND C.[database_name] NOT LIKE '\__%' ESCAPE '\';

--IF (SELECT COUNT(*) FROM #t) = 0
IF NOT EXISTS (SELECT 1 FROM #t)
BEGIN;
	PRINT 'No Full Backups Required.';
	RETURN;
END;

SELECT @Databases =
	STUFF
	(
		(SELECT ',' + [database_name] FROM #t FOR XML PATH(''))
		, 1, 1, ''
	);

PRINT 'Full Backups being done for ' + @Databases

-- Ola Hallengren FULL backup
EXECUTE [dbo].[DatabaseBackup]
	@Databases = @Databases
	,@BackupType = 'FULL'
	,@Updateability = 'READ_WRITE'
	,@CheckSum = 'Y'
	,@LogToTable = 'Y'
	,@Encrypt = 'Y'
	,@EncryptionAlgorithm = 'AES_256'
	,@ServerCertificate = 'TheBackupCertificate';
1 Like
6

Thank You !

Also, looks like it only detects if the backup chain is broke and then performs a full backup. Is there anyway of creating the Diff backups to a different LSN number?

7

There is not - differential backups can only be performed against the latest full backup.

2 Likes
8

Thank You for confirming. Got a meeting set first thing Monday morning to fix this.

1 Like
9

And where are you storing your backups? Hopefully, not on the same machine(s).

And are you storing a copy of the backups offsite? If not, you are absolutely toast if you get hit with a ransom-ware attack.

The BEST thing to do FOR THE COMPANY is for you and the admins to get together and get it right. :wink: