1

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?

  • Have any previous Full backups been done before you set this new schedule? – Henrico Bekker Jun 15 '18 at 6:00
  • no, this was just a test schedule to see how it goes. – Nico M. Jun 15 '18 at 6:07
  • So absolutely no previous backups have been made of this database? Check the properties of the database to see last Full backup date, and if any exist. – Henrico Bekker Jun 15 '18 at 6:08
  • it seems that another job has taken a full backup at 20:45, but how is that one related to this job? Even the backup set names are not the same. – Nico M. Jun 15 '18 at 6:16
  • 2
    It doesn't have to be related. 10 DBAs can make Differential backups within 5 minutes, unrelated. You need to see how the other jobs are working, since you could end up with transaction log restore issues, it seems multiple backups are running in your environment and will affect your chain. – Henrico Bekker Jun 15 '18 at 6:24
4

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.

  • Great script. Now I see there is more than one full backup of the database in various physical device and media sets. Still, when I want to restore the database all I see, is the differential and all transaction logs. How would I know which full back up is bound to these Diff and Tlogs? It doesn't make sense. – Nico M. Jun 15 '18 at 6:52
  • What are the physical devices? Are they in the form {vvvvvvvvv-www-xxxx-yyyyyyyyyyyy}z or any other special format? (Un-comment the columns in the script and re-run). The backup GUI is almost always limited to a time-frame which is displayed in the Restore to: option. Try going further back in time and see if the GUI displays more backups. Otherwise consider asking a new follow up question, so that the community can supply a possible answer. It's hard work trying to answer additional questions in comments. :-) – John aka hot2use Jun 15 '18 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.