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
CREATE DATABASE [Q209724_Database]
CONTAINMENT = NONE
( NAME = N'Q209724_Database', FILENAME = N'C:\SQL\SQL_DATA\Q209724_Database.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
( NAME = N'Q209724_Database_log', FILENAME = N'C:\SQL\SQL_LOGS\Q209724_Database_log.ldf' , SIZE = 3072KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE [Q209724_Database] SET COMPATIBILITY_LEVEL = 130
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
EXEC [Q209724_Database].[dbo].[sp_fulltext_database] @action = 'enable'
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'
NAME = N'Q209724_Database-Full Database Backup',
STATS = 10
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.
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:
-- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME,
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.backupset.name AS backupset_name,
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
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.