A database differential backup without a full backup

Question

At 10 pm, I scheduled a SQL Agent job to do a daily backups with the schedule:

• Daily Full: 22:00
• Differential: Every 4 hours
• Log: Every 30 minutes

Now when I check my backup set(let's say at 22:30), I see that a differential backup has happened without any full backup!!.

I was not expecting to see the backup job up and running before 16:00 tomorrow when the first full backup will take place, however, I have a differential backup without a full backup? How is that possible?

Answer 1

It is not possible to perform a differential backup of a database if no previous backup was performed. A DIFF (differential) backup relies on the previous FULL backup.

A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the base of the differential. Full backups, except for copy-only backups, can serve as the base for a series of differential backups, including database backups, partial backups, and file backups. The base backup for a file differential backup can be contained within a full backup, a file backup, or a partial backup.

Reference: Differential Backups (SQL Server) (Microsoft Docs)

Proving the Thesis

You can check this with the following steps:

Create a New Database

USE [master]
GO
CREATE DATABASE [Q209724_Database]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Q209724_Database', FILENAME = N'C:\SQL\SQL_DATA\Q209724_Database.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Q209724_Database_log', FILENAME = N'C:\SQL\SQL_LOGS\Q209724_Database_log.ldf' , SIZE = 3072KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [Q209724_Database] SET COMPATIBILITY_LEVEL = 130
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Q209724_Database].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

The database is created and has not yet been backed up.

Perform a DIFF Backup of The New Database

BACKUP DATABASE [Q209724_Database] 
TO  DISK = 'C:\SQL\BACKUP\Q209724_Database.bak' 
WITH DIFFERENTIAL, 
     NOFORMAT, 
     NOINIT, 
     NAME = N'Q209724_Database-Full Database Backup', 
     SKIP, 
     NOREWIND, 
     NOUNLOAD, 
     STATS = 10
GO

Output

Msg 3035, Level 16, State 1, Line 91
Cannot perform a differential backup for database "Q209724_Database", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
Msg 3013, Level 16, State 1, Line 91
BACKUP DATABASE is terminating abnormally.

So if you haven't previously performed a FULL backup, you will be unable to perform a DIFF backup.

Conclusion

After your database was created somebody or a backup solution has performed a backup of your database.

Checking Backup History

You can change the database name in the following script to the name of your database and check the backup history for a backup of the database:

SELECT  
   -- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   -- msdb.dbo.backupset.expiration_date, 

   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Full'  
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],  
   -- msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   -- msdb.dbo.backupset.name AS backupset_name, 
   -- msdb.dbo.backupset.description,
   -- msdb.dbo.backupset.is_copy_only,
   -- msdb.dbo.backupset.is_snapshot,   
   msdb.dbo.backupset.first_lsn,
   msdb.dbo.backupset.last_lsn,
   msdb.dbo.backupset.database_backup_lsn,
   msdb.dbo.backupset.checkpoint_lsn,
   msdb.dbo.backupset.differential_base_lsn,
   msdb.dbo.backupset.fork_point_lsn,
   'EOR'

FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  

WHERE 1 = 1

AND     database_name IN ('Q209724_Database')  -- database names
ORDER BY
           2 desc,3 desc

You should see an entry containing a Full remark for the backup_type column in the result set.

You can then analyse when and possibly who/what performed the FULL database backup.

Answer 2

Did you restore the database from a full backup? I had similar experience that a database restored from a full backup doesn't require to take a dull backup prior to a differential backup is taken from it. Usually, the full backup that you used to restore the database can serve as a base backup for the differential backups.

Answer 3

Differential backups cannot be performed without a full backup. As John K. N. correctly noted, the backup has been performed by someone else.

You can view the backup history in the following way:

SELECT
    BS.database_name,
    BS.backup_start_date,
    BS.backup_finish_date,
    CASE BS.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Diff'
        WHEN 'L' THEN 'Log'
    END AS backup_type,
    BMF.device_type,
    BMF.physical_device_name
FROM
    msdb.dbo.backupmediafamily AS BMF WITH (NOLOCK)
    INNER JOIN msdb.dbo.backupset AS BS WITH (NOLOCK)
        ON BMF.media_set_id = BS.media_set_id
ORDER BY
    BS.database_name DESC,
    BS.backup_start_date DESC;

For example, here is the history of backups on my server. Note the records from device_type=7. These backups were made by my cloud service provider.

Here's a small comment from Microsoft about this.