# Sql-server – A database differential backup without a full backup

backupsql-server-2016

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?

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,
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.