FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
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 .

Related article:

Creating Full and Differential Backups of a SQL Server Database

 


Exam Prep 70-461
Exam 70-461