Solved

how to setup log file or intermediate backup

Posted on 2012-04-04
6
501 Views
Last Modified: 2012-06-21
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"?
0
Comment
Question by:jmarkfoley
6 Comments
 
LVL 3

Accepted Solution

by:
sjurus earned 300 total points
ID: 37809571
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.
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 100 total points
ID: 37809713
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)
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 37812500
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
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:jmarkfoley
ID: 37813818
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.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 37814854
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?
0
 
LVL 8

Assisted Solution

by:avirups
avirups earned 50 total points
ID: 37930904
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.
0

Featured Post

Why spend so long doing email signature updates?

Do you spend loads of your time carrying out email signature updates? Not very interesting are they? Don’t let signature updates get you down. Let Exclaimer Cloud - Signatures for Office 365 make managing email signatures a breeze.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Marketers need statistics and metrics like everybody else needs oxygen. In this article we explain how to enable marketing campaign statistics for Microsoft Exchange mail.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
To show how to generate a certificate request in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.:  First we need to log into the Exchange Admin Center. Navigate to the Servers >> Certificates…
This video demonstrates how to sync Microsoft Exchange Public Folders with smartphones using CodeTwo Exchange Sync and Exchange ActiveSync. To learn more about CodeTwo Exchange Sync and download the free trial, go to: http://www.codetwo.com/excha…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now