SQL Server 2008 R2 - Specify the Full Backup to use when doing a Differential Backup

Question

When I backup my Database, I do a Full Backup every day who goes on a tape. I am trying to use the Sunday's Full backup to set up an additionnal Differential Backup, how can I do this? It only takes the last Full Backup.

Full (Run on Sunday) :

BACKUP DATABASE [DATABASE] TO  DISK = N'J:\DATABASE Backup\MyDatabase_Full.bak' WITH NOFORMAT, NOINIT,  NAME = N'DATABASE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Partial (Run each Day, but use the wrong Full Backup) :

BACKUP DATABASE [DATABASE] TO  DISK = N'J:\DATABASE Backup\MyDatabase_Partial_20120105.bak' WITH  DIFFERENTIAL ,  RETAINDAYS = 10, NOFORMAT, NOINIT,  NAME = N'DATABASE-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

Answer 1

You should do the daily backups as copy_only, so that it doesn't break the chain for transaction log backups or differential backups. After that the daily full backup won't be taken into account as basis for the differential backups.

Quote from MSDN:

"COPY_ONLY Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.

Copy-only backups were introduced in SQL Server 2005 for use in situations in which a backup is taken for a special purpose, such as backing up the log before an online file restore. Typically, a copy-only log backup is used once and then deleted.

When used with BACKUP DATABASE, the COPY_ONLY option creates a full backup that cannot serve as a differential base. The differential bitmap is not updated, and differential backups behave as if the copy-only backup does not exist. Subsequent differential backups use the most recent conventional full backup as their base."

Answer 2

The differential backup will only backup the data changed since the last full backup. So if you do a full backup on Monday 9 PM and a differential at 10 PM, the differential backup will only contain the data changed since the backup you did at Monday 9 PM. You cannot specify the job to do a differential from the Sunday night's backup.

As stated by Marian, if you did a copy_only backup for the full backup on Monday 9 PM, then the differential will contain the data changed since Sunday night's full backup.

Answer 3

sqlserver remembers what the most recent backup was. When you request a differential backup it looks to see what the most recent full backup was and then makes a differential backup that contains everything since that most recent full backup. If you use copy_only backups you cannot use those copy_only backups with differential backups at a later point to recover. Do not use copy_only backups as the basis for our backups.