Manual full backups and scheduled differential backups

rvw

I wonder if a scheduled differential backup is takes the last scheduled full backup as base, or the last manual backup made if it is newer than the scheduled full backup. So take the following example:

* sunday: scheduled full backup
* monday: scheduled differential
* tuesday: manual full
* wednesday: scheduled differential

Does the differential backup from wednesday need the full backup from wednesday or sunday?

Robert C

Hi,

Differential backups are based on the last full backup made, whether or not it's scheduled or manual, so in this case you'd need Tuesday's full backup.

If you want to avoid this happening (that is, keep Wednesday's diff relative to Sunday's full backup), and you're running SQL Server 2005, you can use the COPY_ONLY parameter with either the extended stored procedure or command line when making your manual backup. This behaves in the same way as the native SQL Server backup's COPY_ONLY parameter - for more details, have a look at http://msdn2.microsoft.com/en-us/library/ms191495.aspx.

Hope that helps,
Robert