Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to setup log file or intermediate backup

Posted on 2012-04-04
6
Medium Priority
?
512 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 1200 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:Ephraim Wangoya
Ephraim Wangoya earned 400 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 200 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 200 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

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.

Question has a verified solution.

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

As much as Microsoft wants to kill off PST file support, just as they tried to do with public folders, there are still times when it is useful or downright necessary to export Exchange mailboxes to PST files. Thankfully, it is still possible to e…
Steps to fix “Unable to mount database. (hr=0x80004005, ec=1108)”.
In this video we show how to create an Accepted Domain 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 >> Ac…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

971 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