First thing this morning – a request from a DBA to confirm if Differential BACKUPS can be restored on a database in SIMPLE RECOVERY mode.  Yes , you can.

 A few assumptions are made :

1) A Differential base exists

2) The Differential base is not a COPY_ONLY database .More on Differential backup cannot be restored . BACKUP with COPY_ONLY

3) The Differential backup includes changes only since the last backup

3) No Transaction Logs exist as it’s a database in SIMPLE RECOVERY mode

A simple example :

--Create a sample database in SIMPLE RECOVERY 
CREATE DATABASE [DIFF_TEST] 
GO
ALTER DATABASE [DIFF_TEST] SET RECOVERY SIMPLE 
--Create a FULL backup (base)
BACKUP DATABASE DIFF_TEST TO DISK = 'M:\diff_testFULL.bak'
--commit some activity 
--create a DIFFERENTIAL BACKUP
BACKUP DATABASE DIFF_TEST TO DISK = 'M:\diff_testDIF.bak' WITH DIFFERENTIAL
GO
--Drop the DIFF_TEST database
DROP DATABASE 
-- Restore the Full (base) with NORECOVERY mode
RESTORE DATABASE DIFF_TEST
FROM DISK='M:\diff_testFULL.bak'
WITH NORECOVERY
--Restore the DIFFERENTIAL with NORECOVERY mode
RESTORE DATABASE DIFF_TEST
FROM DISK='M:\diff_testDIFF.bak'
WITH NORECOVERY
--Bring the database ONLINE
RESTORE DATABASE DIFF_TEST
WITH RECOVERY