how to setup log file or intermediate backup

I back up my SQL Server 2005 databases daily with a SQL Server Agent job. Now I'd like to do some kind of intermediate backup, or backups, during the business day which will get saved on a remote computer every 20 minutes so we can always be sure to never lose anything other than the last 20 minutes.

I need advice. Should I set the databases to Full or Simple? Should I create transactions logs or do differential backups? I'm not entirely sure how transaction logs work. Do they journalize each transaction? What is this business about having to clean up log space, and "expiration"?
LVL 1
MarkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sjurusCommented:
Hi,
 if you want to do transaction log backup you must set the databases to Full recovery model because in this mode you have full transaction logging. What a choice whether to do transaction log backup or Differential backup is related more to business requirements.

Personally, I very often use such a model:

full backup on Sunday
Differential daily backup at night
and during the day the transaction log backup

You can also adopt a different model: database in Simple Recovery Model

full backup on Sunday
before starting work Differential backup (if performed at night are some additional operations on the database)
Differential backup after work
Only in this model, you have to realize to a business that in case of failure the data will be recovered from the first day of work

Of course, start and end and how often the backup log file depends on the specifics of the system and business requirements

This article will explain how work the transaction log:
http://www.sqlservercentral.com/articles/Design+and+Theory/63350/

So really the choice model disaster recovery actively addressed mainly depends on the system, such a database (transaction, data warehouse, etc) on the number of operations during the day, the amount of data we can lose, etc.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ephraim WangoyaCommented:
Depending on the size of your database, daily full backup may not be efficient in terms of resources (space, CPU)
You can use mirroring or transaction log shipping
Recovery model should be Full

Set a weekly full backup and a daily differential backup.
You can backup the transaction logs every few minutes as you wish
Regular transaction log backups also ensure that your log file does not grow out of hand (I suspect that is what you mean by cleanup)
MarkAuthor Commented:
These database aren't that big, so I am currently doing daily full backups. I guess I'd like to do transactions logs every <n> minutes as ewangoya suggests.

sjurus' link was very informative in telling me about the theory behind logfile, but it didn't really say how to set them up. So, first step, make the database Full Recovery Mode. Then what? How do I specify where the log files go? How often they are created? I didn't really understand the bit about logfile size. Do I set this? is this for each log file? is there more than one? I'm new at this.

Thanks
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

MarkAuthor Commented:
more info

I created a maintence plan task to backup the log files. I have a bit of a problem in that it doesn't seem to run automatically even though I believe I've set it to run every 20 minutes. Can anyone advise me what I could be doing wrong?

Anyway, I did get the T-SQL and ran it by hand. For example:

BACKUP LOG [OSH2011] TO  DISK = N'\\OHPRSstorage\public\SYSADMIN\SQLserverTransactionLogs\OSH2011\OSH2011_backup_201204051533.trn' WITH NOFORMAT, NOINIT,  NAME = N'OSH2011_backup_20120405153333', SKIP, REWIND, NOUNLOAD,  STATS = 10

I was surprised to see that this generated a .trn file of 2,348,673,536 bytes. A fullbackup of this database is 429MB. Why do I have a 2GB transaction file? If that's the case, I'd be ahead to do differential backups.
Anthony PerkinsCommented:
Why do I have a 2GB transaction file? If that's the case, I'd be ahead to do differential backups.
How big was the Transaction Log file?
What was/is the Recovery Model?
When was the last time you did a Transaction Log backup?
avirupsCommented:
If you want to avoid the maintenance involving the transaction log file and since your database is not very large yet, why don't you keep the recovery model to SIMPLE and schedule a Differential Backup every 20 minutes along with your Full Daily Backup.

That should do the trick. Now in this case, whenever you want to restore you only need to restore the last full backup with NORECOVERY option and then restore the last Differential Backup with RECOVERY option.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Exchange

From novice to tech pro — start learning today.