Error restoring Database with Diff & Inc backups

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Error restoring Database with Diff & Inc backups

Author

Topic

methodology
Starting Member

31 Posts

Posted - 2007-09-10 : 05:40:16

I backup a database at the begining of each month with a full and then do nightly diffs on it.

For the same database I run daily fulls and 10 minute log backups.

these two backups create / append to two different backup files.

The problem im having is that I cant restore the Differential backup set. SQL seems to restore the full just fine but alwasy throws an error when its about to start to retore the last diff. now forgive me but I clicked OK on the message and I cant find any record of the error in the logs but its something like:

"SQL cannot restore the database as the database has not been restored to the previous correct state"

is my 10 min TS log backups screwing up the DIff chain somehow?

this is really doing my head in. any help appreciated.

"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

Kristen
Test

22859 Posts

Posted - 2007-09-10 : 10:47:26

So if I understand you correctly you have:

1st of Month FULL Backup (B1)
2nd of Month DIFF Backup (B2)
then FULL backup (B3)
12:00am TLog backup (B4)
12:10am TLog backup (B5)
...
3rd of Month DIFF Backup (B6)
then FULL backup (B7)
12:00am TLog backup (B8)
12:10am TLog backup (B9)

You can NOT restore B1 then B6.

B6 is based on the immediately preceding FULL Backup, i.e. B3.

You can restore B3 then B6

You can ALSO restore B1 then B4, B5 ... and then B8, B9 .. (i.e. ALL subsequent Tlog backups since a FULL (and optionally subsequent DIFF) no matter how far back in time the FULL/DIFF were - provided you have ALL the TLog backups in between of course)

It reads to me like you ought to alter your process:

1) No point doing a FULL and DIFF at the same time.
2) I would NOT risk doing a FULL only once a month. If it is not readable you will have to go back to the previous month's backup file.

3) Unless you have a disk space or performance problem I would not use DIFF at all. Exception might be if you need to take a quickie-backup before some risky procedure - e.g. running some fixes. DIFF might be quicker than FULL for that purpose.

We run FULL backup on Sunday, and DIFF backup daily. We have severe disk space issues on that machine, otherwise we would do FULL every night instead.

FULL during the night and DIFF at, say, lunchtime might be a sensible strategy to reduce the Restore time. e.g. you have a failure at, say, 5PM which means:

Restore last night's FULL
Restore lunchtime DIFF
Restore TLog backups SINCE lunchtime.

That's a lot less Tlog files to restore than "Restore TLog backups since LAST NIGHT"

"two backups create / append to two different backup files"

I very VERY strongly recommend that you do NOT do this.

Use a different filename for every backup (e.g. a filename comprising Database name + Date + Time).

1) A corruption in the appended-file is likely to render the whole file unusable
2) IME the Restore performance, when faced with a large file containing numerous backups, is horrendous compared to separate files.
3) The total disk space used is no greater with separate files
4) You can delete stale files sooner

e.g. you may have a policy:

Full backup once a week, keep for 4 weeks
Diff backup daily, keep for 7 days
TLog backup every 10 minutes, keep for 36 hours

The further back you need to restore the less granularity you have (well, you probably have ALL the files on Tape, this is just what you have available on-line on disk)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources