Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server RESTORE DIFFERENTIAL BACKUP WITH SIMPLE RECOVERY

23 March,2012 by Jack Vamvas

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

Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer