Useful things about data recovery that every DBA should know. Part 3 — Three differential backups tricks

In this article we are going to answers those questions:

1. Is differential backup a cumulative, or an incremental one?

2. Why you really should take a full backup just before switching any file group to read-only?

3. Do you really need a full backup to restart a broken log backup chain?

If you missed previous parts of this series, you would find them there:

· Useful things about data recovery that every DBA should know. Part 1 — To what point in time full backup is being restored?

· Useful things about data recovery that every DBA should know. Part 2 — What does a full backup contain?

In SQL Server differential backups are cumulative

The first question is by far the easiest to answer — all you have to do is:

1. Create a database with three file groups. In this case, there is only one file in each filegroup and all three filgroups are put on the same hard drive. However, following Microsoft recommendations, the primary filegroup is not a default one (this is really important if you are using an Enterprise Edition):

CREATE DATABASE [Test]
 ON  PRIMARY 
( NAME = N'TestPrimary', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATATestPrimary.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [SECONDARY] 
( NAME = N'TestSecondary', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATATestSecondary.ndf'' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [TERTIARY] 
( NAME = N'TestTertiary', FILENAME = N''C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATATestTertiary.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Test_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATATest_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

USE [Test]
GO
ALTER DATABASE [Test] MODIFY FILEGROUP [SECONDARY] DEFAULT
GO

2. Create a table in each non-primary filegroup and take a full database backup (the initial backup that will switch this database into a truly full recovery mode):

CREATE TABLE Tab1 
(Col1 int IDENTITY PRIMARY KEY,
Col2 char(1000) default 'a')
ON SECONDARY;

CREATE TABLE Tab2 
(Col1 int IDENTITY PRIMARY KEY,
Col2 char(1000) default 'a')
ON TERTIARY;

BACKUP DATABASE [Test]
TO DISK ='TestFull.bak'
WITH INIT;  
GO

3. Insert some rows into a first table (the table that is stored in the second filegroup) and take a differential backup:

INSERT INTO Tab1
DEFAULT VALUES;
GO 50

BACKUP DATABASE [Test]
TO DISK ='TestDiff.bak'
WITH DIFFERENTIAL, INIT;  
GO
------
Processed 72 pages for database ''Test'', file ''TestPrimary'' on file 1.
Processed 32 pages for database ''Test'', file ''TestSecondary'' on file 1.
Processed 8 pages for database ''Test'', file ''TestTertiary'' on file 1.
Processed 2 pages for database ''Test'', file ''Test_log'' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 114 pages in 0.009 seconds (98.198 MB/sec).

As you can see, this backup contains 72 pages from the primary filegroup, 32 pages from the secondary and 8 pages from the last filegroup.

4. Now let me insert the same number of rows into the second table (the one stored in the third filegroup), and take another differential backup:

INSERT INTO Tab2
DEFAULT VALUES;
GO 50

BACKUP DATABASE [Test]
TO DISK ='TestDiff.bak'
WITH DIFFERENTIAL;  
GO
------
Processed 72 pages for database ''Test'', file ''TestPrimary'' on file 2.
Processed 32 pages for database ''Test'', file ''TestSecondary'' on file 2.
Processed 32 pages for database ''Test'', file ''TestTertiary'' on file 2.
Processed 1 pages for database ''Test'', file ''Test_log'' on file 2.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 137 pages in 0.016 seconds (66.528 MB/sec).

This backup contains all pages modified since the last full backup was taken, not the previous differential. In this case, all pages allocated to both tables were backed up.

Note. The sixth page in each data file is the first DIFF map page — a page used to track extents modified since the last full backup was taken. To see those pages, execute the following commands (the second file is always a log file, which is why I skipped the fileId 2):

DBCC TRACEON (3604);
GO
DBCC PAGE ('Test',1,6,3);
DBCC PAGE ('Test',3,6,3);
DBCC PAGE ('Test',4,6,3);
GO

In my case, the DIFF bitmaps look like this:

For the first file:

DIFF_MAP: Extent Alloc Status @0x000000001B28A0C2

(1:0) – (1:16) = CHANGED

(1:24) – (1:40) = NOT CHANGED

(1:48) – (1:64) = CHANGED

(1:72) – = NOT CHANGED

(1:80) – = CHANGED

(1:88) – (1:128) = NOT CHANGED

(1:136) – = CHANGED

(1:144) – = NOT CHANGED

(1:152) – = CHANGED

(1:160) – (1:632) = NOT CHANGED

For the second one:

DIFF_MAP: Extent Alloc Status @0x000000001B28A0C2

(3:0) – (3:24) = CHANGED

(3:32) – (3:632) = NOT CHANGED

And for the last one:

DIFF_MAP: Extent Alloc Status @0x000000001B28A0C2

(4:0) – (4:24) = CHANGED

(4:32) – (4:632) = NOT CHANGED

Those bitmaps are reset to NOT CHANGED values when a full backup is taken. Therefore, if I take another differential backup, it will contain exactly the same pages as the previous one:

BACKUP DATABASE [Test]
TO DISK ='TestDiff.bak'
WITH DIFFERENTIAL; 
GO
------
Processed 72 pages for database ''Test'', file ''TestPrimary'' on file 4.
Processed 32 pages for database ''Test'', file ''TestSecondary'' on file 4.
Processed 32 pages for database ''Test'', file ''TestTertiary'' on file 4.
Processed 1 pages for database ''Test'', file ''Test_log'' on file 4.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 137 pages in 0.010 seconds (106.396 MB/sec).

Though, after the full backup was taken, the differential one will contains only the first extent of each file (the one where file metadata is stored):

BACKUP DATABASE [Test]
TO DISK ='TestFull.bak';
GO

BACKUP DATABASE [Test]
TO DISK ='TestDiff.bak'
WITH DIFFERENTIAL; 
GO
------
Processed 40 pages for database ''Test'', file ''TestPrimary'' on file 5.
Processed 8 pages for database ''Test'', file ''TestSecondary'' on file 5.
Processed 8 pages for database ''Test'', file ''TestTertiary'' on file 5.
Processed 1 pages for database ''Test'', file ''Test_log'' on file 5.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 57 pages in 0.014 seconds (31.808 MB/sec).

Lesson learned — In SQL Server a differential backup is cumulative. What this means is that the consecutive differential backup will be larger (or at least, the same size) as the previous one, and when all database extents are modified, the differential backup will be as large as the full one.

Note. If a transaction is rolled back, two changes happened to the corresponding page — first, the original statement (i.e. INSERT) is executed, and then the SQL Server has to compensate this change (i.e. executes DELETE statement). As a result, the extent that contains this page will be added to the next differential backup.

Take a full backup before switching a filgroup to read-only

The reason to do this should be clear by now, but let me show you what I mean. First, I insert another 50 rows into the second table and then switch the third filegroup (the second table is stored in this filegroup) into read-only mode:

INSERT INTO Tab2
DEFAULT VALUES;
GO 50

ALTER DATABASE [Test] 
MODIFY FILEGROUP [TERTIARY] READONLY;
GO

Then the differential backup is taken:

BACKUP DATABASE [Test]
TO DISK ='TestDiff.bak'
WITH DIFFERENTIAL; 
GO
-----
Processed 80 pages for database ''Test'', file ''TestPrimary'' on file 6.
Processed 8 pages for database ''Test'', file ''TestSecondary'' on file 6.
Processed 24 pages for database ''Test'', file ''TestTertiary'' on file 6.
Processed 1 pages for database ''Test'', file ''Test_log'' on file 6.

The newly used pages from the third filegroup were added to this backup, as expected. But even if the full backup is taken, the differential backup will still contains the same pages:

BACKUP DATABASE [Test]
TO DISK ='TestFull.bak';
GO

BACKUP DATABASE [Test]
TO DISK ='TestDiff.bak'
WITH DIFFERENTIAL; 
GO
-------
Processed 40 pages for database ''Test'', file ''TestPrimary'' on file 7.
Processed 8 pages for database ''Test'', file ''TestSecondary'' on file 7.
Processed 24 pages for database ''Test'', file ''TestTertiary'' on file 7.
Processed 1 pages for database ''Test'', file ''Test_log'' on file 7.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 73 pages in 0.008 seconds (71.289 MB/sec).

Yes, you guessed correctly — the file in which the DIFF bitmap is stored was set to read-only, so SQL Server would not be able to reset it during a full backup. And the only solution is to switch this filegroup to the read-write, take a full backup, and switch it back to read-only mode:

ALTER DATABASE [Test] 
MODIFY FILEGROUP [TERTIARY] READWRITE;
GO

BACKUP DATABASE [Test]
TO DISK ='TestFull.bak';
GO

ALTER DATABASE [Test] 
MODIFY FILEGROUP [TERTIARY] READONLY;
GO

From now on, the differential database backups will not contain pages that had changed before a full backup was taken:

BACKUP DATABASE [Test]
TO DISK ='TestDiff.bak'
WITH DIFFERENTIAL, INIT; 
GO
-----
Processed 40 pages for database ''Test'', file ''TestPrimary'' on file 1.
Processed 8 pages for database ''Test'', file ''TestSecondary'' on file 1.
Processed 8 pages for database ''Test'', file ''TestTertiary'' on file 1.
Processed 1 pages for database ''Test'', file ''Test_log'' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 81 pages in 0.027 seconds (23.202 MB/sec).

Lesson learned — As a general rule, you should take a full database backup just before any part of this database will be switched into read-only mode.

Broken log chain backup can be restored using a differential backup

A log chain starts with a full backup of the database. In other word, a restore sequence always must be started with a full backup. This kind of log backup chain will be broken if:

· A database is switched from the FULL or BULK_LOGGED recovery models into the SIMPLE recovery model

· A database is reverted from a snapshot.

· A transaction log backup is taken with the WITH NO_LOG or WITH TRUNCATE_ONLY option (In SQL Server 2005 and previous version only).

But this does not mean that an already established log chain backup has to be restored with a full database backup as well. To see this, switch the Test database to SIMPLE and back to FULL recovery model:

ALTER DATABASE [Test]
SET RECOVERY SIMPLE;
GO

ALTER DATABASE [Test]
SET RECOVERY FULL;
GO

And now try to take a log backup:

BACKUP LOG [Test]
TO DISK ='TestLog.bak'
WITH INIT; 
GO
------
Msg 4214, Level 16, State 1, Line 7
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 7
BACKUP LOG is terminating abnormally.

However, after a differential backup was taken, the log chain backup is restored, and a log backup can be executed:

BACKUP DATABASE [Test]
TO DISK ='TestDiff.bak'
WITH DIFFERENTIAL, INIT; 
GO

BACKUP LOG [Test]
TO DISK ='TestLog.bak'
WITH INIT; 
GO
-------
Processed 1 pages for database ''Test'', file ''Test_log'' on file 1.
BACKUP LOG successfully processed 1 pages in 0.003 seconds (0.488 MB/sec).

Lesson learned — By restoring a broken log chain backup from a differential backup you can save a enormous amount of time.

Hope this clarify the whole thing

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in Administration, Data Recovery, SQL Server and tagged , , by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

Leave a Reply

Connect with:
  • This field its required.
  • This field its required.
    • Message is required