SQL Server - Differential backup with no full backup?

Asked By OlaHallengre

02-Jan-09 02:12 PM

When you perform a differential backup and a full backup doesn't exist you
get this error message.

current database backup does not exist. Perform a full database backup by
reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option."

This is good. However it seems like you don't get this error message under
all conditions.

I have seen a differential backup run successfully, with no current full
backup. Right now when I'm testing I can reproduce this behaviour on SQL
Server 2005 SP3. With SQL Server 2008 I'm getting the error message.

I would like to understand how this works. How can it be that you under some
conditions can perform a differential backup with no current full backup?

Ola Hallengren
http://ola.hallengren.com

02-Jan-09 02:26 PM

Hi Ola

What do you mean by 'current' full backup?

You have to have done a full at some point in the past, so the differential
knows what to use as the baseline. But there is no requirement that the
backup is available.

I just created a new database on SQL 2005, and then tried to make a
differential backup (with  no full) and got a similar error:

Msg 3035, Level 16, State 1, Line 1
Cannot perform a differential backup for database "testdiff", 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.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

02-Jan-09 02:52 PM

Now I'm doing this test on a SQL Server 2005 SP3.

CREATE DATABASE Test
GO

BACKUP DATABASE Test TO DISK = 'C:\Test.bak' WITH DIFFERENTIAL
GO

The backup runs successfully.

Processed 176 pages for database 'Test', file 'Test' on file 5.
Processed 1 pages for database 'Test', file 'Test_log' on file 5.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 177 pages in 0.161
seconds (8.961 MB/sec).

I think that it's very strange.

Ola Hallengren
http://ola.hallengren.com

02-Jan-09 03:08 PM

I also checked the differential_base_lsn for file_id = 1 in sys.master_files
right after I created the database (before I performed the differential
backup).

On SQL Server 2005 SP3 it is 20000000027200037. On SQL Server 2008 it is NULL.

On SQL Server 2005 SP3 the differential backup runs successfully. On SQL
Server 2008 it fails with the error message (as it should).

Ola Hallengren
http://ola.hallengren.com

02-Jan-09 03:18 PM

I did the same thing and verified it SQL2005sp3. My differential_base_lsn is
NULL.  I verified I was checking the files for the new database.

What edition are you using? What is your default recovery model?

This is very strange....

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

02-Jan-09 03:21 PM

Maybe you have a DDL trigger that automatically backs up the database as
soon as you create it?

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

02-Jan-09 03:40 PM

Build: 9.00.4035.00
Edition: Developer Edition

The default recovery model is Full. (I have checked both the model database
and the newly created test database.)

I have no DDL triggers.

I have also checked the error log. Here's how it looks like.

2009-01-02 21:34:54.31 spid53      Starting up database 'Test'.
2009-01-02 21:34:54.45 spid53      CHECKDB for database 'Test' finished
without errors on 2009-01-02 19:09:42.637 (local time). This is an
informational message only; no user action is required.
2009-01-02 21:34:55.18 Backup      Database differential changes were backed
up. Database: Test, creation date(time): 2009/01/02(21:34:54), pages dumped:
124, first LSN: 22:56:1, last LSN: 22:58:1, full backup LSN: 0:0:0, number of
dump devices: 1, device information: (FILE=25, TYPE=DISK: {'C:\Test.bak'}).
This is an informational message. No user action is required.

Ola Hallengren
http://ola.hallengren.com

02-Jan-09 03:56 PM

How did the CHECKDB get in there?  I thought you just created the db and
then immediately tried to do a differential backup...
The message seems to indicate there was a full backup. Maybe you should run
a trace.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

02-Jan-09 04:34 PM

I was thinking that this CHECKDB message is related to the database creation.
(It is on the exact same second as the database creation. It also looks
slighly different than a message in the error log when you run the DBCC
CHECKDB command.) Maybe I'm not right about this.

I have also verfied it in a trace and there are no other activities going on
(only a database creation followed by a differential backup).

About the error log. There are three enries.

2009-01-02 21:34:54.31 spid53      Starting up database 'Test'.
2009-01-02 21:34:54.45 spid53      CHECKDB for database 'Test' finished ...
2009-01-02 21:34:55.18 Backup      Database differential changes were backed
...

If there was a full backup then I think that there would have been an entry
for that too.

Ola Hallengren
http://ola.hallengren.com

02-Jan-09 04:42 PM

I don't get the CHECKDB message. My guess is there is something set on your
server that does some stuff immediately after creating a database, including
making a full backup as the errorlog seems to indicate. Have you checked the
msdb history tables?

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

02-Jan-09 06:34 PM

I think that I have found out about some things now.

I installed a new SQL Server 2005 SP3 instance and things were working as
expected. That is no CKECKDB informational message when I create the
database, differential_base_lsn = NULL and an error message when I perform
the differential backup.

Then I performed a full backup of the model database. This change the
behaviour.

Now the differential_base_lsn is the same for the model database and a new
database that I create. I can also perform a differential backup right after
I create a new database.

If I also perform a DBCC CHECKDB on the model database then that will be
shown in the error log as a Last-Known-Good CHECKDB for new databases that I
create.

Ola Hallengren
http://ola.hallengren.com

02-Jan-09 06:48 PM

Ok, this might explain it.
There was/is a bug that you might have hit, that has to do with what is
copied to the new db if you have made a backup of your model database.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

02-Jan-09 07:06 PM

I also think that it is a bug. Do you know if there is a kb article?

I did some more tests and it seems like it is fixed in SQL Server 2008.

As it is seems it is not fixed in SQL Server 2005 SP3.

Thank you for you help.

Ola Hallengren
http://ola.hallengren.com

02-Jan-09 07:14 PM

Not every bug has a corresponding KB article. I just heard from one of the
Microsoft engineers who work on the transaction log.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

05-Jan-09 12:06 PM

Kalen,

Is there or will there be a fix for this post SP3?

Thanks

Chris

06-Jan-09 07:15 PM

Probably not, unless someone requests a hotfix and can provide a good
business case as to why it is crucial to their business to have this fixed.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

21-Jan-09 01:32 PM

I'm using sys.master_files.differential_base_lsn in my backup scripts to
check if a differential backup of the database can be performed.
http://blog.ola.hallengren.com/DatabaseBackupCTP.sql

This works well. The problem is that you can perform a differential backup
in SQL Server 2005 in the discussed scenario even though you shouldn't (an
initial differential backup is as I understand it useless).

I thought about doing a check if the differential_base_lsn is the same for
the current database as for the model database. Then the
differential_base_lsn should be treated as if it was Null.

How does this sound? Could the differential_base_lsn for a user database and
the model database be the same in any other scenario?

Ola Hallengren
http://ola.hallengren.com

21-Oct-10 10:33 AM

Although the previous messages were some time ago I  thought I'd add my bit as I have just come across an interesting issue.



I wanted to differentiate the backup files from each other as both full backup and differentail backups have the extension .BAK. I therefore changed the extensions to: .BAK - Full Backup, .TRN - Transaction Log and .DIF - Differential Backup.



This I carried out within the Microsoft SQL Server  Management Studio when creating or editing a Maintenance Plan. The option is available to set the backup file extension.



Previously I received the error when trying to undertake a differentail backup without a full backup existing. However with the differential backup having a changed extension it happily takes the backup!