backup

Question

Post reply

backup

subhashkvsql

Old Hand

Points: 372
More actions
December 31, 2002 at 8:43 am

#79425

we have a sql database(not large medium)
what database backup strategy we should use
We are planning to schedule a full database backup daily once. what should i do regarding differential and log backup

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 1

Is this database transaction based? Does it continually get updated?

In my opinion, if you have time to schedule a full backup once a day, then you have a good strategy.

Michelle

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

Transaction logs help if your database is update during the day, and you want to a restore to a specific time of day in the event you have some kind of databas problem.

Differential backups help if you database backups are large, and the amount of data you update daily is small. These type of backups will help minimize disk space usage.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Antares686 SSC Guru Points: 125444 More actions · Answer 3

One thing that may catch you is if the Transaction log is not cleared before the full backup the time will take more each day as full backup backsup the DB and the Transaction Log without resetting the TL. If you are not worried about transaction loss during the day then do

BACKUP LOG DBName

WITH TRUNCATE_ONLY

before your full backup. Or set to truncate on checkpoint.

Otherwise if loss may be an issue do periodic TL backups thru the day and a Full or Diff if time permits overnight. ANd make sure to do a Full backup as often as possible.

subhashkvsql Old Hand Points: 372 More actions · Answer 4

Thanks , for valuable suggestions

I am doing Trnasaction Log backup once in an hour (appending to old TL BACKUPS) and the TL backup set will expire after the full backup(daily). IS is a good method????

if a failure occurs in the day time how i will apply TL backup when restoring

Antares686 SSC Guru Points: 125444 More actions · Answer 5

quote:
Thanks , for valuable suggestions
I am doing Trnasaction Log backup once in an hour (appending to old TL BACKUPS) and the TL backup set will expire after the full backup(daily). IS is a good method????
if a failure occurs in the day time how i will apply TL backup when restoring?

FIrst I would suggest using seperate files for each TL backup. Reason is if one file corrupts you lose only the data from that point forward. But if you append to a single file and it becomes corrupted in anyway you lose every TL backup in it. Also, keep in mind that a Full backup does not issue a chekpoint into the TL and thus does not cause it to truncate. If you loose a Full backup and you have hourly TLs back to the last previous Full backup you can recover it all. I would suggest decide ho many days you can keep and keep as much as possible but I would prefer at least 2 days just in case a bad Full backup shows up.

Now for applying backups in restore for you, you do like so.

Restore Full with no recovery

Restore all TL backups in order except the last one with no recovery

Restore the last TL backup with recovery to bring the DB back online

Do a Full backup as soon a possible.

TomBrattin SSCrazy Points: 2436 More actions · Answer 6

I suggest that you never truncate the transaction log, always back it up.. Database backups are fine for restoring the state of a database, but the transaction log preserves your changes.

Example: You run DBCC each morning and come up with an error. If you are lucky you can repair it without data loss.

More likely the repair would cause some unknown data loss. Your only solution is to back up the log, go back one or more days (to last full backup done before error appeared), restore database, then apply transaction logs back to the one you just dumped. Chances are that the error will not repeat itself as it was caused by some OS/hardware quirk that does not repeat. Net result is that you have fixed the database with no data loss.

Differential backups simply reduce the number of transaction logs that have to be applied. But none of your backups are of any use unless you know they do not contain the problem you are trying to fix.