SQL Server Differential / Incremental Backup

SQLUSA.com

SQL Server Scripts	SQL 2005	SQL 2008	Articles

How to create a differential backup?

Following is a Microsoft SQL Server T-SQL example for the differential backup of AdventureWorks database onto AWDiff backup device:

BACKUP DATABASE [AdventureWorks] TO [AWdiff] WITH INIT, DIFFERENTIAL,
NAME = N'AdventureWorks Differential', RETAINDAYS = 7, STATS = 10, NOFORMAT

Typical database backup plan applying differential backup:

1. Full database backup MON 2am

2. Differential database backup TUE-SAT 2am

3. Transaction log backup MON-FRI 8 am - 6pm every 15 min

NOTE: differential backup supercedes previous differential backups following the full database backup. FRI differential backup makes MON-THU differential backups unnecessary. FRI differential backup has all the changes since MON 2am full backup.

Disaster recovery process at WED 11:35AM:

1. Restore DB from MON 2am full backup with no recovery

2. Restore WED 2am differential backup with no recovery

3. Restore WED transaction log backups in order with no recovery

4. Restore WED 11:30 (last) transaction log backup with recovery .

Creating Full and Differential Backups of a SQL Server Database