How to make a differential backup?

Question

Database administration

I'm interested in differential backup.
I have three backup copies of the same database per day. morning afternoon and evening.
So from which base will the latest changes be removed?
Some three plans make one copy every day by themselves overwriting each other every day. and the fourth plan is a full copy once a month and a difference from it.
So it is possible means sql?

Answer 1

BACKUP DATABASE … TO … WITH INIT  
BACKUP DATABASE … TO … WITH DIFFERENTIAL

Guide .

-- включение xp_cmdshell, запустить ОДИН раз
EXECUTE sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXECUTE sp_configure 'xp_cmdshell', 1;  
GO  
RECONFIGURE;  
GO

-- создание месячного резерва
DECLARE @BF VARCHAR(256), @BN VARCHAR (128) 
DECLARE @DT VARCHAR(8) = CONVERT( VARCHAR, GETDATE(), 112)
SET @BF = 'MKDIR C:\Temp\' + SUBSTRING(@DT, 1, 4)
                           + '\' + SUBSTRING(@DT, 5, 2)
-- создание папки 2021\11
EXECUTE xp_cmdshell @BF 
SET @BF = 'C:\Temp\' + SUBSTRING(@DT, 1, 4)
                     + '\' + SUBSTRING(@DT, 5, 2)
                     + '\Testus.bak'
SET @BN = 'Testus' + CONVERT( VARCHAR, GETDATE(), 121)
BACKUP DATABASE Testus TO DISK = @BF WITH COPY_ONLY, NAME = @BN
-- сжатие
-- BACKUP DATABASE Testus TO DISK = @BF WITH COMPRESSION, COPY_ONLY, NAME = @BN

Check Compatibility: Backup Compression .
Make a full backup at night:

-- создание ежедневного полного резерва
DECLARE @BF VARCHAR(256), @BN VARCHAR (128)
SET @BF = 'forfiles /P C:\Temp /M Testus-* /C "cmd /c DEL @path" /D -5'
-- удаление резервов старше 5 дней
EXECUTE xp_cmdshell @BF 
SET @BF = 'C:\Temp\Testus-' + CONVERT( VARCHAR, GETDATE(), 23) + '.bak'
SET @BN = 'Testus-' + CONVERT( VARCHAR, GETDATE(), 121)
BACKUP DATABASE Testus TO DISK = @BF WITH INIT, NAME = @BN

Then during working hours - differential, for example, every hour:

-- создание разностного резерва
DECLARE @BF VARCHAR(256), @BN VARCHAR(128)
SET @BF = 'C:\Temp\Testus-' + CONVERT( VARCHAR, GETDATE(), 23) + '.bak'
SET @BN = 'Testus-' + CONVERT( VARCHAR, GETDATE(), 121)
BACKUP DATABASE Testus TO DISK = @BF WITH DIFFERENTIAL, NAME = @BN

The job can be executed by OS scheduler or SQL server ( manual ).
File processing (moving, deleting, etc.), also by means of the OS or SQL server ( xp_cmdshell ).

there are also manual backups

In order not to violate the order of automatic backups, you need to make a backup with a parameter COPY_ONLYin a special file.

-- создание ручного полного резерва
DECLARE @BF VARCHAR(256), @BN VARCHAR (128)
SET @BF = 'C:\Temp\Testus-cp-' + CONVERT( VARCHAR, GETDATE(), 23) + '.bak'
SET @BN = 'Testus-' + CONVERT( VARCHAR, GETDATE(), 121)
BACKUP DATABASE Testus TO DISK = @BF WITH COPY_ONLY, NAME = @BN

everything, less than full

It all depends on the application. On the server side, you can only shrink the database ( manual ) and defragment the indexes ( manual ). This will increase the density and possibly reduce the number of modified pages.
Recovery:
Select a day (file name) and restore to the desired state.

day and last

you can choose not the last