-
Title
Performance between Differential backups verses Full backups -
Description
Will Differential backups perform faster than Full backups, if the impact to the Database in terms of I/O and locking are reduced? Are all datbase blocks read in the same fashion during a full and differential backup?
-
Resolution
In SQL Server 2005 and later versions, differential file backups can be very fast because the SQL Server Database Engine tracks the changes that were made since the differential base was created.
Differential backups happen faster only because SQL Server is backing up less data - just the changed pages - instead of all the pages. Depending on the database's change rate, this can make a huge difference.
If you are experiencing IO and locking problems during the backup, those problems will likely happen no matter what the backup method is, though - diffs will just make the backups last shorter time ranges.
-
Additional Information
The following link provides more detailed explanation.
http://msdn.microsoft.com/en-us/library/ms190412.aspx
When using SQL 2000 Enterprise, SS2000 does support differentials through page change bits.