Database Administrators Asked by Nico M. on January 14, 2021
At 10 pm, I scheduled a SQL Agent job to do a daily backups with the schedule:
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?
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.
Answered by Daniel Workneh on January 14, 2021
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)
You can check this with the following steps:
USE [master]
GO
CREATE DATABASE [Q209724_Database]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Q209724_Database', FILENAME = N'C:SQLSQL_DATAQ209724_Database.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Q209724_Database_log', FILENAME = N'C:SQLSQL_LOGSQ209724_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.
BACKUP DATABASE [Q209724_Database]
TO DISK = 'C:SQLBACKUPQ209724_Database.bak'
WITH DIFFERENTIAL,
NOFORMAT,
NOINIT,
NAME = N'Q209724_Database-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
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.
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.
Answered by John K. N. on January 14, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP