Solved

how to setup log file or intermediate backup

Posted on 2012-04-04
6
499 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
To show how to create a transport rule 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 Mail Flow >> Rules tab.:  To cr…
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…

757 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

17 Experts available now in Live!

Get 1:1 Help Now