Why my Differential Backup was Failing?

In one of the online SQL forum, a forum user reported that, one of their server is scheduled for a weekly full database backup & daily differential database backup. However, during mid of this week, the daily differential job failed with the below error message:

Msg 3035, Level 16, State 1, Line 1
Cannot perform a differential backup for database “testdb2″, 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 1
BACKUP DATABASE is terminating abnormally.

All we have to do is, to read the error message clearly & correctly. As, in case of the above message, it is self-explanatory. The differential backup of the database “testdb2” failed, because, the full database backup of the database “testdb2” is not available. A differential backup is based on the last full database backup. For more information about differential backup, read the following post from Books Online. In this particular case, the full database backup of the newly created database was not available. We should initiate a full database backup once we create a new database.

Now, incase you want to interested, here is a small script to re-generate the above error.

use [master];
go
-- drop the database if exists
if exists (select 1 from sys.databases where [name] = 'testdb')
	drop database [TestDB];
go
-- create a new database
create database [testdb];
go
-- changing the auto close property to off
use [master];
go
alter database [testdb]
	set auto_close off;
go
--initiate a differential backup
use [master];
go
backup database [testdb]
	to disk = 'c:\sqlserver\userdb\sql2012\backup\testdb.diff'
	with differential;
go
-- error: Msg 3035, Level 16, State 1, Line 1
-- Cannot perform a differential backup for database "testdb", because a current database backup does not exist.

Update: For additional reasons for Differential backup failure, refer the following two KB articles, here and here. Thanks to Balmukund (b|t) Sir for the feedback

Hope, this may help someone, Happy Learning

About these ads