DBA Myths: You can only restore one differential backup

1

December 13, 2016 by Kenneth Fisher

T-SQL TuesdayOk, I know I’m hosting T-SQL Tuesday this month but I still had post I wanted to share.
 

The basic steps when restoring backups are

Full Recovery

  1. The most recent full backup.
  2. The most recent differential backup.
  3. All log backups after the most recent differential.

 
Simple Recovery

  1. The most recent full backup.
  2. The most recent differential backup.

 
This has led to the belief (or at least I believe this is one of the causes) that you can only restore a single differential backup. And up until the last few weeks I’d believed that myself. So, to set up a fairly simple test. I’m going to take a backup, create a table, and make some changes with differential backups in between the changes.

-- Take a full backup as a base point
BACKUP DATABASE Test TO DISK = 'C:\backups\Test_20161113.bak';
GO
-- Create a table where the changes can happen
CREATE TABLE DifferentialTest (
		Id INT NOT NULL IDENTITY(1,1) 
			CONSTRAINT pk_DifferentailTest PRIMARY KEY,
		Col1 varchar(1000)
		);
GO
-- Insert a thousand rows into the table
INSERT INTO DifferentialTest VALUES (REPLICATE('a',1000));
GO 1000
-- Run the first differential backup
BACKUP DATABASE Test TO DISK = 'C:\backups\Test_20161113_1.dif'
	WITH DIFFERENTIAL;
GO
-- Update 500 of the thousand rows
UPDATE DifferentialTest SET Col1 = REPLICATE('b',1000)
	WHERE Id > 500;
GO
-- Run the second differential backup
BACKUP DATABASE Test TO DISK = 'C:\backups\Test_20161113_2.dif'
	WITH DIFFERENTIAL;
GO
-- Insert an additional five hundred rows
INSERT INTO DifferentialTest VALUES (REPLICATE('c',1000));
GO 500
-- Create the last differential backup for our test
BACKUP DATABASE Test TO DISK = 'C:\backups\Test_20161113_3.dif'
	WITH DIFFERENTIAL;
GO

A simple test would be restore one differential with NORECOVERY then restore another one. But since that final differential is going to contain all of the changes and I’d like to see what’s happening as we go, let’s do a slightly more complicated test.

I’m going to do each of the restores in Standby. This allows me to read the data in the table but still restore additional information when I’m ready.

USE master;
GO
-- Initial full restore
RESTORE DATABASE Test_Restore FROM DISK = 'C:\backups\Test_20161113.bak'
	WITH MOVE 'Test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016CS\MSSQL\DATA\Test_Restore.mdf',
		MOVE 'Test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016CS\MSSQL\DATA\Test_Restore_log.ldf',
		STANDBY = 'C:\backups\Test_Restore_Standby.bak';
GO
-- Table shouldn't exist yet.
SELECT [a], [b], [c]
FROM (SELECT LEFT(Col1,1) AS Col1, Id 
	FROM Test_Restore.dbo.DifferentialTest) AS SourceTable
PIVOT
(
	COUNT(Id)
	FOR Col1 IN ([a], [b], [c])
) AS PivotTable;
GO
-- First differential restore
RESTORE DATABASE Test_Restore FROM DISK = 'C:\backups\Test_20161113_1.dif'
	WITH STANDBY = 'C:\backups\Test_Restore_Standby.bak';
GO
-- There should be 1000 a's and nothing else.
SELECT [a], [b], [c]
FROM (SELECT LEFT(Col1,1) AS Col1, Id 
	FROM Test_Restore.dbo.DifferentialTest) AS SourceTable
PIVOT
(
	COUNT(Id)
	FOR Col1 IN ([a], [b], [c])
) AS PivotTable;
GO
-- Second differential restore
RESTORE DATABASE Test_Restore FROM DISK = 'C:\backups\Test_20161113_2.dif'
	WITH STANDBY = 'C:\backups\Test_Restore_Standby.bak';
GO
-- There should be 500 a's and 500 b's now.
SELECT [a], [b], [c]
FROM (SELECT LEFT(Col1,1) AS Col1, Id 
	FROM Test_Restore.dbo.DifferentialTest) AS SourceTable
PIVOT
(
	COUNT(Id)
	FOR Col1 IN ([a], [b], [c])
) AS PivotTable;
GO
-- Third and final differential restore
RESTORE DATABASE Test_Restore FROM DISK = 'C:\backups\Test_20161113_3.dif'
	WITH RECOVERY
GO
-- Finally, there should be 500 a's, 500 b's and 500 c's.
SELECT [a], [b], [c]
FROM (SELECT LEFT(Col1,1) AS Col1, Id 
	FROM Test_Restore.dbo.DifferentialTest) AS SourceTable
PIVOT
(
	COUNT(Id)
	FOR Col1 IN ([a], [b], [c])
) AS PivotTable;
GO

multipledifferentials

There you go. You can, in fact, do multiple differential restores.

Why is this useful? Well, for example, you could use it for a modified version of log shipping that will work with SIMPLE recovery. Weekly full restores and then daily differential restores. Remember that you can only restore differentials on top of the most recent full backup (most recent to the differential). Also remember that because differentials contain all of the changes since the last full backup they will continue to grow larger and larger over time. You’ll need to do regular full backups (you would anyway for disaster recovery) and regular restores of the full backups. This is unlike regular log shipping where a single full restore is fine and then you can do log backups as long as you like.

One thought on “DBA Myths: You can only restore one differential backup

  1. […] Class 102: I discuss the myth that you can only restore one differential backup. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,134 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: