Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
You say you do full backup every day - are those backups used as part of your backup/recovery strategy or are they used for something else (like loading up a copy of the DB into a QA box)? –  Ram Jan 10 '12 at 0:37
add comment

3 Answers 3

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."

share|improve this answer
    
Why would you recommend breaking the chain? –  Ram Jan 8 '12 at 4:20
    
I wouldn't. Please read the answer again. Copy_only backups don't break the chain started by the weekly backup, the daily full backup do that, start a new chain. –  Marian Jan 8 '12 at 13:05
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
His chain of backups is: Sunday he makes a full backup, in the rest of the days he makes differential backups. The other full backups (with copy_only) made during this week are mostly not necessary for this chain, but for other stuff, like restore in dev/qa, restore in reporting..etc. –  Marian Jan 8 '12 at 13:20
    
I understand what you're suggesting - it's what I do. Regular fulls, frequent tlog backups, occasional copy_onlys to load up qa. I don't see what you are referring to anywhere in the question. Oh well. –  Ram Jan 10 '12 at 0:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.