http://www.sqlservercentral.com/blogs/sql_server_dba/2012/03/23/sql-server-restore-differential-backup-with-simple-recovery-/

Printed 2014/10/21 01:31AM

SQL Server RESTORE DIFFERENTIAL BACKUP WITH SIMPLE RECOVERY

2012/03/23

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

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.