Ok, 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], , [c]
FROM (SELECT LEFT(Col1,1) AS Col1, Id 
FROM Test_Restore.dbo.DifferentialTest) AS SourceTable
PIVOT
(
COUNT(Id)
FOR Col1 IN ([a], , [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], , [c]
FROM (SELECT LEFT(Col1,1) AS Col1, Id 
FROM Test_Restore.dbo.DifferentialTest) AS SourceTable
PIVOT
(
COUNT(Id)
FOR Col1 IN ([a], , [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], , [c]
FROM (SELECT LEFT(Col1,1) AS Col1, Id 
FROM Test_Restore.dbo.DifferentialTest) AS SourceTable
PIVOT
(
COUNT(Id)
FOR Col1 IN ([a], , [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], , [c]
FROM (SELECT LEFT(Col1,1) AS Col1, Id 
FROM Test_Restore.dbo.DifferentialTest) AS SourceTable
PIVOT
(
COUNT(Id)
FOR Col1 IN ([a], , [c])
) AS PivotTable;
GO

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.

Filed under: Backups, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tuesday Tagged: backups, microsoft sql server, restores, T-SQL Tuesday