DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE