Differential backup behaviour between SQL versions

The other day I ran into a rather unique problem with a database when attempting to maintain it’s transaction log. As it turned out my particular issue was related to old Replication code -which I hope to post about at a later date, but essentially I needed to clear down some of the logfile’s VLFs which I was struggling to perform. Anything I attempted in order to maintain it failed (much was related to the bug I came across), however if I performed Transactional log backups, they would fail due to the fact that there was no Full database backup in existence. Since the database was huge, and a backup would have taken 12 hours to perform, it raised quite an interesting question namely “was there a way to force a Transactional log backup”.

My good friend Allen Dunn came up with the idea of taking a Differential backup instead, but I had already tried this. However Alan maintained that it did work and upon questioning it transpired that he was doing so on a SQL 2005 server. I was particularly interested since I was convinced that I had observed this exact behaviour on that platform as well several years back so I decided to run some tests. Below you will see the T-SQL code that I scrambled together to test, but essentially the test would first attempt a Differential backup on a new database (that obviously would not have a Full backup) and then followed the test up with a Transactional log backup on a new database. I was surprised to find that the behaviour differs between SQL 2005 and 2008.

SET NOCOUNT ON

SELECT @@VERSION

IF db_id('PuzzledDB') IS NOT NULL

	DROP DATABASE PuzzledDB

CREATE DATABASE PuzzledDB

--try performing differential backup with no full in place

BACKUP DATABASE PuzzledDB TO DISK = 'C:\PuzzledDB.diff' WITH DIFFERENTIAL

IF db_id('PuzzledDB') IS NOT NULL

	DROP DATABASE PuzzledDB

CREATE DATABASE PuzzledDB

--try performing transaction log backup with no full in place

BACKUP LOG PuzzledDB TO DISK = 'C:\PuzzledDB.log'

As you will see below the screen shot of the results from the SQL 2008 server did confirm exactly what I had experienced that not only the Differential backups require a full backup to have been taken, but also the Transactional log backups do too. I think this behaviour is understandable for normal operations on the basis that either backup without a corresponding Full would essentially be useless for recovery purposes.

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.2  (Build 3790: Service Pack 2)

Msg 3035, Level 16, State 1, Line 9

Cannot perform a differential backup for database "PuzzledDB", 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 9

BACKUP DATABASE is terminating abnormally.

Msg 4214, Level 16, State 1, Line 16

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 16

BACKUP LOG is terminating abnormally.

Now here comes the surprise… As Alan quite rightly said a Differential backup works in SQL 2005! Interestingly Transactional log backups still fail with the same error.

<pre>Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Processed 184 pages for database 'PuzzledDB', file 'PuzzledDB' on file 3.

Processed 1 pages for database 'PuzzledDB', file 'PuzzledDB_log' on file 3.

BACKUP DATABASE WITH DIFFERENTIAL successfully processed 185 pages in 0.342 seconds (4.410 MB/sec).

Msg 4214, Level 16, State 1, Line 16

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 16

BACKUP LOG is terminating abnormally.

I’m not entirely sure why the ability to take Differentials on databases that haven’t had a Full backup taken first (SQL 2008) has been removed or whether it being allowed in SQL 2005 was an oversight for that release. Personally I do not like this kind of “Nanny” style computing in restricting our actions because I believe that by forcing us down one resolution route hinders our ability to make an informed judgement to take another course of action. Going back to my initial problem that I touched upon at the beginning, I believe that had the transaction log backups been allowed to run they probably still wouldn’t have resolved my problem with the VLF’s, but what this would have done is shown me very early on that something else was fundamentally wrong in the database configuration and therefore arrived at a solution faster than I did.

Disclaimer: For important databases you should always try to guarantee that you are able to meet your RPO, this means always having a Full backup and any relevant Differential with respective Transaction log backups (depending upon your SLA and RPO). What this means to YOU as a DBA is to AVOID performing any operation that will break this chain!

Advertisements
This entry was posted in SQL, SQLServerPedia Syndication and tagged . Bookmark the permalink.

4 Responses to Differential backup behaviour between SQL versions

  1. Gethyn Ellis says:

    Nice Post mark, it seems the diff backup without a full backup in 2005 also kicks off the log backup chain and takes the database out of psuedo simple mode (SQL expects TLOG backups) After I took the diff I was able to take a TLOG backup with no Full backup in 2005

    I’m guessing that this maybe a bug in 2005. A diff backup without a full backup is useless isn’t it? You would need the base full backup to restore it.

    • retracement says:

      Hi Gethyn, thanks for your comments and further insight. Yes I agree strictly speaking without the full backup, the diff and tran backups are essentially useless but from my point of view if (for instance) you decided to move a SQL database from simple to full mode and due to whatever restriction (such as performance or business process) cannot take a full backup till later that evening AND are not worried about the risk of potential disk failure and data loss and inability to recover changes between that time frame, then for me its not really acceptable that SQL will fail your differential and transaction log backups up to that point until the full backup finally kicks in. Obviously in that scenario you would also have potential for transaction log growth up to that point. The only way around that (as I can see it) is to make sure you schedule (or perform live) the change from SIMPLE to FULL just prior to the FULL backup kicking in.

      I don’t think its really reasonable for SQL to enforce this behaviour on me even though it would normally be best practice, but not necessarily right for my particular situation – perhaps I will be unavailable that evening and want to make sure the database is guaranteed to be in the right mode at the start of the scheduled FULL backup. Obviously a transition via scheduled job has potential to fail.

  2. Gethyn Ellis says:

    I guess the point I was trying to make relates to the fact that if you have a database without a full backup, regardless of the recovery model specified, SQL Server will treat the database as if its in Simple Recovery mode, so it does not expect log backups and manages the log for you, In truth though I’m not sure how replication will be impact on this in particluar around log growth and manageability.

    Re the changing to Full, If you are concerned about the TLOG backups failing until the full backup is taken(you could script so the TLOG backup job to only takes TLOG backups of databases in true Full recovery mode), you could leave the database in SIMPLE take a full backup on your schedule, switch the database to Full, take a diff backup and start your T-log backups I reckon that could work too.

  3. Pingback: Tweets that mention Differential backup behaviour between SQL versions | -- Topsy.com

Comments are closed.