Microsoft SQL Server forum - SQL Server 2000 Backup + Restore question

Author

SQL Server 2000 Backup + Restore question

Eric J. Holtman

2005-09-28, 8:24 pm

Of course I'll try this before I go live, but I figured I'd ask
here first. I've read the docs, and the answer is unclear.

My current backup strategy is:

Take a full database backup occasionally (like, every two weeks).
Back up logs every five minutes.

This works, but restoring the database all the way through can
take a while, as I've sometimes spun through 800+ log restores.

I'd like to change to

Take a full database backup occasionally (like, every two weeks).
Take a differential backup every day.
Back up logs every five minutes.

So, I'd have this set of backups after day two

FULL
LOGS 1-100
DIFFERENTIAL
LOGS 101-200

When I go to restore, do I always have to do
FULL
DIFFERENTIAL
LOGS 101-200

or can I do

FULL
LOGS 1-100
LOGS 101-200?

I guess what I'm really asking is: is there any reason to keep
the "old" logs around (in this case 1-100), if I assume I always
have the latest differential?

I suppose I *might* need them to do a "point in time" restore,
i.e.
FULL
LOGS 1-53.

but assuming I never want to do that, can I discard the logs
that are earlier than my latest differential?

Simon Hayes

2005-09-29, 3:23 am

Your understanding is correct - "full + all logs" or "full + diff +
logs since diff" are effectively the same thing, but as you say, using
differential backups can significantly reduce the time to restore.
Being able to do a point in time restore is the only thing you would
need the intermediate logs for, unless you want to keep them just in
case you lose or corrupt the differential backups.

Simon

Sponsored Links