Sql-server – Differential Backup Issue – Why? Is this Possible

backuprestoresql server

I am using SQL Server 2014 and this is the situation:

  • I have server A and server B.
  • Overnight ETL is processed on server A.
  • After the loading process completes, database X gets backed-up (with CHECKSUM and RESTORE VERIFYONLY to ensure reliability) and then get sent to server B.
  • Server B receives the bak file and then restore the database there.

I want to use differential backup strategy so that:

  1. Full backup is only done only on Saturday
    i.e. Full backup on server A on Saturday -> ship to server B -> Restore the full backup on server B

  2. The rest of the days will be differential backup
    i.e. Differential backup on server A -> ship to server B -> Restore the differential backup on server B

I have tried but I got an error, saying:

the log or differential backup cannot be restored because no files are ready to rollforward.

Not sure why. I checked sys.database_files on server A and server B, and I can see that the differential_Base_LSN and differential_base_GUID are the same.
Anywhere/anything else to check?

By the way, on step 2 above, when I am restoring the diff backup on server B, do I always need to restore both the Full backup + the differential backup each time?

I only restored the differential backup WITH RECOVERY (and got that error message) because the full backup was restored already the day before.

To clarify: Yes, I want the db on server B to be readable between differentials. How can I get around that? Is my only option to RESTORE FULL (WITH NORECOVERY) + RESTORE DIFF (WITH RECOVERY) combo sequence each night?

Any guidance will be much appreciated.

Best Answer

You don't need to mess with RECOVERY and NORECOVERY here, all you need is the STANDBY option. Here's a quick demo on how to use it.

Create a database, set it to simple recovery, and create a table.

Insert data, take some diffs.

Fun, right?

USE master;

/*Create a dummy database*/
CREATE DATABASE DiffRestoreTest

/*We simple now*/
ALTER DATABASE DiffRestoreTest SET RECOVERY SIMPLE

/*Context is everything*/
USE DiffRestoreTest

/*If nothing changes, do we even need a diff backup?*/
CREATE TABLE dbo.t1 (Id INT)

/*Take a full backup, dummy*/
BACKUP DATABASE DiffRestoreTest 
TO DISK = 'F:\Backup\DRT_FULL.bak' 
WITH INIT, FORMAT, COMPRESSION

/*Make a change*/
INSERT dbo.t1 (Id )
VALUES ( 1 )

/*Take a differential backup*/
BACKUP DATABASE DiffRestoreTest 
TO DISK = 'F:\Backup\DRT_DIFF_1.bak' 
WITH INIT, FORMAT, COMPRESSION, DIFFERENTIAL

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 2 )

/*Take another diff backup*/
BACKUP DATABASE DiffRestoreTest  
TO DISK = 'F:\Backup\DRT_DIFF_2.bak' 
WITH INIT, FORMAT, COMPRESSION, DIFFERENTIAL

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 3 )

/*Take another diff backup*/
BACKUP DATABASE DiffRestoreTest 
TO DISK = 'F:\Backup\DRT_DIFF_3.bak' 
WITH INIT, FORMAT, COMPRESSION, DIFFERENTIAL

Yeah, I lied. That's the boring part.

You can restore your Full backup in STANDBY:

/*Exit stage left*/
USE master

/*Restore the full backup*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_FULL.bak' 
WITH REPLACE, STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

You can restore Diffs in order with STANDBY:

/*Square one*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_1.bak' 
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

/*Square 2*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_2.bak' 
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

/*Square 3*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_3.bak' 
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

And unlike those pesky Log files, you can skip ahead when restoring Diffs, too:

/*Restore the full backup*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_FULL.bak' 
WITH REPLACE, STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

/*What happens if I try to jump the restores?*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_3.bak' 
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

If you want to test readability, just run this in between restore commands. You should see IDs increment with each. If you don't, you did something horribly wrong.

Keep in mind that when you restore files, it will kick any end users out of the database, and it won't wait for their queries to finish.

This also leaves the database in a read only state, no changes can be made here. 

SELECT * 
FROM DiffRestoreTest.dbo.t1 AS t

And finally, clean up after yourself.

/*Bring'er online, lad*/
RESTORE DATABASE DiffRestoreTest WITH RECOVERY

DROP DATABASE DiffRestoreTest

Hope this helps!!

Related Question